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 "Satheesh Bandaram (JIRA)" <de...@db.apache.org> on 2005/12/15 07:17:46 UTC
[jira] Commented: (DERBY-649) Useful indexes not used in UNION ALL
[ 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
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.