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
