db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mike Matrigali (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, 23 Oct 2013 21:08:48 GMT

    [ https://issues.apache.org/jira/browse/DERBY-6301?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13803332#comment-13803332

Mike Matrigali commented on DERBY-6301:

would be interested in opinion on what would be best approach here, and maybe a scoping from
someone who understands the
compile layer.   As I commented it seems like we could just push the OR's using existing interface,
would just need to be careful
about blowing up the size of a compiled plan in the case of 1000's of terms to the IN-LIST.

The existing interface is not as efficient as it could be for the special case of OR's.  For
each term we would be specifying the same column, compare term, and value.  We could change
the internal pushing of terms to somehow special case IN-LIST, so that we could
optimize not needing the compare term or repeated column reference.  I am not sure how this
would affect the non-native push down
interface - i have not looked at it.

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

View raw message