Re: [EXTERNAL] Optimizer limitation with partial indexes
Hick Gunter <hick-p6o8VmWS/[email protected]>
| Newsgroups | gmane.comp.db.sqlite.general |
|---|---|
| Message-ID | <0a7493a0f18ab79ddc2f0753143dcca7bb90a356@localhost> |
>Von: sqlite-users [mailto:[email protected]] Im Auftrag von Jens Alfke >> On Feb 12, 2020, at 5:30 AM, Hick Gunter <[email protected]> wrote: >> >> This is documented here https://sqlite.org/partialindex.html >> <https://sqlite.org/partialindex.html> and here >> https://sqlite.org/queryplanner.html >> <https://sqlite.org/queryplanner.html> >> >> Specifically, SQLIte does not prove theorems in first-order logic. > >Thanks — I hadn't seen the section "Queries Using Partial Indexes" before, and it gives more detail about how the matching is done. >However, it seems that my query does match one of the rules: > > "If W [the query's WHERE clause] is AND-connected terms > and X [the index's WHERE clause] is OR-connected terms > and if any term of W appears as a term of X, > then the partial index is usable." > >Here W = (expr1 > val1 OR expr2 > val2) AND expr3 and X = expr3, which is a degenerate case of one OR-connected term. > >So I'm not sure why the indexes aren't useable, unless there are limitations of the actual rule that aren't described in that English text. My guess ist hat SQLite is looking at the "expr1>val1" and "expr2>val2" terms respectively, which don't have a reference to expr3, and thus concludes that the indices are not usable. However, "expr1>val1 AND expr3" clearly matches the rule and thus should use the index. ___________________________________________ 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 [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users