Re: Optimizer limitation with partial indexes

Does moving the expr3 work?

SELECT * FROM Table WHERE ((expr1 > val1 AND AND expr3) OR (expr2 > val2
AND expr3))

Wout.


On Wed, Feb 12, 2020 at 12:09 AM Jens Alfke <[email protected]> wrote:

> I'm running into a problem with partial indexes; apparently the query
> optimizer isn't smart enough.
>
> I currently have indexes of the form
>         CREATE INDEX Index1 ON Table (expr1)
>         CREATE INDEX Index2 ON Table (expr2)
> where expr1 and expr2 are expressions involving table columns.
>
> The problematic queries are of the form
>         SELECT * FROM Table WHERE (expr1 > val1 OR expr2 > val2) AND expr3
> Such a query correctly uses the above indexes — the EXPLAIN command shows
> it's using a multi-index OR combining two 'search table using index' loops.
>
> If, however, I try to make the indexes smaller by changing them to
>         CREATE INDEX Index1 ON Table (expr1) WHERE expr3
>         CREATE INDEX Index2 ON Table (expr2) WHERE expr3
> the query stops using the indexes effectively. It's reduced to doing 'scan
> table using index', i.e. O(n).
>
> It looks like what happens is that the optimizer doesn't associate the
> "AND expr3" clause with the "expr1" and "expr2" comparisons. In other
> words, it doesn't realize that (A OR B) AND C is equivalent to (A AND C) OR
> (B AND C).
>
> If this were a hand-written SELECT statement it would be easy to work
> around this, but it's not. It's the output of a query translator that
> generates SQL, and it can generate arbitrary queries with arbitrary
> combinations of operators.
>
> I know the SQLite optimizer isn't a Mathematica-grade symbolic logic
> analyzer! But I'm wondering if in this case there's a way around this
> limitation?
>
> —Jens
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users