[
https://issues.apache.org/jira/browse/DERBY-6301?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13885886#comment-13885886
]
Mamta A. Satoor commented on DERBY-6301:
----------------------------------------
I compared the code paths in language code for the query using
multi-probe(select c11 from t1 where c11 in (1,2)) vs query using table
scan(select c11 from t1 where c11 in
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30))
and came across following piece of code in language layer where we decide
which predicates should be pushed down for a given query. The code is in
PredicateList.orderUsefulPredicates.
For "select c11 from t1 where c11 in (1,2)" query, we choose the query plan
with multi-probing and the predicate gets pushed down at
PredicateList.orderUsefulPredicates(Optimizable, ConglomerateDescriptor,
boolean, boolean, boolean) line: 947
For "select c11 from t1 where c11 in
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30)"
query, we choose the query plan with table scan and following if at
PredicateList.orderUsefulPredicates(Optimizable, ConglomerateDescriptor,
boolean, boolean, boolean) line: 500 returns true and we do not push down any
predicates.
/* Is a heap scan or a non-matching index scan on a covering
index? */
if ((cd == null) || (! cd.isIndex()) ||
(nonMatchingIndexScan && coveringIndexScan))
Since the in list predicate is not a qualifier predicate, it looks like we
choose not to push the predicate to store. There is even following comment in
the code
/*
** RESOLVE: For now, not pushing any predicates for heaps.
When this
** changes, we also need to make the scan in
** TableScanResultSet.getCurrentRow() check the qualifiers to
see
** if the row still qualifies (there is a new method in
ScanController
** for this.
*/
> SQL layer should push down IN list predicates to store when doing a scan
> ------------------------------------------------------------------------
>
> Key: DERBY-6301
> URL: https://issues.apache.org/jira/browse/DERBY-6301
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.10.1.1
> Reporter: Mike Matrigali
>
> The store interface allows for OR and AND qualifiers to be passed down to
> store as part of either
> a heap or btree scan. It is more efficient to qualify the rows at the lowest
> levels. The SQL level
> does not seem to push any qualifier in the case of IN lists.
> This does not matter if the optimizer choses the multi-probe execution
> strategy for the IN list as that also
> qualifies the row at the lowest level.
> The problem arises when the optimizer chooses not to do multi-probe, for
> instance if it determines there
> are too many terms in the in-list relative to the size of the table and the
> cardinality of the terms. In this
> case it chooses a scan with no qualifiers which results in all rows being
> returned to the sql layer and qualified there.
> 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.
--
This message was sent by Atlassian JIRA
(v6.1.5#6160)
