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 <j...@mooseyard.com> 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 > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
