I get confusing (to me) results when toying with a query that I think
should use a partial index as a "covering index". This is using sqlite
3.20.1 on Arch Linux x86_64.
The examples listed below start from a new, empty database.
*** Given a table and an index like this
CREATE TABLE tab (x1 TEXT, x2 TEXT, x3 TEXT, x4 TEXT,
PRIMARY KEY (x1, x3)
) WITHOUT ROWID;
CREATE INDEX ind ON tab (x2, x4) WHERE x4 IS NULL;
i get the result I would expect: The index is used as a "covering
index", even though if I read the bytecode correctly there's a != NULL
comparison that's not strictly necessary, but that would be just room
for further optimization:
sqlite> EXPLAIN QUERY PLAN SELECT x1, x2 FROM tab WHERE x4 IS NULL;
selectid|order|from|detail
0|0|0|SCAN TABLE tab USING COVERING INDEX ind
sqlite> EXPLAIN SELECT x1, x2 FROM tab WHERE x4 IS NULL;
addr opcode p1p2p3p4 p5 comment
- - -- -
0 Init 0 10000 Start at 10
1 OpenRead 1 3 0 k(4) 00 root=3 iDb=0; ind
2 Rewind 1 9 1 0 00
3 Column 1 1 100 r[1]=tab.x4
4 NotNull1 8 000 if r[1]!=NULL goto 8
5 Column 1 2 200 r[2]=tab.x1
6 Column 1 0 300 r[3]=tab.x2
7 ResultRow 2 2 000 output=r[2..3]
8 Next 1 3 001
9 Halt 0 0 000
10Transaction0 0 2 0 01 usesStmtJournal=0
11Goto 0 1 000
*** But if I remove the (redundant) column x4 from the index, it seems
to me like it's no longer used as a "covering index" for the same
query, even though all the output columns are still there and the
index *is* actually used:
DROP TABLE tab;
CREATE TABLE tab (x1 TEXT, x2 TEXT, x3 TEXT, x4 TEXT,
PRIMARY KEY (x1, x3)
) WITHOUT ROWID;
CREATE INDEX ind ON tab (x2) WHERE x4 IS NULL;
sqlite> EXPLAIN QUERY PLAN SELECT x1, x2 FROM tab WHERE x4 IS NULL;
selectid|order|from|detail
0|0|0|SCAN TABLE tab USING INDEX ind
sqlite> EXPLAIN SELECT x1, x2 FROM tab WHERE x4 IS NULL;
addr opcode p1p2p3p4 p5 comment
- - -- -
0 Init 0 14000 Start at 14
1 OpenRead 0 2 0 k(2,,) 00 root=2 iDb=0; tab
2 OpenRead 1 3 0 k(3,,,)00 root=3 iDb=0; ind
3 Rewind 1 131 0 00
4 Column 1 1 100 r[1]=
5 Column 1 2 200 r[2]=
6 NotFound 0 121 2 00 key=r[1..2]
7 Column 0 3 300 r[3]=tab.x4
8 NotNull3 12000 if
r[3]!=NULL goto 12
9 Column 1 1 400 r[4]=tab.x1
10 Column 1 0 500 r[5]=tab.x2
11 ResultRow 4 2 000 output=r[4..5]
12Next 1 4 001
13Halt 0 0 000
14Transaction0 0 470 01 usesStmtJournal=0
15Goto 0 1 000
*** It gets even more confusing: If I remove the "TEXT" type from
columns x3 and x4, the index isn't used at all:
DROP TABLE tab;
CREATE TABLE tab (x1 TEXT, x2 TEXT, x3, x4,
PRIMARY KEY (x1, x3)
) WITHOUT ROWID;
CREATE INDEX ind ON tab (x2) WHERE x4 IS NULL;
sqlite> EXPLAIN QUERY PLAN SELECT x1, x2 FROM tab WHERE x4 IS NULL;
selectid|order|from|detail
0|0|0|SCAN TABLE tab
*** Except if I also remove the "TEXT" type from column x2, the index
is used again:
DROP TABLE tab;
CREATE TABLE tab (x1 TEXT, x2, x3, x4,
PRIMARY KEY (x1, x3)
) WITHOUT ROWID;
CREATE INDEX ind ON tab (x2) WHERE x4 IS NULL;
sqlite> EXPLAIN QUERY PLAN SELECT x1, x2 FROM tab WHERE x4 IS NULL;
selectid|order|from|detail
0|0|0|SCAN TABLE tab USING INDEX ind
*** But only until I also remove the "TEXT" type from column x1, then
it's not used:
DROP TABLE tab;
CREATE TABLE tab (x1, x2, x3, x4,
PRIMARY KEY (x1, x3)
) WITHOUT ROWID;
CREATE INDEX ind ON tab (x2) WHERE x4 IS NULL;
sqlite> EXPLAIN QUERY PLAN SELECT x1, x2 FROM tab WHERE x4 IS NULL;
selectid|order|from|detail
0|0|0|SCAN TABLE tab
Is this the expected behaviour?
Thanks!
Martin
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
