db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mamta Satoor <msat...@gmail.com>
Subject DERBY-6301 - In list predicate not getting moved down to store for a subset of in list queries
Date Thu, 06 Feb 2014 06:51:33 GMT

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:
 stop position:
  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


View raw message