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: I need help on getting Derby to use a primary key index on a query
Date Wed, 21 Aug 2013 19:28:43 GMT
Some experiments and hopefully I can get some feedback.

I exported the two tables of interest out to text files, created a new database and importing
them back in using the derby system procedures to unsure that I am not seeing something because
of database corruption.

I updated the statistics and then ran the same query on the new database with only these two
tables.   The results are the same, the query using an index scan on COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY
to find the exact one row and then does a table scan on CONFIGURATION_BUNDLE examining 7 million
rows.

So I tried another test.  I created a new COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY2
table and inserted only the one row in it and updated the query to use this new table and
then ran the query.   The query used an index scan on COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY2
and then magically it used an index scan on CONFIGURATION_BUNDLE to retrieve that one row.

So what I am seeing is that when COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY
has many more rows (about 8 time as much with 58 million rows) than CONFIGURATION_BUNDLE which
has 7 million rows, then the optimizer is performing a table scan on CONFIGURATION_BUNDLE,
but when COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY has many fewer rows
than CONFIGURATION_BUNDLE, the optimizer choses an index scan on CONFIGURATION_BUNDLE.

Note that the join order is the same no matter what with the COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY
being the left result set and CONFIGURATION_BUNDLE being the right result set no matter the
number of rows in COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY.

Any thoughts or comments on why this the optimizer chooses on over the other?

Also, I am following Rick Helligas suggestion of trying a development build to have it output
more optimizer information.   But there seems to be a pretty severe problem with the optimizer
in 10.9/10.10 releases.

On Aug 20, 2013, at 8:29 PM, "Bergquist, Brett" <BBergquist@canoga.com<mailto:BBergquist@canoga.com>>
wrote:

I need some help.  I have a database that is old and has been through multiple upgrades of
Derby.  Now we are using Derby 10.9.1.0.

I have the following query which is not using an index that it should and is instead using
a table scan.   Here is the query:

SELECT * FROM CORE_V1.CONFIGURATION_BUNDLE T1,
PKG_9145E_V1.COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY t0
WHERE t1.ID = t0.COSEDDROPPROFILEDSCPTABLEBUNDLE_ID and
t0.COSEDDROPPROFILEDSCPTABLEENTRY_ID = 12344444

Here are the table definitions:

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 TABLE "PKG_9145E_V1"."COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY"
(
   COSEDDROPPROFILEDSCPTABLEBUNDLE_ID int NOT NULL,
   COSEDDROPPROFILEDSCPTABLEENTRY_ID int NOT NULL,
   CONSTRAINT COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY_PK PRIMARY KEY
(COSEDDROPPROFILEDSCPTABLEBUNDLE_ID,COSEDDROPPROFILEDSCPTABLEENTRY_ID)
)
;
ALTER TABLE "PKG_9145E_V1"."COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY"
ADD CONSTRAINT COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY_FK_1
FOREIGN KEY (COSEDDROPPROFILEDSCPTABLEBUNDLE_ID)
REFERENCES "CORE_V1"."CONFIGURATION_BUNDLE"(ID)
;
ALTER TABLE "PKG_9145E_V1"."COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY"
ADD CONSTRAINT COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY_FK_2
FOREIGN KEY (COSEDDROPPROFILEDSCPTABLEENTRY_ID)
REFERENCES "PKG_9145E_V1"."COS_ED_DROP_PROFILE_DSCP_QMAPPING"(ID)
;

The count of the values in the table are:

SELECT COUNT(*) FROM CORE_V1.CONFIGURATION_BUNDLE;
7003481

SELECT COUNT(*) FROM PKG_9145E_V1.COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY
58248128

When I run the above query, it uses the correct index on PKG_9145E_V1.COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY
(the backing index for the COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY_FK_2
constraint) but then when it queries CORE_V1.CONFIGURATION_BUNDLE, it does a table scan and
does not use the primary key index so it iterates over 7 million records.  If I force the
query to use the primary key index using the optimizer overrides like:

SELECT * FROM CORE_V1.CONFIGURATION_BUNDLE T1 --DERBY-PROPERTIES index=SQL100922215131580
,
PKG_9145E_V1.COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY t0
WHERE t1.ID = t0.COSEDDROPPROFILEDSCPTABLEBUNDLE_ID and
t0.COSEDDROPPROFILEDSCPTABLEENTRY_ID = 12344444;

Then the query is instantaneous as expected.  Below is the execution plan.

Any idea on how to get the Derby to pick the correct plan as the query is generated from JPA
and I cannot put in optimizer overrides.   I have tried using "syscs_util.syscs_drop_statistics"
and "syscs_util.syscs_update_statistics" and also tried "syscs_util.syscs_compress_table",
just trying to get Derby to use the correct index.

Execution plan:

Statement Name:
                SQL_CURLH000C6
Statement Text:
                select * FROM
PKG_9145E_V1.COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY t0
JOIN CORE_V1.CONFIGURATION_BUNDLE t1 on t0.COSEDDROPPROFILEDSCPTABLEBUNDLE_ID = t1.ID
and t0.COSEDDROPPROFILEDSCPTABLEENTRY_ID = 12344444
Parse Time: 1
Bind Time: 2
Optimize Time: 5
Generate Time: 3
Compile Time: 11
Execute Time: 9964
Begin Compilation Timestamp : 2013-08-20 20:15:49.296
End Compilation Timestamp : 2013-08-20 20:15:49.307
Begin Execution Timestamp : 2013-08-20 20:23:05.544
End Execution Timestamp : 2013-08-20 20:23:15.51
Statement Execution Plan Text:
Nested Loop 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) = 9964
                close time (milliseconds) = 0
                optimizer estimated row count: 0.00
                optimizer estimated cost: 6.29
Left result set:
                Index Row to Base Row ResultSet for COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY:
                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) = 1
                                optimizer estimated row count: 0.00
                                optimizer estimated cost: 6.29
                                Index Scan ResultSet for COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY
using constraint COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY_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) = 1
                                                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=4
                                                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: 0.00
                                                optimizer estimated cost: 6.29

Right result set:
                Table Scan ResultSet for CONFIGURATION_BUNDLE 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) = 9964
                                close time (milliseconds) = 0
                                next time in milliseconds/row = 9964

                scan information:
                                Bit set of columns fetched=All
                                Number of columns fetched=4
                                Number of pages visited=197281
                                Number of rows qualified=1
                                Number of rows visited=7003481
                                Scan type=heap
                                start position:
                                                null
                                stop position:
                                                null
                                qualifiers:
                                                Column[0][0] Id: 3
                                                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