db-derby-dev mailing list archives

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