Gonna take a stab and answering this.
http://www.sqlite.org/opcode.html

The explain output for select count() from foo; uses the "Count" opcode. The 
description for that is
"Store the number of entries (an integer value) in the table or index opened by 
cursor P1 in register P2"
So that is indeed going to scan through the whole table, as the OpenRead was 
pointed to the table B-tree and not the index B-tree.

In the second case "select count() from foo where ref_count = 0" the OpenRead 
opens up the index (p4 isn't an integer) so that is indeed going through the 
index.

Remember also that you can get a more succinct explain by using "explain query 
plan".

Here's the output of me running this in a CLI I compiled with the pretty 
explain comments. Using .eqp full it outputs the "explain query plan" results, 
then the "explain" results, then the query results.

(Hmm, random note: It looks like ".eqp full" makes it disregard ".header on" 
when it gets down to outputting the results. Downgrading to only ".eqp on" 
respects the ".header on" though.)

SQLite version 3.15.1 2016-11-04 12:08:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> create table foo (id integer primary key, ref_count integer not null);

sqlite> create index foo_ref_count_idx on foo (ref_count) where ref_count = 0;

sqlite> .eqp full

sqlite> select count() from foo;
--EQP-- 0,0,0,SCAN TABLE foo
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     7     0                    00  Start at 7
1     OpenRead       1     2     0     1              00  root=2 iDb=0
2     Count          1     1     0                    00  r[1]=count()
3     Close          1     0     0                    00
4     Copy           1     2     0                    00  r[2]=r[1]
5     ResultRow      2     1     0                    00  output=r[2]
6     Halt           0     0     0                    00
7     Transaction    0     0     2     0              01  usesStmtJournal=0
8     Goto           0     1     0                    00
0

sqlite> select count() from foo where ref_count = 0;
--EQP-- 0,0,0,SEARCH TABLE foo USING COVERING INDEX foo_ref_count_idx 
(ref_count=?)
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     13    0                    00  Start at 13
1     Null           0     1     1                    00  r[1..1]=NULL
2     OpenRead       1     3     0     k(2,,)         02  root=3 iDb=0; 
foo_ref_count_idx
3     Integer        0     2     0                    00  r[2]=0
4     SeekGE         1     8     2     1              00  key=r[2]
5       IdxGT          1     8     2     1              00  key=r[2]
6       AggStep0       0     0     1     count(0)       00  accum=r[1] 
step(r[0])
7     Next           1     5     1                    00
8     Close          1     0     0                    00
9     AggFinal       1     0     0     count(0)       00  accum=r[1] N=0
10    Copy           1     3     0                    00  r[3]=r[1]
11    ResultRow      3     1     0                    00  output=r[3]
12    Halt           0     0     0                    00
13    Transaction    0     0     2     0              01  usesStmtJournal=0
14    Goto           0     1     0                    00
0

sqlite> select count() from foo where ref_count != 0;
--EQP-- 0,0,0,SCAN TABLE foo
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     13    0                    00  Start at 13
1     Null           0     1     1                    00  r[1..1]=NULL
2     OpenRead       0     2     0     2              00  root=2 iDb=0; foo
3     Rewind         0     8     0                    00
4       Column         0     1     2                    00  r[2]=foo.ref_count
5       Eq             3     7     2     (BINARY)       54  if r[2]==r[3] goto 7
6       AggStep0       0     0     1     count(0)       00  accum=r[1] 
step(r[0])
7     Next           0     4     0                    01
8     Close          0     0     0                    00
9     AggFinal       1     0     0     count(0)       00  accum=r[1] N=0
10    Copy           1     4     0                    00  r[4]=r[1]
11    ResultRow      4     1     0                    00  output=r[4]
12    Halt           0     0     0                    00
13    Transaction    0     0     2     0              01  usesStmtJournal=0
14    Integer        0     3     0                    00  r[3]=0
15    Goto           0     1     0                    00
0


-----Original Message-----
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Paul
Sent: Thursday, December 01, 2016 2:22 AM
To: General Discussion of SQLite Database
Subject: [sqlite] SQLite does not use opportunity to optimise COUNT() queries 
using partial indexes

I am not very familiar with the SQLite internals, but I believe that index 
structure is similar 
to that of a table, ie it's a B-TREE with a root containing a node count value. 
If so, then queries
like SELECT COUNT() FROM FOO WHERE <...>;  can be optimised the same way that
queries like SELECT COUNT() FROM FOO; given that condition is equivalent tho 
the 
condition of the partial index.

Example:

sqlite> CREATE TABLE foo(
   ...>   id INTEGER PRIMARY KEY,
   ...>   ref_count INTEGER NOT NULL
   ...> );
sqlite> 
sqlite> CREATE INDEX foo_ref_count_idx ON foo(ref_count) WHERE ref_count = 0;
sqlite> 
sqlite> EXPLAIN SELECT COUNT() FROM foo;
addr        opcode      p1          p2          p3          p4          p5      
    comment   
----------  ----------  ----------  ----------  ----------  ----------  
----------  ----------
0           Init        0           7           0                       00      
              
1           OpenRead    1           2           0           1           00      
              
2           Count       1           1           0                       00      
              
3           Close       1           0           0                       00      
              
4           Copy        1           2           0                       00      
              
5           ResultRow   2           1           0                       00      
              
6           Halt        0           0           0                       00      
              
7           Transactio  0           0           2           0           01      
              
8           TableLock   0           2           0           foo         00      
              
9           Goto        0           1           0                       00      
              
sqlite> 
sqlite> EXPLAIN SELECT COUNT() FROM foo WHERE ref_count = 0;
addr        opcode      p1          p2          p3          p4          p5      
    comment   
----------  ----------  ----------  ----------  ----------  ----------  
----------  ----------
0           Init        0           13          0                       00      
              
1           Null        0           1           1                       00      
              
2           OpenRead    1           3           0           k(2,nil,ni  00      
              
3           Integer     0           2           0                       00      
              
4           SeekGE      1           8           2           1           00      
              
5           IdxGT       1           8           2           1           00      
              
6           AggStep     0           0           1           count(0)    00      
              
7           Next        1           5           1                       00      
              
8           Close       1           0           0                       00      
              
9           AggFinal    1           0           0           count(0)    00      
              
10          Copy        1           3           0                       00      
              
11          ResultRow   3           1           0                       00      
              
12          Halt        0           0           0                       00      
              
13          Transactio  0           0           2           0           01      
              
14          TableLock   0           2           0           foo         00      
              
15          Goto        0           1           0                       00      
              
sqlite> 
sqlite> EXPLAIN SELECT COUNT() FROM foo WHERE ref_count != 0;
addr        opcode      p1          p2          p3          p4          p5      
    comment   
----------  ----------  ----------  ----------  ----------  ----------  
----------  ----------
0           Init        0           13          0                       00      
              
1           Null        0           1           1                       00      
              
2           OpenRead    0           2           0           2           00      
              
3           Rewind      0           8           0                       00      
              
4           Column      0           1           2                       00      
              
5           Eq          3           7           2           (BINARY)    54      
              
6           AggStep     0           0           1           count(0)    00      
              
7           Next        0           4           0                       01      
              
8           Close       0           0           0                       00      
              
9           AggFinal    1           0           0           count(0)    00      
              
10          Copy        1           4           0                       00      
              
11          ResultRow   4           1           0                       00      
              
12          Halt        0           0           0                       00      
              
13          Transactio  0           0           2           0           01      
              
14          TableLock   0           2           0           foo         00      
              
15          Integer     0           3           0                       00      
              
16          Goto        0           1           0                       00

As we can see 'SELECT COUNT() FROM foo;' query does not scan the table.

But 'SELECT COUNT() FROM foo WHERE ref_count = 0;'  query does and is no
different from 'SELECT COUNT() FROM foo WHERE ref_count != 0;'  query that
obviously cannot be optimised.
 
 
_______________________________________________
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

Reply via email to