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
