Questions regarding the use of a "partial index" as a "covering index"

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Questions regarding the use of a "partial index" as a "covering index"

Martin Thierer
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         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     10    0                    00  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     1                    00  r[1]=tab.x4
4       NotNull        1     8     0                    00  if r[1]!=NULL goto 8
5       Column         1     2     2                    00  r[2]=tab.x1
6       Column         1     0     3                    00  r[3]=tab.x2
7       ResultRow      2     2     0                    00  output=r[2..3]
8     Next           1     3     0                    01
9     Halt           0     0     0                    00
10    Transaction    0     0     2     0              01  usesStmtJournal=0
11    Goto           0     1     0                    00

*** 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         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     14    0                    00  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     13    1     0              00
4       Column         1     1     1                    00  r[1]=
5       Column         1     2     2                    00  r[2]=
6       NotFound       0     12    1     2              00  key=r[1..2]
7       Column         0     3     3                    00  r[3]=tab.x4
8       NotNull        3     12    0                    00  if
r[3]!=NULL goto 12
9       Column         1     1     4                    00  r[4]=tab.x1
10      Column         1     0     5                    00  r[5]=tab.x2
11      ResultRow      4     2     0                    00  output=r[4..5]
12    Next           1     4     0                    01
13    Halt           0     0     0                    00
14    Transaction    0     0     47    0              01  usesStmtJournal=0
15    Goto           0     1     0                    00

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