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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
