db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bergquist, Brett" <BBergqu...@canoga.com>
Subject RE: Why is the optimizer choosing such a bad path
Date Wed, 27 Mar 2013 19:51:35 GMT
Another update.  On a whim, I dropped and recreated:

	CREATE INDEX CONFIGURATION_BUNDLE_IX_1 ON "CORE_V1"."CONFIGURATION_BUNDLE"(DTYPE);

And then the query returns in a fraction of a second.

-----Original Message-----
From: Bergquist, Brett [mailto:BBergquist@canoga.com] 
Sent: Wednesday, March 27, 2013 3:17 PM
To: derby-dev@db.apache.org
Subject: Why is the optimizer choosing such a bad path

Some background.   

I have a customer that is using an earlier release of our system that has 
Derby 10.8.2.1 installed.   Because of issues like 
	https://issues.apache.org/jira/browse/DERBY-5680, 
it has been running with the indexStat daemon disabled.   

We are going to have a new release soon and it will be installing Derby 10.9.1.0 with the
indexStat
daemon enabled.   I recently got a copy of the customer's database (132Gb) and ran into
a very long query.   I manually ran SYS_UTIL.SYSCS_UPDATE_STATISTICS on all of the tables
in the query to ensure that the statistics are up to date.

The tables in the query look like:

// 62,908,433 Rows
CREATE TABLE "CORE_V1"."CONFIGURATIONSET_CONFIGURATIONBUNDLE"
(
   CONFIGURATIONBUNDLE_ID int NOT NULL,
   CONFIGURATIONSET_ID int NOT NULL,
   CONSTRAINT CONFIGURATIONSET_CONFIGURATIONBUNDLE_PK PRIMARY KEY (CONFIGURATIONBUNDLE_ID,CONFIGURATIONSET_ID)
);
ALTER TABLE "CORE_V1"."CONFIGURATIONSET_CONFIGURATIONBUNDLE"
ADD CONSTRAINT CONFIGURATIONSET_CONFIGURATIONBUNDLE_FK_2
FOREIGN KEY (CONFIGURATIONSET_ID)
REFERENCES "CORE_V1"."CONFIGURATION_SET"(ID);
ALTER TABLE "CORE_V1"."CONFIGURATIONSET_CONFIGURATIONBUNDLE"
ADD CONSTRAINT CONFIGURATIONSET_CONFIGURATIONBUNDLE_FK_1
FOREIGN KEY (CONFIGURATIONBUNDLE_ID)
REFERENCES "CORE_V1"."CONFIGURATION_BUNDLE"(ID);
CREATE INDEX SQL100922214558160 ON "CORE_V1"."CONFIGURATIONSET_CONFIGURATIONBUNDLE"(CONFIGURATIONSET_ID);
CREATE INDEX SQL100922215312050 ON "CORE_V1"."CONFIGURATIONSET_CONFIGURATIONBUNDLE"(CONFIGURATIONBUNDLE_ID);
CREATE UNIQUE INDEX SQL081029110254290 ON "CORE_V1"."CONFIGURATIONSET_CONFIGURATIONBUNDLE"
(
  CONFIGURATIONBUNDLE_ID,
  CONFIGURATIONSET_ID
);

// 4 Rows
CREATE TABLE "PKG_9145E10G"."INGRESS_COS_QUEUE_CONFIG_BUNDLE"
(
   ID int PRIMARY KEY NOT NULL,
   LLQ_CONFIG_ENTRY_ID int NOT NULL
);
ALTER TABLE "PKG_9145E10G"."INGRESS_COS_QUEUE_CONFIG_BUNDLE"
ADD CONSTRAINT INGRESS_COS_QUEUE_CONFIG_BUNDLE_FK_2
FOREIGN KEY (LLQ_CONFIG_ENTRY_ID)
REFERENCES "PKG_9145E10G"."INGRESS_COS_QUEUE_CONFIG_ENTRY"(ID);
ALTER TABLE "PKG_9145E10G"."INGRESS_COS_QUEUE_CONFIG_BUNDLE"
ADD CONSTRAINT INGRESS_COS_QUEUE_CONFIG_BUNDLE_FK_1
FOREIGN KEY (ID)
REFERENCES "CORE_V1"."CONFIGURATION_BUNDLE"(ID);
CREATE INDEX SQL130327093508050 ON "PKG_9145E10G"."INGRESS_COS_QUEUE_CONFIG_BUNDLE"(ID);
CREATE INDEX SQL130327093508060 ON "PKG_9145E10G"."INGRESS_COS_QUEUE_CONFIG_BUNDLE"(LLQ_CONFIG_ENTRY_ID);
CREATE UNIQUE INDEX SQL130327093437740 ON "PKG_9145E10G"."INGRESS_COS_QUEUE_CONFIG_BUNDLE"(ID);

