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 Thu, 24 Oct 2013 20:22:01 GMT

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

Mike Matrigali commented on DERBY-6301:

I believe multi-probe is only picked when internally we have an in-list.  We will get internal
in-list either in the case of a user specified
IN-list or the code may (maybe always - i am not sure) convert a set of equality OR's to an
internal IN-list.

I did not mean to say there is a current bound.  I just remember that a long time ago there
were derby compiler problems with very long
IN-lists, and Dan did work in this area to avoid them.  So whatever we do in this area we
should think about how it might affect the size of
a compiled query plan with 1000's of terms in an IN-list.  

There is a point when the optimizer will not pick multi-probe on an IN-list even if a good
index exists, but it is more of a costing issue.  The cost to
 probe each term is
more than a normal scan so as the number of terms gets close to or exceeds the number of rows
in the table then multi-probe will not be

In general I think the more we can get the sql layer to push predicates into store the better.
  Read committed locking worst best when this
is the case, and I assume it also fixes issues with virtual tables and predicates.

> 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