db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mike Matrigali (JIRA)" <j...@apache.org>
Subject [jira] [Comment Edited] (DERBY-6226) enhance optmizer to use multiple probes into multiple indexes to satisfy OR queries on different columns.
Date Thu, 23 May 2013 22:47:19 GMT

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

Mike Matrigali edited comment on DERBY-6226 at 5/23/13 10:45 PM:
-----------------------------------------------------------------

For a repro of this issue see the testDerby6045DeleteTest() in InListMultiProbeTest.java.
 This test currently is incorrect.  It assumes that because it
found a index scan that there is no problem.  But what is really happening is that there is
an index scan, but it is a full index scan with no start/stop (ie. no key probe).
Our tools using junit to verify plans are pretty limited, hopefully we can fix test to better
about verifying that an index scan is used and it is not doing a full index
scan.

high level description of the test is that it creates table/indexes as follows:
s.executeUpdate(CREATE_DERBY_6045_DATA_TABLE2);
            s.executeUpdate("ALTER TABLE " + DERBY_6045_DATA_TABLE2 +
                    " ADD CONSTRAINT kb_mt_gaf_top_level_term_counts_pk" +
                    " PRIMARY KEY (mt, term, term_index)");
            s.executeUpdate("CREATE INDEX " +
                    "kb_mt_gaf_top_level_term_counts_mt_index "+
                    "ON " + DERBY_6045_DATA_TABLE2+"(mt)");
            s.executeUpdate("CREATE INDEX " +
                    "kb_mt_gaf_top_level_term_counts_term_index "+
                    "ON " + DERBY_6045_DATA_TABLE2+"(term)");

And executes delete as follows:

            s.execute("DELETE FROM mt_gaf_top_level_term_counts WHERE (term = 5)
 OR (mt = 6)");

The query plan picks the index with both term and mt in it, and does a full index scan.

Below is a print out of the full queryplan showing that the index scan has no start/stop keys,
see:
start position:
                    None
stop position:
                    None

Also note number of rows visited:
Number of rows visited=10000

Here is full query plan I got by adding in a System.out.println("rtsp = " + rtsp); when the
plan is gotten in the junit test:

.
(emb)lang.InListMultiProbeTest.testDerby6045DeleteTest rtsp = Statement Name:
    null
Statement Text:
    DELETE FROM mt_gaf_top_level_term_counts WHERE (term = 5) OR (mt = 6)
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:
Delete ResultSet using row locking:
deferred: false
Rows deleted = 2
Indexes updated = 3
Execute Time = 0
    Project-Restrict ResultSet (2):
    Number of opens = 1
    Rows seen = 2
    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: 3916.00
        optimizer estimated cost: 2847.96
    Source result set:
        Project-Restrict ResultSet (1):
        Number of opens = 1
        Rows seen = 2
        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: 3916.00
            optimizer estimated cost: 2847.96
        Source result set:
            Index Scan ResultSet for MT_GAF_TOP_LEVEL_TERM_COUNTS using constraint KB_MT_GAF_TOP_LEVEL_TERM_COUNTS_PK
at read committed isolation level usin
g exclusive row locking chosen by the optimizer
            Number of opens = 1
            Rows seen = 2
            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, 1, 2, 3}
                Number of columns fetched=4
                Number of deleted rows visited=1
                Number of pages visited=107
                Number of rows qualified=2
                Number of rows visited=10000
                Scan type=btree
                Tree height=3
                start position:
                    None
                stop position:
                    None
                qualifiers:
                    Column[1][0] Id: 1
                    Operator: =
                    Ordered nulls: false
                    Unknown return value: false
                    Negate comparison result: false
                    Column[1][1] Id: 0
                    Operator: =
                    Ordered nulls: false
                    Unknown return value: false
                    Negate comparison result: false
                optimizer estimated row count: 3916.00
                optimizer estimated cost: 2847.96



                
      was (Author: mikem):
    For a repro of this issue see the testDerby6045DeleteTest() in InListMultiProbeTest.java.
 This test currently is incorrect.  It assumes that because it
found a index scan that there is no problem.  But what is really happening is that there is
an index scan, but it is a full index scan with no start/stop (ie. no key probe).
Our tools using junit to verify plans are pretty limited, hopefully we can fix test to better
about verifying that an index scan is used and it is not doing a full index
scan.

Below is a print out of the full queryplan showing that the index scan has not start/stop
keys, see:
start position:
                    None
stop position:
                    None

Also note number of rows visited:
Number of rows visited=10000

Here is full query plan I got by adding in a System.out.println("rtsp = " + rtsp); when the
plan is gotten in the junit test:

.
(emb)lang.InListMultiProbeTest.testDerby6045DeleteTest rtsp = Statement Name:
    null
Statement Text:
    DELETE FROM mt_gaf_top_level_term_counts WHERE (term = 5) OR (mt = 6)
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:
Delete ResultSet using row locking:
deferred: false
Rows deleted = 2
Indexes updated = 3
Execute Time = 0
    Project-Restrict ResultSet (2):
    Number of opens = 1
    Rows seen = 2
    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: 3916.00
        optimizer estimated cost: 2847.96
    Source result set:
        Project-Restrict ResultSet (1):
        Number of opens = 1
        Rows seen = 2
        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: 3916.00
            optimizer estimated cost: 2847.96
        Source result set:
            Index Scan ResultSet for MT_GAF_TOP_LEVEL_TERM_COUNTS using constraint KB_MT_GAF_TOP_LEVEL_TERM_COUNTS_PK
at read committed isolation level usin
g exclusive row locking chosen by the optimizer
            Number of opens = 1
            Rows seen = 2
            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, 1, 2, 3}
                Number of columns fetched=4
                Number of deleted rows visited=1
                Number of pages visited=107
                Number of rows qualified=2
                Number of rows visited=10000
                Scan type=btree
                Tree height=3
                start position:
                    None
                stop position:
                    None
                qualifiers:
                    Column[1][0] Id: 1
                    Operator: =
                    Ordered nulls: false
                    Unknown return value: false
                    Negate comparison result: false
                    Column[1][1] Id: 0
                    Operator: =
                    Ordered nulls: false
                    Unknown return value: false
                    Negate comparison result: false
                optimizer estimated row count: 3916.00
                optimizer estimated cost: 2847.96



                  
> enhance optmizer to use multiple probes into multiple indexes to satisfy OR queries on
different columns.
> ---------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-6226
>                 URL: https://issues.apache.org/jira/browse/DERBY-6226
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions: 10.8.3.1, 10.11.0.0
>            Reporter: Mike Matrigali
>
> For queries of the type:
> select * from a where col1 = ? or col2 = ?
> and good indexes exist on col1 and col2 it would optimal if derby could execute a plan
that
> did an index scan first on the col1 index and then an index scan on col2 index.  
> Currently it looks like derby will do a full index scan if col1 and col2 are in any index,
or a full table
> scan otherwise.

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