// 197,363 Rows
CREATE TABLE "PKG_9145E10G"."CONFIGURATION_BUNDLE_9145E10G"
(
   ID int PRIMARY KEY NOT NULL
);
ALTER TABLE "PKG_9145E10G"."CONFIGURATION_BUNDLE_9145E10G"
ADD CONSTRAINT CONFIGURATION_BUNDLE_9145E10G_FK_1
FOREIGN KEY (ID)
REFERENCES "CORE_V1"."CONFIGURATION_BUNDLE"(ID);
CREATE UNIQUE INDEX SQL100226025356610 ON "PKG_9145E10G"."CONFIGURATION_BUNDLE_9145E10G"(ID);
CREATE INDEX SQL100922215631540 ON "PKG_9145E10G"."CONFIGURATION_BUNDLE_9145E10G"(ID);

// 6,740,327 Rows
CREATE TABLE "CORE_V1"."CONFIGURATION_BUNDLE"
(
   DTYPE varchar(64),
   OPLOCK int DEFAULT 0 NOT NULL,
   BUNDLE_NAME varchar(64) NOT NULL,
   ID int PRIMARY KEY NOT NULL
);
CREATE INDEX CONFIGURATION_BUNDLE_IX_2 ON "CORE_V1"."CONFIGURATION_BUNDLE"(BUNDLE_NAME);
CREATE INDEX CONFIGURATION_BUNDLE_IX_1 ON "CORE_V1"."CONFIGURATION_BUNDLE"(DTYPE);
CREATE UNIQUE INDEX SQL100922215131580 ON "CORE_V1"."CONFIGURATION_BUNDLE"(ID);


For some reason, the optimizer is doing a table scan across the CORE_V1.CONFIGURATIONSET_CONFIGURATIONBUNDLE
table
which has 64 million rows and it even goes across this 4 times!.   

One issue is that this query is generated by Eclipselink (JPA) and I have no control on the
query generation. 

So any ideas on why it would chose this path and what I might be able to do about it?   In
playing
around and trying to find the issue, I notice that if I remove the 

    t0.DTYPE = 'INGRESS_COS_QUEUE_CONFIG_BUNDLE_9145E10G')

constraint, then the query returns 0 rows (it should even with this constraint as there are
no rows
that are acceptable) and returns them in about .05 seconds.   Note CORE_V1.CONFIGURATION_BUNDLE
does
have a non-unique index on DTYPE.

Ok, just on a whim, I removed the index on DTYPE and the query came back .06 seconds. That
column is used by Eclipselink in determining which of a "derived" object to return so I don't
have much control on it.

----
Statement Name: 
	SQL_CURLH000C1
Statement Text: 
	SELECT t0.ID, t0.DTYPE, t0.BUNDLE_NAME, t0.OPLOCK, t1.ID, t2.ID, t2.LLQ_CONFIG_ENTRY_ID FROM
CORE_V1.CONFIGURATIONSET_CONFIGURATIONBUNDLE t3, PKG_9145E10G.INGRESS_COS_QUEUE_CONFIG_BUNDLE
t2, PKG_9145E10G.CONFIGURATION_BUNDLE_9145E10G t1, CORE_V1.CONFIGURATION_BUNDLE t0 WHERE (((t3.CONFIGURATIONSET_ID
= 4000) AND (t0.ID = t3.CONFIGURATIONBUNDLE_ID)) AND (((t2.ID = t0.ID) AND (t1.ID = t0.ID))
AND (t0.DTYPE = 'INGRESS_COS_QUEUE_CONFIG_BUNDLE_9145E10G')))
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text: 
Project-Restrict ResultSet (9):
Number of opens = 1
Rows seen = 0
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: 0.00
	optimizer estimated cost: 8.05
