You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Rick Hillegas (JIRA)" <de...@db.apache.org> on 2005/10/26 15:48:55 UTC
[jira] Created: (DERBY-649) Useful indexes not used in UNION ALL
Useful indexes not used in UNION ALL
------------------------------------
Key: DERBY-649
URL: http://issues.apache.org/jira/browse/DERBY-649
Project: Derby
Type: Bug
Reporter: Rick Hillegas
Frederic Moreau reports (http://mail-archives.apache.org/mod_mbox/db-derby-user/200510.mbox/browser):
Hello,
The optimizer does not take my indexes into account when I do a select on
a 'UNION ALL' type of view ; therefore, table scans are done and
performances are bad.
Note : my indexes are taken into account if I try equivalent selects on
tables (instead of views).
Please find below a sample illustrating the problem using the
RUNTIMESTATISTICS calls.
Could anynone help me on this subject ?
Thank you.
My cloudscape version is the 10.0.2.1 one.
I also tried it on the 10.1.1.0 version (same result).
c:\>java -classpath
"%CLOUDSCAPE_INSTALL%\lib\derbyclient.jar;%CLOUDSCAPE_INSTALL%\lib\derbytools.jar"
-Dij.driver=org.apache.derby.jdbc.ClientDriver
-Dij.protocol=jdbc:derby://localhost:1527/ -Dij.user=APP -Dij.password=APP
-Dij.maximumDisplayWidth=32768 org.apache.derby.tools.ij ij> connect
'testdb' ;
ij> ;
ij> create table test.table1(a integer, b integer, c integer);
ij> create index test.table1idx on test.table1(b);
ij> ;
ij> create table test.table2(a integer, b integer, c integer);
ij> create index test.table2idx on test.table2(b);
ij> ;
ij> create view test.view0 as select all a,b from test.table1 union all
select a,b from test.table2;
ij> ;
ij> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
ij> select a from test.table1 where b=25;
ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
...
Index Scan ResultSet for TABLE1 using index TABLE1IDX at read
committed isolation level using instantaneous share row locking chosen by
the optimizer
...
ij> select a from test.table2 where b=25;
ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
...
Index Scan ResultSet for TABLE2 using index TABLE2IDX at read
committed isolation level using instantaneous share row locking chosen by
the optimizer
...
ij> select a from test.view0 where b=25;
ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
...
Table Scan ResultSet for TABLE1 at read committed isolation level
using share row locking chosen by the optimizer
...
Table Scan ResultSet for TABLE2 at read committed isolation level
using share row locking chosen by the optimizer
...
ij> ;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Closed: (DERBY-649) Useful indexes not used in UNION ALL
Posted by "Andrew McIntyre (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-649?page=all ]
Andrew McIntyre closed DERBY-649:
---------------------------------
Resolution: Fixed
> Useful indexes not used in UNION ALL
> ------------------------------------
>
> Key: DERBY-649
> URL: http://issues.apache.org/jira/browse/DERBY-649
> Project: Derby
> Type: Bug
> Reporter: Rick Hillegas
> Fix For: 10.2.0.0, 10.1.3.0, 10.1.2.2
> Attachments: DERBY-649.patch, DERBY-649.stat
>
> Frederic Moreau reports (http://mail-archives.apache.org/mod_mbox/db-derby-user/200510.mbox/browser):
> Hello,
> The optimizer does not take my indexes into account when I do a select on
> a 'UNION ALL' type of view ; therefore, table scans are done and
> performances are bad.
> Note : my indexes are taken into account if I try equivalent selects on
> tables (instead of views).
> Please find below a sample illustrating the problem using the
> RUNTIMESTATISTICS calls.
> Could anynone help me on this subject ?
> Thank you.
> My cloudscape version is the 10.0.2.1 one.
> I also tried it on the 10.1.1.0 version (same result).
> c:\>java -classpath
> "%CLOUDSCAPE_INSTALL%\lib\derbyclient.jar;%CLOUDSCAPE_INSTALL%\lib\derbytools.jar"
> -Dij.driver=org.apache.derby.jdbc.ClientDriver
> -Dij.protocol=jdbc:derby://localhost:1527/ -Dij.user=APP -Dij.password=APP
> -Dij.maximumDisplayWidth=32768 org.apache.derby.tools.ij ij> connect
> 'testdb' ;
> ij> ;
> ij> create table test.table1(a integer, b integer, c integer);
> ij> create index test.table1idx on test.table1(b);
> ij> ;
> ij> create table test.table2(a integer, b integer, c integer);
> ij> create index test.table2idx on test.table2(b);
> ij> ;
> ij> create view test.view0 as select all a,b from test.table1 union all
> select a,b from test.table2;
> ij> ;
> ij> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
> ij> select a from test.table1 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Index Scan ResultSet for TABLE1 using index TABLE1IDX at read
> committed isolation level using instantaneous share row locking chosen by
> the optimizer
> ...
> ij> select a from test.table2 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Index Scan ResultSet for TABLE2 using index TABLE2IDX at read
> committed isolation level using instantaneous share row locking chosen by
> the optimizer
> ...
> ij> select a from test.view0 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Table Scan ResultSet for TABLE1 at read committed isolation level
> using share row locking chosen by the optimizer
> ...
> Table Scan ResultSet for TABLE2 at read committed isolation level
> using share row locking chosen by the optimizer
> ...
> ij> ;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-649) Useful indexes not used in UNION ALL
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-649?page=comments#action_12363153 ]
Satheesh Bandaram commented on DERBY-649:
-----------------------------------------
Fix ported from 10.1 branch to trunk.
Sending java\engine\org\apache\derby\impl\sql\compile\PredicateList.java
Sending java\engine\org\apache\derby\impl\sql\compile\ProjectRestrictNode.java
Sending java\engine\org\apache\derby\impl\sql\compile\SelectNode.java
Sending java\engine\org\apache\derby\impl\sql\compile\UnionNode.java
Sending java\testing\org\apache\derbyTesting\functionTests\master\predicatesIntoViews.out
Sending java\testing\org\apache\derbyTesting\functionTests\tests\lang\predicatesIntoViews.sql
Transmitting file data ......
Committed revision 370247.
> Useful indexes not used in UNION ALL
> ------------------------------------
>
> Key: DERBY-649
> URL: http://issues.apache.org/jira/browse/DERBY-649
> Project: Derby
> Type: Bug
> Reporter: Rick Hillegas
> Attachments: DERBY-649.patch, DERBY-649.stat
>
> Frederic Moreau reports (http://mail-archives.apache.org/mod_mbox/db-derby-user/200510.mbox/browser):
> Hello,
> The optimizer does not take my indexes into account when I do a select on
> a 'UNION ALL' type of view ; therefore, table scans are done and
> performances are bad.
> Note : my indexes are taken into account if I try equivalent selects on
> tables (instead of views).
> Please find below a sample illustrating the problem using the
> RUNTIMESTATISTICS calls.
> Could anynone help me on this subject ?
> Thank you.
> My cloudscape version is the 10.0.2.1 one.
> I also tried it on the 10.1.1.0 version (same result).
> c:\>java -classpath
> "%CLOUDSCAPE_INSTALL%\lib\derbyclient.jar;%CLOUDSCAPE_INSTALL%\lib\derbytools.jar"
> -Dij.driver=org.apache.derby.jdbc.ClientDriver
> -Dij.protocol=jdbc:derby://localhost:1527/ -Dij.user=APP -Dij.password=APP
> -Dij.maximumDisplayWidth=32768 org.apache.derby.tools.ij ij> connect
> 'testdb' ;
> ij> ;
> ij> create table test.table1(a integer, b integer, c integer);
> ij> create index test.table1idx on test.table1(b);
> ij> ;
> ij> create table test.table2(a integer, b integer, c integer);
> ij> create index test.table2idx on test.table2(b);
> ij> ;
> ij> create view test.view0 as select all a,b from test.table1 union all
> select a,b from test.table2;
> ij> ;
> ij> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
> ij> select a from test.table1 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Index Scan ResultSet for TABLE1 using index TABLE1IDX at read
> committed isolation level using instantaneous share row locking chosen by
> the optimizer
> ...
> ij> select a from test.table2 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Index Scan ResultSet for TABLE2 using index TABLE2IDX at read
> committed isolation level using instantaneous share row locking chosen by
> the optimizer
> ...
> ij> select a from test.view0 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Table Scan ResultSet for TABLE1 at read committed isolation level
> using share row locking chosen by the optimizer
> ...
> Table Scan ResultSet for TABLE2 at read committed isolation level
> using share row locking chosen by the optimizer
> ...
> ij> ;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-649) Useful indexes not used in UNION ALL
Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-649?page=comments#action_12361063 ]
Daniel John Debrunner commented on DERBY-649:
---------------------------------------------
Submiited a modified version of the patch thus removing my veto for this and the DERBY-772 patch.
> Useful indexes not used in UNION ALL
> ------------------------------------
>
> Key: DERBY-649
> URL: http://issues.apache.org/jira/browse/DERBY-649
> Project: Derby
> Type: Bug
> Reporter: Rick Hillegas
> Attachments: DERBY-649.patch, DERBY-649.stat
>
> Frederic Moreau reports (http://mail-archives.apache.org/mod_mbox/db-derby-user/200510.mbox/browser):
> Hello,
> The optimizer does not take my indexes into account when I do a select on
> a 'UNION ALL' type of view ; therefore, table scans are done and
> performances are bad.
> Note : my indexes are taken into account if I try equivalent selects on
> tables (instead of views).
> Please find below a sample illustrating the problem using the
> RUNTIMESTATISTICS calls.
> Could anynone help me on this subject ?
> Thank you.
> My cloudscape version is the 10.0.2.1 one.
> I also tried it on the 10.1.1.0 version (same result).
> c:\>java -classpath
> "%CLOUDSCAPE_INSTALL%\lib\derbyclient.jar;%CLOUDSCAPE_INSTALL%\lib\derbytools.jar"
> -Dij.driver=org.apache.derby.jdbc.ClientDriver
> -Dij.protocol=jdbc:derby://localhost:1527/ -Dij.user=APP -Dij.password=APP
> -Dij.maximumDisplayWidth=32768 org.apache.derby.tools.ij ij> connect
> 'testdb' ;
> ij> ;
> ij> create table test.table1(a integer, b integer, c integer);
> ij> create index test.table1idx on test.table1(b);
> ij> ;
> ij> create table test.table2(a integer, b integer, c integer);
> ij> create index test.table2idx on test.table2(b);
> ij> ;
> ij> create view test.view0 as select all a,b from test.table1 union all
> select a,b from test.table2;
> ij> ;
> ij> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
> ij> select a from test.table1 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Index Scan ResultSet for TABLE1 using index TABLE1IDX at read
> committed isolation level using instantaneous share row locking chosen by
> the optimizer
> ...
> ij> select a from test.table2 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Index Scan ResultSet for TABLE2 using index TABLE2IDX at read
> committed isolation level using instantaneous share row locking chosen by
> the optimizer
> ...
> ij> select a from test.view0 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Table Scan ResultSet for TABLE1 at read committed isolation level
> using share row locking chosen by the optimizer
> ...
> Table Scan ResultSet for TABLE2 at read committed isolation level
> using share row locking chosen by the optimizer
> ...
> ij> ;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
Re: [jira] Commented: (DERBY-649) Useful indexes not used in UNION
ALL
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Satheesh Bandaram wrote:
> Thanks for reviewing the patch.
>
> Daniel John Debrunner wrote:
>
>>Satheesh Bandaram (JIRA) wrote:
>>And closely related, in the code (UnionNode.java) you say
>>'For now, we only push simple
>>+ * single column predicates that are binaryOperations.'
>>
>>Does this need to be clarified? Maybe state explictly what can be pushed
>>down, maybe 'simple' to you, would be 'complex' to me?
>>
>>
> OK. I will expand the comments on this.
>
>>Could you explain new the code in pushExpressionsIntoSelect? Since you
>>have spent time on this, it would be great to capture your thoughts in
>>the code comments. For example, why do we need a new nodes that
>>represent '= TRUE', I'm sure it's required but to a reader of the code
>>it's not obvious why.
>>
>>
> OK. I will add more comments.
Are you still planning to add these comments at some time?
It seems the committed patch did not include comments in this area.
Even though the patch was reverted I'm sure it will come back with the
correct fix and it would be good to capture your original thoughts when
you created the patch.
Thanks,
Dan.
Re: [jira] Commented: (DERBY-649) Useful indexes not used in UNION
ALL
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Satheesh Bandaram (JIRA) wrote:
> [ http://issues.apache.org/jira/browse/DERBY-649?page=comments#action_12360485 ]
>
> Satheesh Bandaram commented on DERBY-649:
> -----------------------------------------
>
> I have a patch for this bug. While I am still testing it, I would appreciate any comments about my approach. I have a customer who is hurting a lot because of this bug. Once complete, I would like to put this fix into both 10.1.x and trunk versions.
>
> I have implemented a simpler solution to address this optimization for some cases where predicates are of the form <ColumnReference> <RELOP> <constant>. These causes really benifit the most by pushing predicates down these into inner selects of union, since that would enable optimizer to use applicable indexes on them.
>
> I will also enhance this patch to make it more generic for trunk later.
When you say <ColumnReference> <RELOP> <constant>, can the constant be
a parameter?
And closely related, in the code (UnionNode.java) you say
'For now, we only push simple
+ * single column predicates that are binaryOperations.'
Does this need to be clarified? Maybe state explictly what can be pushed
down, maybe 'simple' to you, would be 'complex' to me?
Could you explain new the code in pushExpressionsIntoSelect? Since you
have spent time on this, it would be great to capture your thoughts in
the code comments. For example, why do we need a new nodes that
represent '= TRUE', I'm sure it's required but to a reader of the code
it's not obvious why.
Great patch to improve performance!
Dan.
[jira] Commented: (DERBY-649) Useful indexes not used in UNION ALL
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-649?page=comments#action_12360485 ]
Satheesh Bandaram commented on DERBY-649:
-----------------------------------------
I have a patch for this bug. While I am still testing it, I would appreciate any comments about my approach. I have a customer who is hurting a lot because of this bug. Once complete, I would like to put this fix into both 10.1.x and trunk versions.
I have implemented a simpler solution to address this optimization for some cases where predicates are of the form <ColumnReference> <RELOP> <constant>. These causes really benifit the most by pushing predicates down these into inner selects of union, since that would enable optimizer to use applicable indexes on them.
I will also enhance this patch to make it more generic for trunk later.
> Useful indexes not used in UNION ALL
> ------------------------------------
>
> Key: DERBY-649
> URL: http://issues.apache.org/jira/browse/DERBY-649
> Project: Derby
> Type: Bug
> Reporter: Rick Hillegas
>
> Frederic Moreau reports (http://mail-archives.apache.org/mod_mbox/db-derby-user/200510.mbox/browser):
> Hello,
> The optimizer does not take my indexes into account when I do a select on
> a 'UNION ALL' type of view ; therefore, table scans are done and
> performances are bad.
> Note : my indexes are taken into account if I try equivalent selects on
> tables (instead of views).
> Please find below a sample illustrating the problem using the
> RUNTIMESTATISTICS calls.
> Could anynone help me on this subject ?
> Thank you.
> My cloudscape version is the 10.0.2.1 one.
> I also tried it on the 10.1.1.0 version (same result).
> c:\>java -classpath
> "%CLOUDSCAPE_INSTALL%\lib\derbyclient.jar;%CLOUDSCAPE_INSTALL%\lib\derbytools.jar"
> -Dij.driver=org.apache.derby.jdbc.ClientDriver
> -Dij.protocol=jdbc:derby://localhost:1527/ -Dij.user=APP -Dij.password=APP
> -Dij.maximumDisplayWidth=32768 org.apache.derby.tools.ij ij> connect
> 'testdb' ;
> ij> ;
> ij> create table test.table1(a integer, b integer, c integer);
> ij> create index test.table1idx on test.table1(b);
> ij> ;
> ij> create table test.table2(a integer, b integer, c integer);
> ij> create index test.table2idx on test.table2(b);
> ij> ;
> ij> create view test.view0 as select all a,b from test.table1 union all
> select a,b from test.table2;
> ij> ;
> ij> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
> ij> select a from test.table1 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Index Scan ResultSet for TABLE1 using index TABLE1IDX at read
> committed isolation level using instantaneous share row locking chosen by
> the optimizer
> ...
> ij> select a from test.table2 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Index Scan ResultSet for TABLE2 using index TABLE2IDX at read
> committed isolation level using instantaneous share row locking chosen by
> the optimizer
> ...
> ij> select a from test.view0 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Table Scan ResultSet for TABLE1 at read committed isolation level
> using share row locking chosen by the optimizer
> ...
> Table Scan ResultSet for TABLE2 at read committed isolation level
> using share row locking chosen by the optimizer
> ...
> ij> ;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Closed: (DERBY-649) Useful indexes not used in UNION ALL
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-649?page=all ]
Satheesh Bandaram closed DERBY-649:
-----------------------------------
Fix has been verified against customer database and query on 10.2 and 10.1.
> Useful indexes not used in UNION ALL
> ------------------------------------
>
> Key: DERBY-649
> URL: http://issues.apache.org/jira/browse/DERBY-649
> Project: Derby
> Type: Bug
> Reporter: Rick Hillegas
> Fix For: 10.2.0.0, 10.1.3.0, 10.1.2.2
> Attachments: DERBY-649.patch, DERBY-649.stat
>
> Frederic Moreau reports (http://mail-archives.apache.org/mod_mbox/db-derby-user/200510.mbox/browser):
> Hello,
> The optimizer does not take my indexes into account when I do a select on
> a 'UNION ALL' type of view ; therefore, table scans are done and
> performances are bad.
> Note : my indexes are taken into account if I try equivalent selects on
> tables (instead of views).
> Please find below a sample illustrating the problem using the
> RUNTIMESTATISTICS calls.
> Could anynone help me on this subject ?
> Thank you.
> My cloudscape version is the 10.0.2.1 one.
> I also tried it on the 10.1.1.0 version (same result).
> c:\>java -classpath
> "%CLOUDSCAPE_INSTALL%\lib\derbyclient.jar;%CLOUDSCAPE_INSTALL%\lib\derbytools.jar"
> -Dij.driver=org.apache.derby.jdbc.ClientDriver
> -Dij.protocol=jdbc:derby://localhost:1527/ -Dij.user=APP -Dij.password=APP
> -Dij.maximumDisplayWidth=32768 org.apache.derby.tools.ij ij> connect
> 'testdb' ;
> ij> ;
> ij> create table test.table1(a integer, b integer, c integer);
> ij> create index test.table1idx on test.table1(b);
> ij> ;
> ij> create table test.table2(a integer, b integer, c integer);
> ij> create index test.table2idx on test.table2(b);
> ij> ;
> ij> create view test.view0 as select all a,b from test.table1 union all
> select a,b from test.table2;
> ij> ;
> ij> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
> ij> select a from test.table1 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Index Scan ResultSet for TABLE1 using index TABLE1IDX at read
> committed isolation level using instantaneous share row locking chosen by
> the optimizer
> ...
> ij> select a from test.table2 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Index Scan ResultSet for TABLE2 using index TABLE2IDX at read
> committed isolation level using instantaneous share row locking chosen by
> the optimizer
> ...
> ij> select a from test.view0 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Table Scan ResultSet for TABLE1 at read committed isolation level
> using share row locking chosen by the optimizer
> ...
> Table Scan ResultSet for TABLE2 at read committed isolation level
> using share row locking chosen by the optimizer
> ...
> ij> ;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Reopened: (DERBY-649) Useful indexes not used in UNION ALL
Posted by "Andrew McIntyre (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-649?page=all ]
Andrew McIntyre reopened DERBY-649:
-----------------------------------
> Useful indexes not used in UNION ALL
> ------------------------------------
>
> Key: DERBY-649
> URL: http://issues.apache.org/jira/browse/DERBY-649
> Project: Derby
> Type: Bug
> Reporter: Rick Hillegas
> Fix For: 10.2.0.0, 10.1.3.0, 10.1.2.2
> Attachments: DERBY-649.patch, DERBY-649.stat
>
> Frederic Moreau reports (http://mail-archives.apache.org/mod_mbox/db-derby-user/200510.mbox/browser):
> Hello,
> The optimizer does not take my indexes into account when I do a select on
> a 'UNION ALL' type of view ; therefore, table scans are done and
> performances are bad.
> Note : my indexes are taken into account if I try equivalent selects on
> tables (instead of views).
> Please find below a sample illustrating the problem using the
> RUNTIMESTATISTICS calls.
> Could anynone help me on this subject ?
> Thank you.
> My cloudscape version is the 10.0.2.1 one.
> I also tried it on the 10.1.1.0 version (same result).
> c:\>java -classpath
> "%CLOUDSCAPE_INSTALL%\lib\derbyclient.jar;%CLOUDSCAPE_INSTALL%\lib\derbytools.jar"
> -Dij.driver=org.apache.derby.jdbc.ClientDriver
> -Dij.protocol=jdbc:derby://localhost:1527/ -Dij.user=APP -Dij.password=APP
> -Dij.maximumDisplayWidth=32768 org.apache.derby.tools.ij ij> connect
> 'testdb' ;
> ij> ;
> ij> create table test.table1(a integer, b integer, c integer);
> ij> create index test.table1idx on test.table1(b);
> ij> ;
> ij> create table test.table2(a integer, b integer, c integer);
> ij> create index test.table2idx on test.table2(b);
> ij> ;
> ij> create view test.view0 as select all a,b from test.table1 union all
> select a,b from test.table2;
> ij> ;
> ij> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
> ij> select a from test.table1 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Index Scan ResultSet for TABLE1 using index TABLE1IDX at read
> committed isolation level using instantaneous share row locking chosen by
> the optimizer
> ...
> ij> select a from test.table2 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Index Scan ResultSet for TABLE2 using index TABLE2IDX at read
> committed isolation level using instantaneous share row locking chosen by
> the optimizer
> ...
> ij> select a from test.view0 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Table Scan ResultSet for TABLE1 at read committed isolation level
> using share row locking chosen by the optimizer
> ...
> Table Scan ResultSet for TABLE2 at read committed isolation level
> using share row locking chosen by the optimizer
> ...
> ij> ;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
Re: [jira] Updated: (DERBY-649) Useful indexes not used in UNION
ALL
Posted by Satheesh Bandaram <sa...@Sourcery.Org>.
Hi Rick,
Thanks for your review... Like I already mentioned, I am still working
on making this patch more generic. Since I am putting this patch into
10.1 branch, I wanted to be cautious. I thought pushing down predicates
that could make optimizer use indices are more important and is my
current itch. (Because of huge performance gain and an immediate
requirement)
Rick Hillegas wrote:
> Hi Satheesh,
>
> I have taken a quick look at your patch. It's a very useful
> improvement. Your approach was very cautious. It might be useful to
> comment the code with an explanation of why you decided to be so
> cautious.
>
> It appears that your patch does the following for a UNION with a top
> level predicate list:
>
> o Copies each predicate to all the branches of the UNION while
> retaining the predicate in the top level list. Why do you need to
> retain the top level predicates once they have been cloned and pushed
> down?
It is possible not to have the predicate at the top-level once I know
for sure the predicateList is pushed to every branch successfully. But
my current implementation doesn't address many cases in the UnionNode
(only handles SelectNodes) and until we expand the logic to others, it
is required.
> o Performs this push-down only for predicates with a specific shape.
> Shouldn't the whole predicate list be evaluatable on every branch of
> the UNION? Why do you limit this patch to one specific shape of
> predicate?
This is the area I will be improving in follow up patches, as the
comments I have added indicate. There are many complications possible.
For example, for the following query
Select * from (select i, j from t union all select a, count(*) from t1
group by a) myTab(c, d) where d = 5
what would "d=5" mean to second inner select? Where would you add it?
Satheesh
> Thanks,
> -Rick
>
> Satheesh Bandaram wrote:
>
>> Ouch... Rick... I have submitted my fix, based on Dan's comments. But I
>> will address any input you may have. I am still working on enhancing the
>> patch to cover more cases.
>>
>> Satheesh
>>
>> Rick Hillegas wrote:
>>
>>
>>
>>> Hi Satheesh,
>>>
>>> I will take a look at this later on today. I'm down with a cold and so
>>> only intermittently vertical.
>>>
>>> Cheers,
>>> -Rick
>>>
>>> Satheesh Bandaram (JIRA) wrote:
>>>
>>>
>>>
>>>> [ http://issues.apache.org/jira/browse/DERBY-649?page=all ]
>>>>
>>>> Satheesh Bandaram updated DERBY-649:
>>>> ------------------------------------
>>>>
>>>> Attachment: DERBY-649.stat
>>>> DERBY-649.patch
>>>>
>>>> First version of the patch. I am still testing and enhancing the
>>>> patch. Appreciate any comments.
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>> Useful indexes not used in UNION ALL
>>>>> ------------------------------------
>>>>>
>>>>> Key: DERBY-649
>>>>> URL: http://issues.apache.org/jira/browse/DERBY-649
>>>>> Project: Derby
>>>>> Type: Bug
>>>>> Reporter: Rick Hillegas
>>>>> Attachments: DERBY-649.patch, DERBY-649.stat
>>>>>
>>>>> Frederic Moreau reports
>>>>> (http://mail-archives.apache.org/mod_mbox/db-derby-user/200510.mbox/browser):
>>>>>
>>>>>
>>>>> Hello,
>>>>> The optimizer does not take my indexes into account when I do a
>>>>> select on a 'UNION ALL' type of view ; therefore, table scans are
>>>>> done and performances are bad.
>>>>> Note : my indexes are taken into account if I try equivalent selects
>>>>> on tables (instead of views).
>>>>> Please find below a sample illustrating the problem using the
>>>>> RUNTIMESTATISTICS calls.
>>>>> Could anynone help me on this subject ?
>>>>> Thank you.
>>>>> My cloudscape version is the 10.0.2.1 one.
>>>>> I also tried it on the 10.1.1.0 version (same result).
>>>>> c:\>java -classpath
>>>>> "%CLOUDSCAPE_INSTALL%\lib\derbyclient.jar;%CLOUDSCAPE_INSTALL%\lib\derbytools.jar"
>>>>>
>>>>> -Dij.driver=org.apache.derby.jdbc.ClientDriver
>>>>> -Dij.protocol=jdbc:derby://localhost:1527/ -Dij.user=APP
>>>>> -Dij.password=APP -Dij.maximumDisplayWidth=32768
>>>>> org.apache.derby.tools.ij ij> connect 'testdb' ;
>>>>> ij> ;
>>>>> ij> create table test.table1(a integer, b integer, c integer);
>>>>> ij> create index test.table1idx on test.table1(b);
>>>>> ij> ;
>>>>> ij> create table test.table2(a integer, b integer, c integer);
>>>>> ij> create index test.table2idx on test.table2(b);
>>>>> ij> ;
>>>>> ij> create view test.view0 as select all a,b from test.table1 union
>>>>> all select a,b from test.table2;
>>>>> ij> ;
>>>>> ij> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
>>>>> ij> select a from test.table1 where b=25;
>>>>> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
>>>>> ...
>>>>> Index Scan ResultSet for TABLE1 using index TABLE1IDX at read
>>>>> committed isolation level using instantaneous share row locking
>>>>> chosen by the optimizer
>>>>> ...
>>>>> ij> select a from test.table2 where b=25;
>>>>> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
>>>>> ...
>>>>> Index Scan ResultSet for TABLE2 using index TABLE2IDX at read
>>>>> committed isolation level using instantaneous share row locking
>>>>> chosen by the optimizer
>>>>> ...
>>>>> ij> select a from test.view0 where b=25;
>>>>> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
>>>>> ...
>>>>> Table Scan ResultSet for TABLE1 at read committed isolation
>>>>> level using share row locking chosen by the optimizer
>>>>> ...
>>>>> Table Scan ResultSet for TABLE2 at read committed isolation
>>>>> level using share row locking chosen by the optimizer
>>>>> ...
>>>>> ij> ;
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>
>>
>>
>>
>
>
>
Re: [jira] Updated: (DERBY-649) Useful indexes not used in UNION ALL
Posted by Rick Hillegas <Ri...@Sun.COM>.
Hi Satheesh,
I have taken a quick look at your patch. It's a very useful improvement.
Your approach was very cautious. It might be useful to comment the code
with an explanation of why you decided to be so cautious.
It appears that your patch does the following for a UNION with a top
level predicate list:
o Copies each predicate to all the branches of the UNION while retaining
the predicate in the top level list. Why do you need to retain the top
level predicates once they have been cloned and pushed down?
o Performs this push-down only for predicates with a specific shape.
Shouldn't the whole predicate list be evaluatable on every branch of the
UNION? Why do you limit this patch to one specific shape of predicate?
Thanks,
-Rick
Satheesh Bandaram wrote:
>Ouch... Rick... I have submitted my fix, based on Dan's comments. But I
>will address any input you may have. I am still working on enhancing the
>patch to cover more cases.
>
>Satheesh
>
>Rick Hillegas wrote:
>
>
>
>>Hi Satheesh,
>>
>>I will take a look at this later on today. I'm down with a cold and so
>>only intermittently vertical.
>>
>>Cheers,
>>-Rick
>>
>>Satheesh Bandaram (JIRA) wrote:
>>
>>
>>
>>> [ http://issues.apache.org/jira/browse/DERBY-649?page=all ]
>>>
>>>Satheesh Bandaram updated DERBY-649:
>>>------------------------------------
>>>
>>> Attachment: DERBY-649.stat
>>> DERBY-649.patch
>>>
>>>First version of the patch. I am still testing and enhancing the
>>>patch. Appreciate any comments.
>>>
>>>
>>>
>>>
>>>
>>>>Useful indexes not used in UNION ALL
>>>>------------------------------------
>>>>
>>>> Key: DERBY-649
>>>> URL: http://issues.apache.org/jira/browse/DERBY-649
>>>> Project: Derby
>>>> Type: Bug
>>>> Reporter: Rick Hillegas
>>>>Attachments: DERBY-649.patch, DERBY-649.stat
>>>>
>>>>Frederic Moreau reports
>>>>(http://mail-archives.apache.org/mod_mbox/db-derby-user/200510.mbox/browser):
>>>>
>>>>Hello,
>>>>The optimizer does not take my indexes into account when I do a
>>>>select on a 'UNION ALL' type of view ; therefore, table scans are
>>>>done and performances are bad.
>>>>Note : my indexes are taken into account if I try equivalent selects
>>>>on tables (instead of views).
>>>>Please find below a sample illustrating the problem using the
>>>>RUNTIMESTATISTICS calls.
>>>>Could anynone help me on this subject ?
>>>>Thank you.
>>>>My cloudscape version is the 10.0.2.1 one.
>>>>I also tried it on the 10.1.1.0 version (same result).
>>>> c:\>java -classpath
>>>>"%CLOUDSCAPE_INSTALL%\lib\derbyclient.jar;%CLOUDSCAPE_INSTALL%\lib\derbytools.jar"
>>>>-Dij.driver=org.apache.derby.jdbc.ClientDriver
>>>>-Dij.protocol=jdbc:derby://localhost:1527/ -Dij.user=APP
>>>>-Dij.password=APP -Dij.maximumDisplayWidth=32768
>>>>org.apache.derby.tools.ij ij> connect 'testdb' ;
>>>> ij> ;
>>>> ij> create table test.table1(a integer, b integer, c integer);
>>>> ij> create index test.table1idx on test.table1(b);
>>>> ij> ;
>>>> ij> create table test.table2(a integer, b integer, c integer);
>>>> ij> create index test.table2idx on test.table2(b);
>>>> ij> ;
>>>> ij> create view test.view0 as select all a,b from test.table1 union
>>>>all select a,b from test.table2;
>>>> ij> ;
>>>> ij> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
>>>> ij> select a from test.table1 where b=25;
>>>> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
>>>> ...
>>>> Index Scan ResultSet for TABLE1 using index TABLE1IDX at read
>>>>committed isolation level using instantaneous share row locking
>>>>chosen by the optimizer
>>>> ...
>>>> ij> select a from test.table2 where b=25;
>>>> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
>>>> ...
>>>> Index Scan ResultSet for TABLE2 using index TABLE2IDX at read
>>>>committed isolation level using instantaneous share row locking
>>>>chosen by the optimizer
>>>> ...
>>>> ij> select a from test.view0 where b=25;
>>>> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
>>>> ...
>>>> Table Scan ResultSet for TABLE1 at read committed isolation
>>>>level using share row locking chosen by the optimizer
>>>> ...
>>>> Table Scan ResultSet for TABLE2 at read committed isolation
>>>>level using share row locking chosen by the optimizer
>>>> ...
>>>> ij> ;
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>
>
>
Re: [jira] Updated: (DERBY-649) Useful indexes not used in
UNION ALL
Posted by Jeffrey Lichtman <sw...@rcn.com>.
I haven't had the time to go over this patch in detail, but I do have
a basic question and some comments:
Is the pushing of expressions into the union done before optimization
or during optimization? If it's done before optimization, then only
single-table expressions involving the union are pushable. But if
it's done during optimization, it would be possible to push joins
into the union as long as it's in the right place in the join order.
For example:
create view v as select * from t1 union all select * from t2;
select * from v, t3 where v.c1 = t3.c2;
In this select, if t3 is the outer table then the qualification could
be pushed into the union and optimized there, but if t3 is the inner
table the qualification can't be pushed into the union.
If the pushing is done at preprocess time (i.e. before optimization)
it is impossible to know whether a join qualification like this can
be safely pushed.
There's a comment in UnionNode.optimizeIt() saying:
/* RESOLVE - don't try to push predicated through for now */
This is where I'd expect to see something for pushing predicates into
the union during optimization.
BTW, the business of pushing and pulling predicates during
optimization can be hard to understand and debug, so maybe it's best
to only handle the simple cases and do it during preprocessing.
Another question: should this fix be limited to unions? What about
other set operations like INTERSECT?
- Jeff Lichtman
swazoo@rcn.com
Check out Swazoo Koolak's Web Jukebox at
http://swazoo.com/
Re: [jira] Updated: (DERBY-649) Useful indexes not used in UNION
ALL
Posted by Satheesh Bandaram <sa...@Sourcery.Org>.
Daniel John Debrunner wrote:
>Wow, I'm glad I pestered. This was a useful exchange, found a bug in the
>patch, I learnt something about predicates & search clauses, and
>potentially it lead you to find another performance bug related to
>parameters. Thanks for being patient with me.
>
>
Thank you.. for finding one issue.
>Just quickly thinking about the checking for ConstantNode you mention, I
>wonder if that is the correct approach. I thought a ValueExpression node
>had a method that indicated its invariance, thus checking if a value is
>statement (or query?) invariant should be used instead of it being an
>instanceof ConstantNode or ParameterNode. That would mean in the future
>functions that were declared deterministic on invariant values could
>also be seen as constants for such optimizations. That's probably a
>future cleanup, but it would be a much better approach than having these
>instanceof checks.
>
>
I have to look at this some more... It is the right approach, but I
don't see this being done in other similar places. Same ignorance? I
have to make sure.
>Not sure the patch at this point in time has to address pushing down
>'3 = a' if the column reference does appear on the right in the node
>tree, but I'm curious about it.
>
>
I would expect this to be normalized very early, but I will confirm.
Satheesh
>Thanks,
>Dan.
>
>
>
>
>
>
Re: [jira] Updated: (DERBY-649) Useful indexes not used in UNION
ALL
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Satheesh Bandaram wrote:
>
>
> Daniel John Debrunner wrote:
>
>>>>From what I understand, your patch will push predicates like
>>
>> a = ?
>> a = 3
>>
>>but will not push expressions like
>>
>> a = (1 + b)
>> a = cost(c)
>> a = rate()
>>
>>
> You are right... I get confused between predicates and search clauses...
> Search clauses are guaranteed to have a constant on the right, you don't
> have to check for them. But a predicate can have a non-constant
> expression on the right. I should add a check for this. *Thanks *a lot
> for catching this. BTW, I see search transitive closure optimizations
> checking for ConstantNode, but not ParameterNode. I wonder if we are
> missing some possible optimizations where a parameter marker is used,
> instead of a ConstantNode. This would could make PreparedStatements go
> slower than Statements, when most people would expect it other way. I
> will investigate and file a bug.
Wow, I'm glad I pestered. This was a useful exchange, found a bug in the
patch, I learnt something about predicates & search clauses, and
potentially it lead you to find another performance bug related to
parameters. Thanks for being patient with me.
Just quickly thinking about the checking for ConstantNode you mention, I
wonder if that is the correct approach. I thought a ValueExpression node
had a method that indicated its invariance, thus checking if a value is
statement (or query?) invariant should be used instead of it being an
instanceof ConstantNode or ParameterNode. That would mean in the future
functions that were declared deterministic on invariant values could
also be seen as constants for such optimizations. That's probably a
future cleanup, but it would be a much better approach than having these
instanceof checks.
> I will submit this small patch:
Looks good, but it leads to my next dumb question.
For these type of expressions, are they normalized so that the
column reference is always on the left? I.e. will 'a = 3' lead
to the same nodes as '3 = a'?
Not sure the patch at this point in time has to address pushing down
'3 = a' if the column reference does appear on the right in the node
tree, but I'm curious about it.
Thanks,
Dan.
Re: [jira] Updated: (DERBY-649) Useful indexes not used in UNION
ALL
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Satheesh Bandaram wrote:
>>And finally one real dumb question, in
>>PredicateList.pushExpressionsIntoSelect I see where you ensure the
>>predicate is a binary operator and has a column reference, but I can't
>>see where the 'don't push expressions' logic is?
>>
>>
>>
>
> By not calling pushExpressionsIntoSelect(), the code doesn't push
> expressions... Where ever I find I currently can't handle the situation,
> calling 'continue' in the loop skips pushing that predicate.
I don't think I was clear enough.
>From what I understand, your patch will push predicates like
a = ?
a = 3
but will not push expressions like
a = (1 + b)
a = cost(c)
a = rate()
I don't see where predicates of the first type are distinguished from
those of the second type. I'm sure I'm being dumb and it's staring me in
the face.
In PredicateList.pushExpressionsIntoSelect I do see:
line 1418 - a comment indicating (to me) that there is logic here to
only push "simple" predicates.
line 1421 - check to see the predicate is a binary relational node
line 1425 - check to see the left is a column reference
line 1431 - see if the column is referenced in where it is being pushed to
but I don't see any code that says the right is "simple" so do push, or
the right is "complex" so don't push.
This is as much for my education on the optimizer as well as trying to
understand the patch completely.
Dan.
Re: [jira] Updated: (DERBY-649) Useful indexes not used in UNION
ALL
Posted by Satheesh Bandaram <sa...@Sourcery.Org>.
Some answers below... Unless you spend sometime with complex areas like
optimizer, it is pretty difficult to understand what one function or a
specific change is trying to do in the larger scheme of things. I do see
some points here about comments that I will improve in the code.
Daniel John Debrunner wrote:
>I would avoid usin subjective words like 'simple', what does that
>really mean?
>
>It's may be better to explicitly state what type of predicates are being
>pushed down, rather than what isn't being pushed down. Always clearer to
>state what is being done, a positive, that what is not being done, a
>negative. Not sure if I read the comments as they stand, that it's
>totally clear what can be pushed down. I think the code is hampered
>somewhat by existing names, e.g. methods called pushExpressesion, and
>then you say that expressions cannot be pushed down, but something is
>being pushed down in that method! Did the original author really mean
>pushPredicate or am I just confused. :-)
>
>
PushExpression() tries to push an expression and could decide it can't,
for many reasons. If a predicate qualifies some of the conditions, it
would get pushed. (or copied for union case)
>And even though I'm asking for more comments, in some cases one can have
>too many comments. In this patch, in several places, you talk about the
>type of predicates that cannot be pushed down, but it seems to me it has
>no relevance to the method being commented. This confuses me, as I look
>for logic matching the comments in the body of the method. Examples are:
>
> Method: findColumnReferenceInResult
> "Handle the case of single table selects for now. Also if there
>is an expression under the result column, it is not possible yet to push
>the predicates for now."
> I don't see any matching logic in the method.
>
>
OK. I will cleanup comments that don't belong here. I guess I have mixed
comments about what a function does and how it is used by the caller. I
can separe the two.
> Method: UnionNode.pushExpressions
> All the comment is about what expressions can't be pushed,
>but it's not decided in this method, but elsewhere.
>
>These "stray" comments have the problem of being very confusing some
>time from now, especially if the restriction is lifted but no-one
>updates the method with the stray comment as it is not required to be
>modified for the fix. It' also makes other coders very nervous to change
>such code, as they can't see where the restriction is being enforced,
>therefore they think it's some magic they don't understand.
>
>
>
Again, the same problem as above.. mixing the use with what this
function does. I think in this case, it may be useful to list what kind
of predicates a unionNode can currently push and what it can't, because
this is the main entry point that is specific to unions. Each type of
node can only handle some specific types of predicates and it is good to
list them at the entry point, though the decisions are made later.
>And finally one real dumb question, in
>PredicateList.pushExpressionsIntoSelect I see where you ensure the
>predicate is a binary operator and has a column reference, but I can't
>see where the 'don't push expressions' logic is?
>
>
>
By not calling pushExpressionsIntoSelect(), the code doesn't push
expressions... Where ever I find I currently can't handle the situation,
calling 'continue' in the loop skips pushing that predicate.
>Also in the same method, looking at the parameter copyPredicate I was
>expecting to see code that copied a predicate, but really as the comment
>below says, it's re-mapping the predicate.
>
>
We can call it remap if that makes more sense.. I would interpret remap
to mean "change this predicate to point else where". That is not the
case. We are creating a new predicate that matches current one with a
new columnReference. So, is this copy or remap or something else?
>One of my current itches is to improve general knowledge of the
>optimizer, and it seems with the good work and investigation you did for
>this fix, it's a great chance to ensure the resulting code with comments
>is clear while it is still fresh in your mind.
>
>Another one of my beliefs is that correctly named methods, classes,
>fields, variables etc. greatly help in understanding the code.
>
>Don't take this as critism, but instead just showing that code &
>comments you think is somewhat obvious because you are an expert in the
>area, may not appear that way to others.
>
>
>
I don't mind changing method names or variables... if you have any
specific suggestion, I will consider... If I knew a better name, I would
have used it already .. :-)
Satheesh
>Thanks,
>Dan.
>
>
>
>
>
>
>
>
>
>
>
>
>
Re: [jira] Updated: (DERBY-649) Useful indexes not used in UNION
ALL
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Satheesh Bandaram wrote:
> Ouch... Rick... I have submitted my fix, based on Dan's comments. But I
> will address any input you may have. I am still working on enhancing the
> patch to cover more cases.
Just a few more comments on the comments in this patch. This could be
seen as more in the way of general advice. Good, clear, relevant
comments are essential for more people joining Derby development. The
following is really a dump of my thoughts as I read the comments in the
code for this patch.
I would avoid using subjective words like 'simple', what does that
really mean?
It's may be better to explicitly state what type of predicates are being
pushed down, rather than what isn't being pushed down. Always clearer to
state what is being done, a positive, that what is not being done, a
negative. Not sure if I read the comments as they stand, that it's
totally clear what can be pushed down. I think the code is hampered
somewhat by existing names, e.g. methods called pushExpressesion, and
then you say that expressions cannot be pushed down, but something is
being pushed down in that method! Did the original author really mean
pushPredicate or am I just confused. :-)
And even though I'm asking for more comments, in some cases one can have
too many comments. In this patch, in several places, you talk about the
type of predicates that cannot be pushed down, but it seems to me it has
no relevance to the method being commented. This confuses me, as I look
for logic matching the comments in the body of the method. Examples are:
Method: findColumnReferenceInResult
"Handle the case of single table selects for now. Also if there
is an expression under the result column, it is not possible yet to push
the predicates for now."
I don't see any matching logic in the method.
Method: UnionNode.pushExpressions
All the comment is about what expressions can't be pushed,
but it's not decided in this method, but elsewhere.
These "stray" comments have the problem of being very confusing some
time from now, especially if the restriction is lifted but no-one
updates the method with the stray comment as it is not required to be
modified for the fix. It' also makes other coders very nervous to change
such code, as they can't see where the restriction is being enforced,
therefore they think it's some magic they don't understand.
And finally one real dumb question, in
PredicateList.pushExpressionsIntoSelect I see where you ensure the
predicate is a binary operator and has a column reference, but I can't
see where the 'don't push expressions' logic is?
Also in the same method, looking at the parameter copyPredicate I was
expecting to see code that copied a predicate, but really as the comment
below says, it's re-mapping the predicate.
One of my current itches is to improve general knowledge of the
optimizer, and it seems with the good work and investigation you did for
this fix, it's a great chance to ensure the resulting code with comments
is clear while it is still fresh in your mind.
Another one of my beliefs is that correctly named methods, classes,
fields, variables etc. greatly help in understanding the code.
Don't take this as critism, but instead just showing that code &
comments you think is somewhat obvious because you are an expert in the
area, may not appear that way to others.
Thanks,
Dan.
Re: [jira] Updated: (DERBY-649) Useful indexes not used in UNION
ALL
Posted by Satheesh Bandaram <sa...@Sourcery.Org>.
Ouch... Rick... I have submitted my fix, based on Dan's comments. But I
will address any input you may have. I am still working on enhancing the
patch to cover more cases.
Satheesh
Rick Hillegas wrote:
> Hi Satheesh,
>
> I will take a look at this later on today. I'm down with a cold and so
> only intermittently vertical.
>
> Cheers,
> -Rick
>
> Satheesh Bandaram (JIRA) wrote:
>
>> [ http://issues.apache.org/jira/browse/DERBY-649?page=all ]
>>
>> Satheesh Bandaram updated DERBY-649:
>> ------------------------------------
>>
>> Attachment: DERBY-649.stat
>> DERBY-649.patch
>>
>> First version of the patch. I am still testing and enhancing the
>> patch. Appreciate any comments.
>>
>>
>>
>>> Useful indexes not used in UNION ALL
>>> ------------------------------------
>>>
>>> Key: DERBY-649
>>> URL: http://issues.apache.org/jira/browse/DERBY-649
>>> Project: Derby
>>> Type: Bug
>>> Reporter: Rick Hillegas
>>> Attachments: DERBY-649.patch, DERBY-649.stat
>>>
>>> Frederic Moreau reports
>>> (http://mail-archives.apache.org/mod_mbox/db-derby-user/200510.mbox/browser):
>>>
>>> Hello,
>>> The optimizer does not take my indexes into account when I do a
>>> select on a 'UNION ALL' type of view ; therefore, table scans are
>>> done and performances are bad.
>>> Note : my indexes are taken into account if I try equivalent selects
>>> on tables (instead of views).
>>> Please find below a sample illustrating the problem using the
>>> RUNTIMESTATISTICS calls.
>>> Could anynone help me on this subject ?
>>> Thank you.
>>> My cloudscape version is the 10.0.2.1 one.
>>> I also tried it on the 10.1.1.0 version (same result).
>>> c:\>java -classpath
>>> "%CLOUDSCAPE_INSTALL%\lib\derbyclient.jar;%CLOUDSCAPE_INSTALL%\lib\derbytools.jar"
>>> -Dij.driver=org.apache.derby.jdbc.ClientDriver
>>> -Dij.protocol=jdbc:derby://localhost:1527/ -Dij.user=APP
>>> -Dij.password=APP -Dij.maximumDisplayWidth=32768
>>> org.apache.derby.tools.ij ij> connect 'testdb' ;
>>> ij> ;
>>> ij> create table test.table1(a integer, b integer, c integer);
>>> ij> create index test.table1idx on test.table1(b);
>>> ij> ;
>>> ij> create table test.table2(a integer, b integer, c integer);
>>> ij> create index test.table2idx on test.table2(b);
>>> ij> ;
>>> ij> create view test.view0 as select all a,b from test.table1 union
>>> all select a,b from test.table2;
>>> ij> ;
>>> ij> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
>>> ij> select a from test.table1 where b=25;
>>> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
>>> ...
>>> Index Scan ResultSet for TABLE1 using index TABLE1IDX at read
>>> committed isolation level using instantaneous share row locking
>>> chosen by the optimizer
>>> ...
>>> ij> select a from test.table2 where b=25;
>>> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
>>> ...
>>> Index Scan ResultSet for TABLE2 using index TABLE2IDX at read
>>> committed isolation level using instantaneous share row locking
>>> chosen by the optimizer
>>> ...
>>> ij> select a from test.view0 where b=25;
>>> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
>>> ...
>>> Table Scan ResultSet for TABLE1 at read committed isolation
>>> level using share row locking chosen by the optimizer
>>> ...
>>> Table Scan ResultSet for TABLE2 at read committed isolation
>>> level using share row locking chosen by the optimizer
>>> ...
>>> ij> ;
>>>
>>
>>
>>
>>
>
>
>
Re: [jira] Updated: (DERBY-649) Useful indexes not used in UNION ALL
Posted by Rick Hillegas <Ri...@Sun.COM>.
Hi Satheesh,
I will take a look at this later on today. I'm down with a cold and so
only intermittently vertical.
Cheers,
-Rick
Satheesh Bandaram (JIRA) wrote:
> [ http://issues.apache.org/jira/browse/DERBY-649?page=all ]
>
>Satheesh Bandaram updated DERBY-649:
>------------------------------------
>
> Attachment: DERBY-649.stat
> DERBY-649.patch
>
>First version of the patch. I am still testing and enhancing the patch. Appreciate any comments.
>
>
>
>>Useful indexes not used in UNION ALL
>>------------------------------------
>>
>> Key: DERBY-649
>> URL: http://issues.apache.org/jira/browse/DERBY-649
>> Project: Derby
>> Type: Bug
>> Reporter: Rick Hillegas
>> Attachments: DERBY-649.patch, DERBY-649.stat
>>
>>Frederic Moreau reports (http://mail-archives.apache.org/mod_mbox/db-derby-user/200510.mbox/browser):
>>Hello,
>>The optimizer does not take my indexes into account when I do a select on
>>a 'UNION ALL' type of view ; therefore, table scans are done and
>>performances are bad.
>>Note : my indexes are taken into account if I try equivalent selects on
>>tables (instead of views).
>>Please find below a sample illustrating the problem using the
>>RUNTIMESTATISTICS calls.
>>Could anynone help me on this subject ?
>>Thank you.
>>My cloudscape version is the 10.0.2.1 one.
>>I also tried it on the 10.1.1.0 version (same result).
>> c:\>java -classpath
>>"%CLOUDSCAPE_INSTALL%\lib\derbyclient.jar;%CLOUDSCAPE_INSTALL%\lib\derbytools.jar"
>>-Dij.driver=org.apache.derby.jdbc.ClientDriver
>>-Dij.protocol=jdbc:derby://localhost:1527/ -Dij.user=APP -Dij.password=APP
>>-Dij.maximumDisplayWidth=32768 org.apache.derby.tools.ij ij> connect
>>'testdb' ;
>> ij> ;
>> ij> create table test.table1(a integer, b integer, c integer);
>> ij> create index test.table1idx on test.table1(b);
>> ij> ;
>> ij> create table test.table2(a integer, b integer, c integer);
>> ij> create index test.table2idx on test.table2(b);
>> ij> ;
>> ij> create view test.view0 as select all a,b from test.table1 union all
>>select a,b from test.table2;
>> ij> ;
>> ij> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
>> ij> select a from test.table1 where b=25;
>> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
>> ...
>> Index Scan ResultSet for TABLE1 using index TABLE1IDX at read
>>committed isolation level using instantaneous share row locking chosen by
>>the optimizer
>> ...
>> ij> select a from test.table2 where b=25;
>> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
>> ...
>> Index Scan ResultSet for TABLE2 using index TABLE2IDX at read
>>committed isolation level using instantaneous share row locking chosen by
>>the optimizer
>> ...
>> ij> select a from test.view0 where b=25;
>> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
>> ...
>> Table Scan ResultSet for TABLE1 at read committed isolation level
>>using share row locking chosen by the optimizer
>> ...
>> Table Scan ResultSet for TABLE2 at read committed isolation level
>>using share row locking chosen by the optimizer
>> ...
>> ij> ;
>>
>>
>
>
>
[jira] Updated: (DERBY-649) Useful indexes not used in UNION ALL
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-649?page=all ]
Satheesh Bandaram updated DERBY-649:
------------------------------------
Attachment: DERBY-649.stat
DERBY-649.patch
First version of the patch. I am still testing and enhancing the patch. Appreciate any comments.
> Useful indexes not used in UNION ALL
> ------------------------------------
>
> Key: DERBY-649
> URL: http://issues.apache.org/jira/browse/DERBY-649
> Project: Derby
> Type: Bug
> Reporter: Rick Hillegas
> Attachments: DERBY-649.patch, DERBY-649.stat
>
> Frederic Moreau reports (http://mail-archives.apache.org/mod_mbox/db-derby-user/200510.mbox/browser):
> Hello,
> The optimizer does not take my indexes into account when I do a select on
> a 'UNION ALL' type of view ; therefore, table scans are done and
> performances are bad.
> Note : my indexes are taken into account if I try equivalent selects on
> tables (instead of views).
> Please find below a sample illustrating the problem using the
> RUNTIMESTATISTICS calls.
> Could anynone help me on this subject ?
> Thank you.
> My cloudscape version is the 10.0.2.1 one.
> I also tried it on the 10.1.1.0 version (same result).
> c:\>java -classpath
> "%CLOUDSCAPE_INSTALL%\lib\derbyclient.jar;%CLOUDSCAPE_INSTALL%\lib\derbytools.jar"
> -Dij.driver=org.apache.derby.jdbc.ClientDriver
> -Dij.protocol=jdbc:derby://localhost:1527/ -Dij.user=APP -Dij.password=APP
> -Dij.maximumDisplayWidth=32768 org.apache.derby.tools.ij ij> connect
> 'testdb' ;
> ij> ;
> ij> create table test.table1(a integer, b integer, c integer);
> ij> create index test.table1idx on test.table1(b);
> ij> ;
> ij> create table test.table2(a integer, b integer, c integer);
> ij> create index test.table2idx on test.table2(b);
> ij> ;
> ij> create view test.view0 as select all a,b from test.table1 union all
> select a,b from test.table2;
> ij> ;
> ij> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
> ij> select a from test.table1 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Index Scan ResultSet for TABLE1 using index TABLE1IDX at read
> committed isolation level using instantaneous share row locking chosen by
> the optimizer
> ...
> ij> select a from test.table2 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Index Scan ResultSet for TABLE2 using index TABLE2IDX at read
> committed isolation level using instantaneous share row locking chosen by
> the optimizer
> ...
> ij> select a from test.view0 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Table Scan ResultSet for TABLE1 at read committed isolation level
> using share row locking chosen by the optimizer
> ...
> Table Scan ResultSet for TABLE2 at read committed isolation level
> using share row locking chosen by the optimizer
> ...
> ij> ;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Resolved: (DERBY-649) Useful indexes not used in UNION ALL
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-649?page=all ]
Satheesh Bandaram resolved DERBY-649:
-------------------------------------
Fix Version: 10.2.0.0
10.1.3.0
10.1.2.2
Resolution: Fixed
Fix checked into both 10.1 and trunk.
> Useful indexes not used in UNION ALL
> ------------------------------------
>
> Key: DERBY-649
> URL: http://issues.apache.org/jira/browse/DERBY-649
> Project: Derby
> Type: Bug
> Reporter: Rick Hillegas
> Fix For: 10.2.0.0, 10.1.3.0, 10.1.2.2
> Attachments: DERBY-649.patch, DERBY-649.stat
>
> Frederic Moreau reports (http://mail-archives.apache.org/mod_mbox/db-derby-user/200510.mbox/browser):
> Hello,
> The optimizer does not take my indexes into account when I do a select on
> a 'UNION ALL' type of view ; therefore, table scans are done and
> performances are bad.
> Note : my indexes are taken into account if I try equivalent selects on
> tables (instead of views).
> Please find below a sample illustrating the problem using the
> RUNTIMESTATISTICS calls.
> Could anynone help me on this subject ?
> Thank you.
> My cloudscape version is the 10.0.2.1 one.
> I also tried it on the 10.1.1.0 version (same result).
> c:\>java -classpath
> "%CLOUDSCAPE_INSTALL%\lib\derbyclient.jar;%CLOUDSCAPE_INSTALL%\lib\derbytools.jar"
> -Dij.driver=org.apache.derby.jdbc.ClientDriver
> -Dij.protocol=jdbc:derby://localhost:1527/ -Dij.user=APP -Dij.password=APP
> -Dij.maximumDisplayWidth=32768 org.apache.derby.tools.ij ij> connect
> 'testdb' ;
> ij> ;
> ij> create table test.table1(a integer, b integer, c integer);
> ij> create index test.table1idx on test.table1(b);
> ij> ;
> ij> create table test.table2(a integer, b integer, c integer);
> ij> create index test.table2idx on test.table2(b);
> ij> ;
> ij> create view test.view0 as select all a,b from test.table1 union all
> select a,b from test.table2;
> ij> ;
> ij> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
> ij> select a from test.table1 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Index Scan ResultSet for TABLE1 using index TABLE1IDX at read
> committed isolation level using instantaneous share row locking chosen by
> the optimizer
> ...
> ij> select a from test.table2 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Index Scan ResultSet for TABLE2 using index TABLE2IDX at read
> committed isolation level using instantaneous share row locking chosen by
> the optimizer
> ...
> ij> select a from test.view0 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Table Scan ResultSet for TABLE1 at read committed isolation level
> using share row locking chosen by the optimizer
> ...
> Table Scan ResultSet for TABLE2 at read committed isolation level
> using share row locking chosen by the optimizer
> ...
> ij> ;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-649) Useful indexes not used in UNION ALL
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-649?page=comments#action_12358885 ]
Satheesh Bandaram commented on DERBY-649:
-----------------------------------------
I am trying to address this problem by trying to push predicates into UNION. For now, I am not handling the cases where the select list may have expressions and handling only simple column references. I am attempting to push predicates into both left and right ResultSets, much like PredicateList.pushExpressionsIntoSelect handles the case of single SelectNodes. Does this sound like a reasonable approach?
My initial attempts don't seem to make the query use the index... I suspect I am not correctly remapping column references once pushed inside the selects.
> Useful indexes not used in UNION ALL
> ------------------------------------
>
> Key: DERBY-649
> URL: http://issues.apache.org/jira/browse/DERBY-649
> Project: Derby
> Type: Bug
> Reporter: Rick Hillegas
>
> Frederic Moreau reports (http://mail-archives.apache.org/mod_mbox/db-derby-user/200510.mbox/browser):
> Hello,
> The optimizer does not take my indexes into account when I do a select on
> a 'UNION ALL' type of view ; therefore, table scans are done and
> performances are bad.
> Note : my indexes are taken into account if I try equivalent selects on
> tables (instead of views).
> Please find below a sample illustrating the problem using the
> RUNTIMESTATISTICS calls.
> Could anynone help me on this subject ?
> Thank you.
> My cloudscape version is the 10.0.2.1 one.
> I also tried it on the 10.1.1.0 version (same result).
> c:\>java -classpath
> "%CLOUDSCAPE_INSTALL%\lib\derbyclient.jar;%CLOUDSCAPE_INSTALL%\lib\derbytools.jar"
> -Dij.driver=org.apache.derby.jdbc.ClientDriver
> -Dij.protocol=jdbc:derby://localhost:1527/ -Dij.user=APP -Dij.password=APP
> -Dij.maximumDisplayWidth=32768 org.apache.derby.tools.ij ij> connect
> 'testdb' ;
> ij> ;
> ij> create table test.table1(a integer, b integer, c integer);
> ij> create index test.table1idx on test.table1(b);
> ij> ;
> ij> create table test.table2(a integer, b integer, c integer);
> ij> create index test.table2idx on test.table2(b);
> ij> ;
> ij> create view test.view0 as select all a,b from test.table1 union all
> select a,b from test.table2;
> ij> ;
> ij> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
> ij> select a from test.table1 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Index Scan ResultSet for TABLE1 using index TABLE1IDX at read
> committed isolation level using instantaneous share row locking chosen by
> the optimizer
> ...
> ij> select a from test.table2 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Index Scan ResultSet for TABLE2 using index TABLE2IDX at read
> committed isolation level using instantaneous share row locking chosen by
> the optimizer
> ...
> ij> select a from test.view0 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Table Scan ResultSet for TABLE1 at read committed isolation level
> using share row locking chosen by the optimizer
> ...
> Table Scan ResultSet for TABLE2 at read committed isolation level
> using share row locking chosen by the optimizer
> ...
> ij> ;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
Re: [jira] Commented: (DERBY-649) Useful indexes not used in UNION
ALL
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Rick Hillegas (JIRA) wrote:
> [ http://issues.apache.org/jira/browse/DERBY-649?page=comments#action_12361005 ]
>
> Rick Hillegas commented on DERBY-649:
> -------------------------------------
>
> Hi Dan, your fix looks good. I would be tempted to sand it down a bit: Since the new relational operator could be something other than "==", you might want to call it "newRelop" rather than "newEquals".
Yep, that's what I meant by my comment ' changes would also be made to
te variable name, to correctly represent its use.'
newRelop is better than the name I was thinking of.
Thanks,
Dan.
[jira] Commented: (DERBY-649) Useful indexes not used in UNION ALL
Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-649?page=comments#action_12361005 ]
Rick Hillegas commented on DERBY-649:
-------------------------------------
Hi Dan, your fix looks good. I would be tempted to sand it down a bit: Since the new relational operator could be something other than "==", you might want to call it "newRelop" rather than "newEquals".
> Useful indexes not used in UNION ALL
> ------------------------------------
>
> Key: DERBY-649
> URL: http://issues.apache.org/jira/browse/DERBY-649
> Project: Derby
> Type: Bug
> Reporter: Rick Hillegas
> Attachments: DERBY-649.patch, DERBY-649.stat
>
> Frederic Moreau reports (http://mail-archives.apache.org/mod_mbox/db-derby-user/200510.mbox/browser):
> Hello,
> The optimizer does not take my indexes into account when I do a select on
> a 'UNION ALL' type of view ; therefore, table scans are done and
> performances are bad.
> Note : my indexes are taken into account if I try equivalent selects on
> tables (instead of views).
> Please find below a sample illustrating the problem using the
> RUNTIMESTATISTICS calls.
> Could anynone help me on this subject ?
> Thank you.
> My cloudscape version is the 10.0.2.1 one.
> I also tried it on the 10.1.1.0 version (same result).
> c:\>java -classpath
> "%CLOUDSCAPE_INSTALL%\lib\derbyclient.jar;%CLOUDSCAPE_INSTALL%\lib\derbytools.jar"
> -Dij.driver=org.apache.derby.jdbc.ClientDriver
> -Dij.protocol=jdbc:derby://localhost:1527/ -Dij.user=APP -Dij.password=APP
> -Dij.maximumDisplayWidth=32768 org.apache.derby.tools.ij ij> connect
> 'testdb' ;
> ij> ;
> ij> create table test.table1(a integer, b integer, c integer);
> ij> create index test.table1idx on test.table1(b);
> ij> ;
> ij> create table test.table2(a integer, b integer, c integer);
> ij> create index test.table2idx on test.table2(b);
> ij> ;
> ij> create view test.view0 as select all a,b from test.table1 union all
> select a,b from test.table2;
> ij> ;
> ij> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
> ij> select a from test.table1 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Index Scan ResultSet for TABLE1 using index TABLE1IDX at read
> committed isolation level using instantaneous share row locking chosen by
> the optimizer
> ...
> ij> select a from test.table2 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Index Scan ResultSet for TABLE2 using index TABLE2IDX at read
> committed isolation level using instantaneous share row locking chosen by
> the optimizer
> ...
> ij> select a from test.view0 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Table Scan ResultSet for TABLE1 at read committed isolation level
> using share row locking chosen by the optimizer
> ...
> Table Scan ResultSet for TABLE2 at read committed isolation level
> using share row locking chosen by the optimizer
> ...
> ij> ;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-649) Useful indexes not used in UNION ALL
Posted by "Kathey Marsden (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-649?page=all ]
Kathey Marsden updated DERBY-649:
---------------------------------
Component: SQL
> Useful indexes not used in UNION ALL
> ------------------------------------
>
> Key: DERBY-649
> URL: http://issues.apache.org/jira/browse/DERBY-649
> Project: Derby
> Type: Bug
> Components: SQL
> Reporter: Rick Hillegas
> Fix For: 10.2.0.0, 10.1.3.0, 10.1.2.2
> Attachments: DERBY-649.patch, DERBY-649.stat
>
> Frederic Moreau reports (http://mail-archives.apache.org/mod_mbox/db-derby-user/200510.mbox/browser):
> Hello,
> The optimizer does not take my indexes into account when I do a select on
> a 'UNION ALL' type of view ; therefore, table scans are done and
> performances are bad.
> Note : my indexes are taken into account if I try equivalent selects on
> tables (instead of views).
> Please find below a sample illustrating the problem using the
> RUNTIMESTATISTICS calls.
> Could anynone help me on this subject ?
> Thank you.
> My cloudscape version is the 10.0.2.1 one.
> I also tried it on the 10.1.1.0 version (same result).
> c:\>java -classpath
> "%CLOUDSCAPE_INSTALL%\lib\derbyclient.jar;%CLOUDSCAPE_INSTALL%\lib\derbytools.jar"
> -Dij.driver=org.apache.derby.jdbc.ClientDriver
> -Dij.protocol=jdbc:derby://localhost:1527/ -Dij.user=APP -Dij.password=APP
> -Dij.maximumDisplayWidth=32768 org.apache.derby.tools.ij ij> connect
> 'testdb' ;
> ij> ;
> ij> create table test.table1(a integer, b integer, c integer);
> ij> create index test.table1idx on test.table1(b);
> ij> ;
> ij> create table test.table2(a integer, b integer, c integer);
> ij> create index test.table2idx on test.table2(b);
> ij> ;
> ij> create view test.view0 as select all a,b from test.table1 union all
> select a,b from test.table2;
> ij> ;
> ij> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
> ij> select a from test.table1 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Index Scan ResultSet for TABLE1 using index TABLE1IDX at read
> committed isolation level using instantaneous share row locking chosen by
> the optimizer
> ...
> ij> select a from test.table2 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Index Scan ResultSet for TABLE2 using index TABLE2IDX at read
> committed isolation level using instantaneous share row locking chosen by
> the optimizer
> ...
> ij> select a from test.view0 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Table Scan ResultSet for TABLE1 at read committed isolation level
> using share row locking chosen by the optimizer
> ...
> Table Scan ResultSet for TABLE2 at read committed isolation level
> using share row locking chosen by the optimizer
> ...
> ij> ;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-649) Useful indexes not used in UNION ALL
Posted by "Jeff Lichtman (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-649?page=comments#action_12356000 ]
Jeff Lichtman commented on DERBY-649:
-------------------------------------
The problem is probably that the qualification is not being pushed down into the union. In the example script, the select from the view is expanded to:
select a from (select all a,b from test.table1 union all
select a,b from test.table2) tab where b = 25
After this expansion, the compilation phase should push the qualification down into the union:
select a from (select all a,b from test.table1 where b = 25 union all
select a,b from test.table2 where b = 25) tab
This should probably happen in the preprocessing phase.
> Useful indexes not used in UNION ALL
> ------------------------------------
>
> Key: DERBY-649
> URL: http://issues.apache.org/jira/browse/DERBY-649
> Project: Derby
> Type: Bug
> Reporter: Rick Hillegas
>
> Frederic Moreau reports (http://mail-archives.apache.org/mod_mbox/db-derby-user/200510.mbox/browser):
> Hello,
> The optimizer does not take my indexes into account when I do a select on
> a 'UNION ALL' type of view ; therefore, table scans are done and
> performances are bad.
> Note : my indexes are taken into account if I try equivalent selects on
> tables (instead of views).
> Please find below a sample illustrating the problem using the
> RUNTIMESTATISTICS calls.
> Could anynone help me on this subject ?
> Thank you.
> My cloudscape version is the 10.0.2.1 one.
> I also tried it on the 10.1.1.0 version (same result).
> c:\>java -classpath
> "%CLOUDSCAPE_INSTALL%\lib\derbyclient.jar;%CLOUDSCAPE_INSTALL%\lib\derbytools.jar"
> -Dij.driver=org.apache.derby.jdbc.ClientDriver
> -Dij.protocol=jdbc:derby://localhost:1527/ -Dij.user=APP -Dij.password=APP
> -Dij.maximumDisplayWidth=32768 org.apache.derby.tools.ij ij> connect
> 'testdb' ;
> ij> ;
> ij> create table test.table1(a integer, b integer, c integer);
> ij> create index test.table1idx on test.table1(b);
> ij> ;
> ij> create table test.table2(a integer, b integer, c integer);
> ij> create index test.table2idx on test.table2(b);
> ij> ;
> ij> create view test.view0 as select all a,b from test.table1 union all
> select a,b from test.table2;
> ij> ;
> ij> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
> ij> select a from test.table1 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Index Scan ResultSet for TABLE1 using index TABLE1IDX at read
> committed isolation level using instantaneous share row locking chosen by
> the optimizer
> ...
> ij> select a from test.table2 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Index Scan ResultSet for TABLE2 using index TABLE2IDX at read
> committed isolation level using instantaneous share row locking chosen by
> the optimizer
> ...
> ij> select a from test.view0 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Table Scan ResultSet for TABLE1 at read committed isolation level
> using share row locking chosen by the optimizer
> ...
> Table Scan ResultSet for TABLE2 at read committed isolation level
> using share row locking chosen by the optimizer
> ...
> ij> ;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
Re: [jira] Commented: (DERBY-649) Useful indexes not used in UNION
ALL
Posted by Army <qo...@sbcglobal.net>.
Daniel John Debrunner (JIRA) wrote:
>
> I think I've found the bug in the patch. I'd appreciate any optimizer experts
> looking at this.
>
> In PredicateList.pushExpressionsIntoSelect when the predicate is copied,
> any type of binary relational node can be copied, but the new relational
> node create is always an quality node, it is not based upon the type
> being pushed.
[ snip code replacement ]
I'm far from an optimizer expert, but this change makes sense to me and appears
to fulfill the "copy this predicate" notion that this block of code is dealing with.
For what that's worth,
Army
Re: [jira] Commented: (DERBY-649) Useful indexes not used in UNION
ALL
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Satheesh Bandaram wrote:
> Thanks for catching the regression and resolving it so quickly. Is there
> anything you cann't do? :-)
Hey, my bit was the easy bit, spot the typo in the patch given a simple
reproducible case. Had I had to actually fix the problem like you did,
it would have been a different story. :-)
> I thought the reason for having TRUE constant node was because of need
> to have the predicate in CNF form... AndNode is always at the top, which
> assists (or simplifies) logic in the optimizer. I am still not done with
> changes in 10.1 branch. I will add a comment for the need to have
> constant node.
Most likely true, actually I did mess up here by asking why there was a
'= TRUE' construct, when the real construct in the original patch was
'col = value AND TRUE'. If I had looked more carefully I hope the '='
would have jumped out at me.
Many eyes etc.
Dan.
[jira] Commented: (DERBY-649) Useful indexes not used in UNION ALL
Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-649?page=comments#action_12360997 ]
Daniel John Debrunner commented on DERBY-649:
---------------------------------------------
I think I've found the bug in the patch. I'd appreciate any optimizer experts looking at this.
In PredicateList.pushExpressionsIntoSelect when the predicate is copied, any type of binary relational node can be copied, but the new relational node create is always an quality node, it is not based upon the type being pushed.
I'm replacing this code, difference is first argument to getNode() :
around line 1438 - changes would also be made to te variable name, to correctly represent its use.
BinaryRelationalOperatorNode newEquals = (BinaryRelationalOperatorNode)
getNodeFactory().getNode(
C_NodeTypes.BINARY_EQUALS_OPERATOR_NODE,
newCRNode,
opNode.getRightOperand(),
getContextManager());
with
BinaryRelationalOperatorNode newEquals = (BinaryRelationalOperatorNode)
getNodeFactory().getNode(
opNode.getNodeType(),
newCRNode,
opNode.getRightOperand(),
getContextManager());
I'd incorrectly assumed in the review that the (incorrect) new equality node was related to the boolean constant TRUE being created. So sort of '(pushed expression) = TRUE' was being pushed and required for some reason. I knew there was a good reason I'd asked for comments on this code section:
> Dec 15th 08:19
> For example, why do we need a new nodes that
> represent '= TRUE', I'm sure it's required but to a reader of the code
it's not obvious why.
> Useful indexes not used in UNION ALL
> ------------------------------------
>
> Key: DERBY-649
> URL: http://issues.apache.org/jira/browse/DERBY-649
> Project: Derby
> Type: Bug
> Reporter: Rick Hillegas
> Attachments: DERBY-649.patch, DERBY-649.stat
>
> Frederic Moreau reports (http://mail-archives.apache.org/mod_mbox/db-derby-user/200510.mbox/browser):
> Hello,
> The optimizer does not take my indexes into account when I do a select on
> a 'UNION ALL' type of view ; therefore, table scans are done and
> performances are bad.
> Note : my indexes are taken into account if I try equivalent selects on
> tables (instead of views).
> Please find below a sample illustrating the problem using the
> RUNTIMESTATISTICS calls.
> Could anynone help me on this subject ?
> Thank you.
> My cloudscape version is the 10.0.2.1 one.
> I also tried it on the 10.1.1.0 version (same result).
> c:\>java -classpath
> "%CLOUDSCAPE_INSTALL%\lib\derbyclient.jar;%CLOUDSCAPE_INSTALL%\lib\derbytools.jar"
> -Dij.driver=org.apache.derby.jdbc.ClientDriver
> -Dij.protocol=jdbc:derby://localhost:1527/ -Dij.user=APP -Dij.password=APP
> -Dij.maximumDisplayWidth=32768 org.apache.derby.tools.ij ij> connect
> 'testdb' ;
> ij> ;
> ij> create table test.table1(a integer, b integer, c integer);
> ij> create index test.table1idx on test.table1(b);
> ij> ;
> ij> create table test.table2(a integer, b integer, c integer);
> ij> create index test.table2idx on test.table2(b);
> ij> ;
> ij> create view test.view0 as select all a,b from test.table1 union all
> select a,b from test.table2;
> ij> ;
> ij> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
> ij> select a from test.table1 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Index Scan ResultSet for TABLE1 using index TABLE1IDX at read
> committed isolation level using instantaneous share row locking chosen by
> the optimizer
> ...
> ij> select a from test.table2 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Index Scan ResultSet for TABLE2 using index TABLE2IDX at read
> committed isolation level using instantaneous share row locking chosen by
> the optimizer
> ...
> ij> select a from test.view0 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Table Scan ResultSet for TABLE1 at read committed isolation level
> using share row locking chosen by the optimizer
> ...
> Table Scan ResultSet for TABLE2 at read committed isolation level
> using share row locking chosen by the optimizer
> ...
> ij> ;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-649) Useful indexes not used in UNION ALL
Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-649?page=comments#action_12360982 ]
Daniel John Debrunner commented on DERBY-649:
---------------------------------------------
-1 on the patch, causes a regression. Will also vote -1 on the subsequent patch for 772 as it is dependent on this patch.
Run this simple script, at svn revision 357054 the output is expected including the
two argument IN clause on the select from the view returning two rows.
Once this patch is applied, svn revision 357105, that select returns no rows.
DROP VIEW V1;
DROP TABLE D1;
CREATE TABLE D1 (A INT, B VARCHAR(4) FOR BIT DATA);
INSERT INTO D1 VALUES (1, x'600Eaaef') ;
INSERT INTO D1 VALUES (2, x'83452213') ;
select * from D1 where B IN (x'600Eaaef',x'83452213') ;
select * from D1 where B IN (x'83452213') ;
select * from D1 where B IN (x'600Eaaef') ;
CREATE VIEW V1 AS SELECT A,B FROM D1 UNION SELECT A,B FROM D1;
SELECT * FROM V1;
-- this fails! returns no rows
select * from V1 where B IN (x'600Eaaef',x'83452213') ;
select * from V1 where B IN (x'83452213') ;
select * from V1 where B IN (x'600Eaaef') ;
> Useful indexes not used in UNION ALL
> ------------------------------------
>
> Key: DERBY-649
> URL: http://issues.apache.org/jira/browse/DERBY-649
> Project: Derby
> Type: Bug
> Reporter: Rick Hillegas
> Attachments: DERBY-649.patch, DERBY-649.stat
>
> Frederic Moreau reports (http://mail-archives.apache.org/mod_mbox/db-derby-user/200510.mbox/browser):
> Hello,
> The optimizer does not take my indexes into account when I do a select on
> a 'UNION ALL' type of view ; therefore, table scans are done and
> performances are bad.
> Note : my indexes are taken into account if I try equivalent selects on
> tables (instead of views).
> Please find below a sample illustrating the problem using the
> RUNTIMESTATISTICS calls.
> Could anynone help me on this subject ?
> Thank you.
> My cloudscape version is the 10.0.2.1 one.
> I also tried it on the 10.1.1.0 version (same result).
> c:\>java -classpath
> "%CLOUDSCAPE_INSTALL%\lib\derbyclient.jar;%CLOUDSCAPE_INSTALL%\lib\derbytools.jar"
> -Dij.driver=org.apache.derby.jdbc.ClientDriver
> -Dij.protocol=jdbc:derby://localhost:1527/ -Dij.user=APP -Dij.password=APP
> -Dij.maximumDisplayWidth=32768 org.apache.derby.tools.ij ij> connect
> 'testdb' ;
> ij> ;
> ij> create table test.table1(a integer, b integer, c integer);
> ij> create index test.table1idx on test.table1(b);
> ij> ;
> ij> create table test.table2(a integer, b integer, c integer);
> ij> create index test.table2idx on test.table2(b);
> ij> ;
> ij> create view test.view0 as select all a,b from test.table1 union all
> select a,b from test.table2;
> ij> ;
> ij> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
> ij> select a from test.table1 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Index Scan ResultSet for TABLE1 using index TABLE1IDX at read
> committed isolation level using instantaneous share row locking chosen by
> the optimizer
> ...
> ij> select a from test.table2 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Index Scan ResultSet for TABLE2 using index TABLE2IDX at read
> committed isolation level using instantaneous share row locking chosen by
> the optimizer
> ...
> ij> select a from test.view0 where b=25;
> ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> ...
> Table Scan ResultSet for TABLE1 at read committed isolation level
> using share row locking chosen by the optimizer
> ...
> Table Scan ResultSet for TABLE2 at read committed isolation level
> using share row locking chosen by the optimizer
> ...
> ij> ;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira