Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Wed, 4 Sep 2013 18:58:54 +0000 (UTC)
From: "Mamta A. Satoor (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: <JIRA.12665641.1377570359931.79790.1378321134512@arcas>
In-Reply-To: <JIRA.12665641.1377570359931@arcas>
References: <JIRA.12665641.1377570359931@arcas>
Subject: [jira] [Commented] (DERBY-6317) Optmizer can choose the wrong path
 when BTreeCostController.java returns an estimate cost and row count of 0.0
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


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

Mamta A. Satoor commented on DERBY-6317:
----------------------------------------

The following query plan is when we are doing index scan of TABLE1 which is the correct behavior. Note the value in SELECT for indexed column is 5189285 (just one higher than the 5189284 where we use table scan)
----------------------------------------------------------------
Wed Sep 04 11:55:19 PDT 2013:
Booting Derby version The Apache Software Foundation - Apache Derby - 10.11.0.0 alpha - (1): instance a816c00e-0140-ea54-2a4c-00000022b220 
on database directory C:\p4clients\svnmain\client3\trunk\systest\del\ClobTest3 with class loader sun.misc.Launcher$AppClassLoader@53745374 
Loaded from file:/C:/p4clients/svnmain/client3/trunk/classes/
java.vendor=IBM Corporation
java.runtime.version=pwi3260sr14-20130705_01 (SR14)
java.fullversion=JRE 1.6.0 IBM J9 2.4 Windows 7 x86-32 jvmwi3260sr14-20130704_155156 (JIT enabled, AOT enabled)
J9VM - 20130704_155156
JIT  - r9_20130517_38390
GC   - GA24_Java6_SR14_20130704_1138_B155156
user.dir=C:\p4clients\svnmain\client3\trunk\systest\del
os.name=Windows 7
os.arch=x86
os.version=6.1 build 7601 Service Pack 1
derby.system.home=null
Database Class Loader started - derby.database.classpath=''
Wed Sep 04 11:55:24 PDT 2013 Thread[main,5,main] (XID = 767118), (SESSIONID = 1), SELECT count(*) FROM 
	Table1 T1,
	Table2 t0 
	WHERE t1.ID = t0.Table1_ID and 
		t0.Table3_ID = 5189285 ******* Project-Restrict ResultSet (7):
Number of opens = 1
Rows seen = 1
Rows filtered = 0
restriction = false
projection = true
	constructor time (milliseconds) = 0
	open time (milliseconds) = 0
	next time (milliseconds) = 0
	close time (milliseconds) = 0
	restriction time (milliseconds) = 0
	projection time (milliseconds) = 0
	optimizer estimated row count: 1.00
	optimizer estimated cost: 12.72
Source result set:
	Scalar Aggregate ResultSet:
	Number of opens = 1
	Rows input = 1
		constructor time (milliseconds) = 0
		open time (milliseconds) = 0
		next time (milliseconds) = 0
		close time (milliseconds) = 0
		optimizer estimated row count: 1.00
		optimizer estimated cost: 12.72
	Index Key Optimization = false
	Source result set:
		Project-Restrict ResultSet (6):
		Number of opens = 1
		Rows seen = 1
		Rows filtered = 0
		restriction = false
		projection = true
			constructor time (milliseconds) = 0
			open time (milliseconds) = 0
			next time (milliseconds) = 0
			close time (milliseconds) = 0
			restriction time (milliseconds) = 0
			projection time (milliseconds) = 0
			optimizer estimated row count: 1.00
			optimizer estimated cost: 12.72
		Source result set:
			Nested Loop Exists Join ResultSet:
			Number of opens = 1
			Rows seen from the left = 1
			Rows seen from the right = 1
			Rows filtered = 0
			Rows returned = 1
				constructor time (milliseconds) = 0
				open time (milliseconds) = 0
				next time (milliseconds) = 0
				close time (milliseconds) = 0
				optimizer estimated row count: 1.00
				optimizer estimated cost: 12.72
			Left result set:
				Index Row to Base Row ResultSet for TABLE2:
				Number of opens = 1
				Rows seen = 1
				Columns accessed from heap = {0, 1}
					constructor time (milliseconds) = 0
					open time (milliseconds) = 0
					next time (milliseconds) = 0
					close time (milliseconds) = 0
					optimizer estimated row count: 1.00
					optimizer estimated cost: 8.01
					Index Scan ResultSet for TABLE2 using constraint TABLE2_FK_2 at read committed isolation level using instantaneous share row locking chosen by the optimizer
					Number of opens = 1
					Rows seen = 1
					Rows filtered = 0
					Fetch Size = 16
						constructor time (milliseconds) = 0
						open time (milliseconds) = 0
						next time (milliseconds) = 0
						close time (milliseconds) = 0
						next time in milliseconds/row = 0

					scan information:
						Bit set of columns fetched=All
						Number of columns fetched=2
						Number of deleted rows visited=0
						Number of pages visited=4
						Number of rows qualified=1
						Number of rows visited=2
						Scan type=btree
						Tree height=-1
						start position:
							>= on first 1 column(s).
							Ordered null semantics on the following columns: 
							0 
						stop position:
							> on first 1 column(s).
							Ordered null semantics on the following columns: 
							0 
						qualifiers:
							None
						optimizer estimated row count: 1.00
						optimizer estimated cost: 8.01

			Right result set:
				Index Scan ResultSet for TABLE1 using constraint SQL130829215951000 at read committed isolation level using share row locking chosen by the optimizer
				Number of opens = 1
				Rows seen = 1
				Rows filtered = 0
				Fetch Size = 1
					constructor time (milliseconds) = 0
					open time (milliseconds) = 0
					next time (milliseconds) = 0
					close time (milliseconds) = 0
					next time in milliseconds/row = 0

				scan information:
					Bit set of columns fetched={0}
					Number of columns fetched=1
					Number of deleted rows visited=0
					Number of pages visited=3
					Number of rows qualified=1
					Number of rows visited=1
					Scan type=btree
					Tree height=3
					start position:
						>= on first 1 column(s).
						Ordered null semantics on the following columns: 
						0 
					stop position:
						> on first 1 column(s).
						Ordered null semantics on the following columns: 
						0 
					qualifiers:
						None
					optimizer estimated row count: 1.00
					optimizer estimated cost: 4.71


> 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_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