db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik" <dag.wan...@oracle.com>
Subject Re: DERBY-6301 - In list predicate not getting moved down to store for a subset of in list queries
Date Fri, 07 Feb 2014 11:04:03 GMT
For what it's worth, I just had the opposite problem: I want the 
ProjectRestrictNode to *not* push qualifiers down to store, and I found 
I could avoid that by making sure this test in PRN was extended with my 
case, ca line 707:

         if ((restrictionList != null) && !alreadyPushed && 
!hashJoinWithThisPRN && !validatingDeferredCheckConstraints)
         {
             restrictionList.pushUsefulPredicates((Optimizable) 
childResult);
         }

so somehow you'd need to get the pushDown to happen in your case.

Dag


On 06. feb. 2014 07:51, Mamta Satoor wrote:
> Hi,
> I have spent sometime looking through the code for DERBY-6301(SQL 
> layer should push down IN list predicates to store when doing a scan). 
> I have found that if the number of elements in the in list are fairly 
> large, optimizer will find that table scan is better than using an 
> available index. This is a good optimization since it is indeed better 
> to use table scan for such an in list query. But the problem is when 
> we talk to the store about doing table scan, we do not pass any 
> qualifiers to the store based on the in list. This causes store to 
> lock more rows than really necessary. As Mike noted down in DERBY-6301 
> "In addition to performance considerations this presents a locking 
> problem with respect to the repeatable read isolation level. It is 
> optimal in repeatable read to not maintain locks on those
> rows that do not qualify. Currently this locking optimization only 
> takes place for those rows that are qualified in the store vs. those 
> qualified in the upper SQL layer. So in the case of a non-multi-probe 
> IN-LIST plan all non-qualified rows looked at as part of the execution 
> will remain locked in repeatable read. "
> During my debugging, following is where I see that there are no 
> qualifiers sent to BulkTableScanResultSet.
> BulkTableScanResultSet(TableScanResultSet).openCore() line: 246
>  BulkTableScanResultSet.openCore() line: 286
>  ProjectRestrictResultSet.openCore() line: 174
>  ProjectRestrictResultSet(BasicNoPutResultSetImpl).open() line: 266
>  GenericPreparedStatement.executeStmt(Activation, boolean, long) line: 
> 439
>  GenericPreparedStatement.execute(Activation, long) line: 320
>  EmbedStatement.executeStatement(Activation, boolean, boolean) line: 1337
>  EmbedStatement.execute(String, boolean, boolean, int, int[], 
> String[]) line: 704
>  EmbedStatement.execute(String) line: 631
>  ij.executeImmediate(String) line: 367
>  utilMain.doCatch(String) line: 527
>  utilMain.runScriptGuts() line: 372
>  utilMain.go(LocalizedInput[], LocalizedOutput) line: 245
>  Main.go(LocalizedInput, LocalizedOutput) line: 229
>  Main.mainCore(String[], Main) line: 184
>  Main.main(String[]) line: 75
>  ij.main(String[]) line: 59
> I tried a simple query as shown below to verify that there indeed are 
> other places in the code where we pass qualifiers when doing table 
> scan and we need to mimic something similar for in list when table 
> scan seems a better option for in list. An example query which does 
> table scan and passes qualifier is as below
> select * from t2 where c21>=1 and c21<=30;
> Table t2 above has 4 columns with no index on any column. It has 200 
> rows with values ranging from 1 to 200 in column c21. When i run this 
> query in ij with log query plan, I see following query plan for it.
> Wed Feb 05 14:50:19 PST 2014 Thread[main,5,main] (XID = 1082), 
> (SESSIONID = 1), select * from t2 where c21>=1 and c21<=30 ******* 
> Table Scan ResultSet for T2 at read committed isolation level using 
> instantaneous share row locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 30
> Rows filtered = 0
> Fetch Size = 16
>  constructor time (milliseconds) = 0
>  open time (milliseconds) = 0
>  next time (milliseconds) = 0
>  close time (milliseconds) = 0
>  next time in milliseconds/row = 0
> scan information:
>  Bit set of columns fetched=All
>  Number of columns fetched=4
>  Number of pages visited=3
>  Number of rows qualified=30
>  Number of rows visited=200
>  Scan type=heap
>  start position:
>   null
>  stop position:
>   null
>  qualifiers:
>   Column[0][0] Id: 0
>   Operator: <
>   Ordered nulls: false
>   Unknown return value: true
>   Negate comparison result: true
>   Column[0][1] Id: 0
>   Operator: <=
>   Ordered nulls: false
>   Unknown return value: false
>   Negate comparison result: false
>  optimizer estimated row count: 25.16
>  optimizer estimated cost: 81.76
> I will start investigating how are the qualifiers sent for the above 
> query using AND and see if I get the language layer to do the same for 
> in list cases with table scan and an index on the column on which in 
> list is being used. If anyone has any ideas on how to do this, I will 
> highly appreciate that
> thanks,
> Mamta


Mime
View raw message