Source result set:
	Nested Loop Join ResultSet:
	Number of opens = 1
	Rows seen from the left = 0
	Rows seen from the right = 0
	Rows filtered = 0
	Rows returned = 0
		constructor time (milliseconds) = 0
		open time (milliseconds) = 0
		next time (milliseconds) = 0
		close time (milliseconds) = 0
		optimizer estimated row count: 0.00
		optimizer estimated cost: 8.05
	Left result set:
		Nested Loop Join ResultSet:
		Number of opens = 1
		Rows seen from the left = 0
		Rows seen from the right = 0
		Rows filtered = 0
		Rows returned = 0
			constructor time (milliseconds) = 0
			open time (milliseconds) = 0
			next time (milliseconds) = 0
			close time (milliseconds) = 0
			optimizer estimated row count: 0.00
			optimizer estimated cost: 8.05
		Left result set:
			Nested Loop Join ResultSet:
			Number of opens = 1
			Rows seen from the left = 4
			Rows seen from the right = 0
			Rows filtered = 0
			Rows returned = 0
				constructor time (milliseconds) = 0
				open time (milliseconds) = 0
				next time (milliseconds) = 0
				close time (milliseconds) = 0
				optimizer estimated row count: 0.00
				optimizer estimated cost: 8.05
			Left result set:
				Index Row to Base Row ResultSet for CONFIGURATION_BUNDLE:
				Number of opens = 1
				Rows seen = 4
				Columns accessed from heap = {0, 1, 2, 3}
					constructor time (milliseconds) = 0
					open time (milliseconds) = 0
					next time (milliseconds) = 0
					close time (milliseconds) = 0
					optimizer estimated row count: 0.00
					optimizer estimated cost: 8.05
					Index Scan ResultSet for CONFIGURATION_BUNDLE using index CONFIGURATION_BUNDLE_IX_1 at
read committed isolation level using instantaneous share row locking chosen by the optimizer
					Number of opens = 1
					Rows seen = 4
					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=5
						Number of rows qualified=4
						Number of rows visited=5
						Scan type=btree
						Tree height=5
						start position:
							>= on first 1 column(s).
							Ordered null semantics on the following columns: 
						stop position:
							> on first 1 column(s).
							Ordered null semantics on the following columns: 
						qualifiers:
							None
						optimizer estimated row count: 0.00
						optimizer estimated cost: 8.05

			Right result set:
				Table Scan ResultSet for CONFIGURATIONSET_CONFIGURATIONBUNDLE at read committed isolation
level using instantaneous share row locking chosen by the optimizer
				Number of opens = 4
				Rows seen = 0
				Rows filtered = 0
				Fetch Size = 16
					constructor time (milliseconds) = 0
					open time (milliseconds) = 0
					next time (milliseconds) = 0
					close time (milliseconds) = 0

				scan information:
					Bit set of columns fetched=All
					Number of columns fetched=2
					Number of pages visited=670838
					Number of rows qualified=0
					Number of rows visited=262429876
					Scan type=heap
					start position:
						null
					stop position:
						null
					qualifiers:
						Column[0][0] Id: 0
						Operator: =
						Ordered nulls: false
						Unknown return value: false
						Negate comparison result: false
						Column[0][1] Id: 1
						Operator: =
						Ordered nulls: false
						Unknown return value: false
						Negate comparison result: false
					optimizer estimated row count: 0.00
					optimizer estimated cost: 0.00

		Right result set:
			Table Scan ResultSet for INGRESS_COS_QUEUE_CONFIG_BUNDLE at read committed isolation level
using instantaneous share row locking chosen by the optimizer
			Number of opens = 0
			Rows seen = 0
			Rows filtered = 0
			Fetch Size = 16
				constructor time (milliseconds) = 0
				open time (milliseconds) = 0
				next time (milliseconds) = 0
				close time (milliseconds) = 0

			scan information:
				start position:
					null
				stop position:
					null
				qualifiers:
					Column[0][0] Id: 0
					Operator: =
					Ordered nulls: false
					Unknown return value: false
					Negate comparison result: false
				optimizer estimated row count: 0.00
				optimizer estimated cost: 0.00

	Right result set:
		Table Scan ResultSet for CONFIGURATION_BUNDLE_9145E10G at read committed isolation level
using instantaneous share row locking chosen by the optimizer
		Number of opens = 0
		Rows seen = 0
		Rows filtered = 0
		Fetch Size = 16
			constructor time (milliseconds) = 0
			open time (milliseconds) = 0
			next time (milliseconds) = 0
			close time (milliseconds) = 0

		scan information:
			start position:
				null
			stop position:
				null
			qualifiers:
				Column[0][0] Id: 0
				Operator: =
				Ordered nulls: false
				Unknown return value: false
				Negate comparison result: false
			optimizer estimated row count: 0.00
			optimizer estimated cost: 0.00





Mime
View raw message