db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Brett Bergquist (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6317) Optmizer can choose the wrong path when BTreeCostController.java returns an estimate cost and row count of 0.0
Date Tue, 27 Aug 2013 17:29:53 GMT

    [ https://issues.apache.org/jira/browse/DERBY-6317?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13751474#comment-13751474
] 

Brett Bergquist commented on DERBY-6317:
----------------------------------------

Mike's request:

   1) can you try the query on some value where there are exactly 2 values, maybe just add
another row to the value you know there is 
only one of? Just wondering if this is an off by 1 bug somewhere.

I added a new row which has the same value being queried on (22112129) and the left_of_start
and right_of_start still return the same value even though there are now two rows that will
be returned in the query.   So it does not look like a off by 1 bug.   I surmise that since
it it dealing with percentages and these are floating point, non-exact values, that what is
happening is that sometimes the percentage of the result space starting and stopping regions
are so close that the percentages become equivalent.

   3) The BtreeCostController is probably never returning a 0.0 cost, but in FromBaseTable.java,
we have:


			/*
			** Let the join strategy decide whether the cost of the base
			** scan is a single scan, or a scan per outer row.
			** NOTE: The multiplication should only be done against the
			** total row count, not the singleScanRowCount.
			*/
			double newCost = costEstimate.getEstimatedCost();

			if (currentJoinStrategy.multiplyBaseCostByOuterRows())
			{
				newCost *= outerCost.rowCount();
			}

			costEstimate.setCost(
				newCost,
				costEstimate.rowCount() * outerCost.rowCount(),
				costEstimate.singleScanRowCount());

So if the row count of the outer table of the join is 0.0, the newCost can end up being 0.0,
even though the cost for the table being processed is very large.

                
> 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

Mime
View raw message