I've seen similar. I think the issue is that since a isn't an indexed column then it's not technically covering, despite being given a in the where clause.
sqlite> create index Foo_partial_with_a on Foo (a, b, c) where a = 1; sqlite> analyze; sqlite> explain query plan select b from Foo where a = 1 and b = 2 order by c; selectid|order|from|detail 0|0|0|SEARCH TABLE Foo USING COVERING INDEX Foo_partial_with_a (a=? AND b=?) -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Deon Brewis Sent: Friday, June 15, 2018 1:09 PM To: SQLite mailing list Subject: [sqlite] SQLite query planner doesn't seem to know when a partial index is COVERING Looks like a missed optimization opportunity here. Well, more than that - there doesn't appear to be a way to get SQLITE to automatically use a partial index if a similar non-partial index exists. E.g. create table Foo(a,b,c,d,e); create index Foo_inx on Foo(a,b,c); create index Foo_partial_inx on Foo(b,c) where a = 1; insert into Foo(a,b,c,d,e) values(1,2,3,4,5); insert into Foo(a,b,c,d,e) values(1,2,2,4,6); insert into Foo(a,b,c,d,e) values(1,2,5,1,1); insert into Foo(a,b,c,d,e) values(2,1,6,1,1); insert into Foo(a,b,c,d,e) values(6,4,6,1,1); analyze Foo; explain query plan select b from Foo where a=1 and b=2 order by c; > SEARCH TABLE Foo USING COVERING INDEX Foo_inx (a=? AND b=?) In this case SQLite picks Foo_inx over Foo_partial_inx. I suspect it's because it doesn't know Foo_partial_inx is effectively a covering index because if I force the index by hand, it doesn't list it as a COVERING index: explain query plan select b from Foo indexed by Foo_partial_inx where a=1 and b=2 order by c; > SEARCH TABLE Foo USING INDEX Foo_partial_inx (b=?) And I suspect that's why it picks Foo_inx over Foo_partial_inx. But otherwise this behavior seems to be exactly what I want though (will need to step through an 'explain' to make sure it doesn't do main table lookups), but it requires an INDEXED BY to get there. As a workaround, if I repeat the WHERE clause field ('a') in the partial index field list, THEN it starts using the partial index automatically: create index Foo_partial_inx2 on Foo(a,b,c) where a = 1; analyze Foo; > SEARCH TABLE Foo USING COVERING INDEX Foo_partial_inx2 (a=? AND b=?) But that has 2 problems: a) this makes the index bigger due to the extra (very unnecessary) column b) the executer doesn't seem to take into the account that this is a partial index so it searches for 'a' (minor issue) Of course the partial index still has less rows than without partial so it's still a win, but still - it shouldn't need 'a' to be repeated on every row. Either way, though there are issues with the workaround, the bigger issue is that it doesn't automatically pick the original Foo_partial_inx in the first place. - Deon _______________________________________________ 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
