db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Satheesh Bandaram <sathe...@Sourcery.Org>
Subject Re: [jira] Updated: (DERBY-649) Useful indexes not used in UNION ALL
Date Thu, 15 Dec 2005 22:06:14 GMT
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> ;
>>>>>  
>>>>>       
>>>>
>>>>
>>>>
>>>>     
>>>
>>>
>>>   
>>
>>
>>  
>>
>
>
>


Mime
View raw message