[
https://issues.apache.org/jira/browse/DERBY-6317?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13751678#comment-13751678
]
Brett Bergquist commented on DERBY-6317:
----------------------------------------
It does choose a valid and optimal one but because the BtreeCostController is
really not coming into play. That is returning all rows as the estimate row
count. So for example, for a 7M row table, the estimate row count is 7M and
the cost is very high because of the other calculations done in there (ie. cost
+= (estimated_row_count * row_size) * BASE_ROW_PER_BYTECOST).
Then FromBaseTable uses the statistics selectivity in the final cost
computation.
So the answer is yes it gets a "correct" plan, but though a different
computation of the cost.
> Optmizer can choose the wrong path when BTreeCostController.java returns an
> estimate cost and row count of 0.0
> --------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-6317
> URL: https://issues.apache.org/jira/browse/DERBY-6317
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.8.2.2
> Environment: Derby 10.8.2.2 on Oracle Solaris 10
> Reporter: Brett Bergquist
>
> The optimizer can chose the wrong path when BTreeCostController.java returns
> an estimate cost and row count of 0.0.
> Assume that you have two tables that are being joined like:
> SELECT * FROM T1, T0
> WHERE T1.ID = T0.F_ID and
> T0.ID = 3;
> Also assume that T0 has two columns, ID and F_ID and F_ID is a foreign key on
> T1.ID. Assume that T1.ID is the primary key of T1 and (T0.F_ID, T0.ID) is
> the primary key on T0. Assume that there is a non-unique index on T0.ID.
> The correct query plan for this should be to query T0 using the non-unique
> index on T0.ID and then use the foreign key value in those rows to do query
> T1 using the primary key on T1.
> With some values of T0.ID in the above query this query plan is chosen and
> works. With other values of T0.ID , the query plan does an query on T0 using
> the non-unique index on T0.ID and then does a table scan on T1.
> For example, in my case the query:
> SELECT * FROM T1, T0
> WHERE T1.ID = T0.F_ID and
> T0.ID = 22112129;
> has this query plan.
> The problem appears to be in BTreeCostController.java. When this returns the
> same value for the "left_of_start" and the "left_of_stop" (which is being
> used to estimate the number of rows and cost), then the estimate cost and row
> count becomes 0.0. When this is used in the join order of T0, T1, then the
> cost of the table scan for T1 becomes 0.0 as well.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
