db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DERBY-6317) Optmizer can choose the wrong path when BTreeCostController.java returns an estimate cost and row count of 0.0
Date Fri, 06 Sep 2013 03:07:55 GMT

     [ https://issues.apache.org/jira/browse/DERBY-6317?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Mamta A. Satoor updated DERBY-6317:
-----------------------------------

    Attachment: DERBY_6317_junit_test_v2_diff.txt

Attaching patch DERBY_6317_junit_test_v2_diff.txt which has junit reproducible test for this
jira. There are handful of fixtures. The longest running fixture is testDERBY_6317 since it
does select on each of the 8 million rows individually and checks if we are using index scan
for getting the row. Without Mike's patch, this test fixture can take about 6hrs. This test
fixture will fail along with couple other test fixtures(testDERBY_6317_value1, testDERBY_6317_value2,
testDERBY_6317_value3) without Mike's changes because they will detect that we are using table
scan. Once Mike's patch is applied, all of the test fixtures should pass. I am running the
junit test right now with Mike's changes and the test has not finished yet after 9 hours of
run. I will post a comment once this test run is over. Because of the length of the time the
testDERBY_6317 fixture takes, we obviously won't want this to be running as part of junit
complete suite. Even without this test fixture, the test can take over an hr because it has
to load 3 tables with 1million, 8million and 8million rows respectively and these tables have
indexes defined on them. The indexes need to be defined before the data load in order to reproduce
the problem.
                
> 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
>            Assignee: Mike Matrigali
>         Attachments: derby6317_2.diff, derby6317.diff, DERBY_6317_junit_test_v1_diff.txt,
DERBY_6317_junit_test_v2_diff.txt, DERBY_6317_temp_changes_for_debugging.txt, testRepro_v1.txt
>
>
> 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