This is documented here https://sqlite.org/partialindex.html and here https://sqlite.org/queryplanner.html
Specifically, SQLIte does not prove theorems in first-order logic. To have a chance of using the partial indices, you would need to have your query translator formulate (expr1>val1 AND expr 3) OR (expr2>val2 AND expr3) -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Jens Alfke Gesendet: Mittwoch, 12. Februar 2020 00:09 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: [EXTERNAL] [sqlite] Optimizer limitation with partial indexes 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 ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
