db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6301) SQL layer should push down IN list predicates to store when doing a scan
Date Wed, 29 Jan 2014 22:10:10 GMT

    [ 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)

Mime
View raw message