I've got a question or two on the query planner's use of partial indexes. It
seems that the query planner isn't using a partial index unless the fields in
the WHERE clause of the query are present as fields in the index, even if the
WHERE of the query exactly matches the WHERE of the index.
Example simplified schema:
create table problemTable
(
pk text primary key,
A text not null,
n int not null
--other fields here
);
create index typicalFullIndex on problemTable (A);
create index unusedPartial on problemTable (A) where n > 1;
In this case the WHERE clause on the index isn't on the field being stored in
the index. But according to the partial indexes page:
"The columns referenced in the WHERE clause of a partial index can be any of
the columns in the table, not just columns that happen to be indexed. However,
it is very common for the WHERE clause expression of a partial index to be a
simple expression on the column being indexed."
When I run queries which I think should use this index, it won't use it.
sqlite> explain query plan select count(*) from problemTable where n > 1;
0|0|0|SCAN TABLE problemTable
Even trying to force it with an INDEXED BY statement doesn't work.
sqlite> explain query plan select count(*) from problemTable indexed by
unusedPartial where n > 1;
Error: no query solution
Analyzing the database doesn't help either, resulting in the same thing,
although the sqlite_stat numbers clearly show the index is there, and only
includes the correct number of records.
If I make another partial index, this time including the field from the where
clause in the index then everything starts working.
sqlite> create index usedPartial on problemTable (n, A) where n > 1;
sqlite> explain query plan select count(*) from problemTable where n > 1;
0|0|0|SEARCH TABLE problemTable USING COVERING INDEX usedPartial (n>?)
sqlite> explain query plan select A from problemTable where n > 1;
0|0|0|SEARCH TABLE problemTable USING COVERING INDEX usedPartial (n>?)
sqlite> explain query plan select * from problemTable where n > 1;
0|0|0|SEARCH TABLE problemTable USING INDEX usedPartial (n>?)
If I create that with n and A reversed then there's a slight difference when it
comes to querying for all fields...
sqlite> drop index usedPartial;
sqlite> create index usedPartial2 on problemTable (A, n) where n > 1;
sqlite> explain query plan select count(*) from problemTable where n > 1;
0|0|0|SCAN TABLE problemTable USING COVERING INDEX usedPartial2
sqlite> explain query plan select A from problemTable where n > 1;
0|0|0|SCAN TABLE problemTable USING COVERING INDEX usedPartial2
sqlite> explain query plan select * from problemTable where n > 1;
0|0|0|SCAN TABLE problemTable
sqlite> explain query plan select * from problemTable indexed by usedPartial2
where n > 1;
Error: no query solution
In my specific case I don't really care what fields are in the index as I'm
gonna need all of them, I just care that a record has an entry in the index at
all. (Order of magnitude I'm looking at 70,000,000 records with 1,000 that
fulfill the WHERE clause) "I would have thought" (famous last words) that the
WHERE clauses exactly matching between index and query would be enough there.
After all, creating the index doesn't seem to care that the WHERE fields aren't
in the index, and it makes the index just fine. So the query shouldn't need the
value of n to know whether or not the WHERE clause evaluates to True, a record
simply being in the index at all is exactly that case.
So, after all that:
Is this just bad reading comprehension and bad expectations on my part?
Although it will create and analyze unusedIndex just fine, will the query
planner even be able to make use of it?
Is it a full on requirement that fields in the WHERE clause be included as
fields in the index, and not just something that's "very common"?
<Insert other questions I've forgotten to ask here>
I'm relatively new at this, so my apologies if this is a ton of text for what
turns out to be a simple answer, or if it's been covered in detail before.
Much obliged,
Dave R
(Tested with precompiled command line interface for Windows, version 3.11.0)
sqlite> select sqlite_version();
3.11.0
sqlite> select sqlite_source_id();
2016-02-15 17:29:24 3d862f207e3adc00f78066799ac5a8c282430a5f
And as I was about to hit send I noticed 3.11.1 is out, and a quick test looks
like it does the same things.
P.S.
One other weird thing I noticed while testing:
INDEXED BY doesn't seem to prevent queries from using other indexes in some
cases.
sqlite> explain query plan select count(*) from problemTable indexed by
unusedPartial;
selectid|order|from|detail
0|0|0|SCAN TABLE problemTable USING COVERING INDEX
sqlite_autoindex_problemTable_1
Shouldn't the INDEXED BY phrase there cause that to fail as it can't use the
specified (partial) index?
