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