Re: [sqlite] SQLite does not use opportunity to optimise COUNT() queries using partial indexes
Thanks! That explains a lot. For some reason I thought that 'SELECT COUNT() FROM ' is optimised. > 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 p1p2p3p4 p5 comment > - - -- - > 0 Init 0 7 000 Start at 7 > 1 OpenRead 1 2 0 1 00 root=2 iDb=0 > 2 Count 1 1 000 r[1]=count() > 3 Close 1 0 000 > 4 Copy 1 2 000 r[2]=r[1] > 5 ResultRow 2 1 000 output=r[2] > 6 Halt 0 0 000 > 7 Transaction0 0 2 0 01 usesStmtJournal=0 > 8 Goto 0 1 000 > 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 p1p2p3p4 p5 comment > - - -- - > 0 Init 0 13000 Start at 13 > 1 Null 0 1 100 r[1..1]=NULL > 2 OpenRead 1 3 0 k(2,,) 02 root=3 iDb=0; > foo_ref_count_idx > 3 Integer0 2 000 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 100 > 8 Close 1 0 000 > 9 AggFinal 1 0 0 count(0) 00 accum=r[1] N=0 > 10Copy 1 3 000 r[3]=r[1] > 11ResultRow 3 1 000 output=r[3] > 12Halt 0 0 000 > 13Transaction0 0 2 0 01 usesStmtJournal=0 > 14Goto 0 1 000 > 0 > > sqlite> select count() from foo where ref_count != 0; > --EQP-- 0,0,0,SCAN TABLE foo > addr opcode p1p2p3p4 p5 comment > - - -- - > 0 Init 0 13000 Start at 13 > 1 Null 0 1 100 r[1..1]=NULL > 2 OpenRead 0 2 0 2 00 root=2 iDb=0; foo > 3 Rewind 0 8 000 > 4 Column 0 1 200 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 001 > 8 Close 0 0 000 > 9 AggFinal 1 0 0 count(0) 00 accum=r[1] N=0 > 10Copy 1 4 000 r[4]=r[1] > 11ResultRow 4 1 000 output=r[4] > 12Halt 0 0 000 > 13Transaction0 0 2 0
Re: [sqlite] SQLite does not use opportunity to optimise COUNT() queries using partial indexes
ginal 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; addropcode p1 p2 p3 p4 p5 comment -- -- -- -- -- -- -- -- 0 Init0 7 0 00 1 OpenRead1 2 0 1 00 2 Count 1 1 0 00 3 Close 1 0 0 00 4 Copy1 2 0 00 5 ResultRow 2 1 0 00 6 Halt0 0 0 00 7 Transactio 0 0 2 0 01 8 TableLock 0 2 0 foo 00 9 Goto0 1 0 00 sqlite> sqlite> EXPLAIN SELECT COUNT() FROM foo WHERE ref_count = 0; addropcode p1 p2 p3 p4 p5 comment -- -- -- -- -- -- -- -- 0 Init0 13 0 00 1 Null0 1 1 00 2 OpenRead1 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 Next1 5 1 00 8 Close 1 0 0 00 9 AggFinal1 0 0 count(0)00 10 Copy1 3 0 00 11 ResultRow 3 1 0 00 12 Halt0 0 0 00 13 Transactio 0 0 2 0 01 14 TableLock 0 2 0 foo 00 15 Goto0 1 0 00 sqlite> sqlite> EXPLAIN SELECT COUNT() FROM foo WHERE ref_count != 0; addropcode p1 p2 p3 p4 p5 comment -- -- -- -- -- -- -- -- 0 Init0 13 0 00 1 Null0 1 1 00 2 OpenRead0 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 Next0 4 0 01 8 Close 0
[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; addropcode p1 p2 p3 p4 p5 comment -- -- -- -- -- -- -- -- 0 Init0 7 0 00 1 OpenRead1 2 0 1 00 2 Count 1 1 0 00 3 Close 1 0 0 00 4 Copy1 2 0 00 5 ResultRow 2 1 0 00 6 Halt0 0 0 00 7 Transactio 0 0 2 0 01 8 TableLock 0 2 0 foo 00 9 Goto0 1 0 00 sqlite> sqlite> EXPLAIN SELECT COUNT() FROM foo WHERE ref_count = 0; addropcode p1 p2 p3 p4 p5 comment -- -- -- -- -- -- -- -- 0 Init0 13 0 00 1 Null0 1 1 00 2 OpenRead1 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 Next1 5 1 00 8 Close 1 0 0 00 9 AggFinal1 0 0 count(0)00 10 Copy1 3 0 00 11 ResultRow 3 1 0 00 12 Halt0 0 0 00 13 Transactio 0 0 2 0 01 14 TableLock 0 2 0 foo 00 15 Goto0 1 0 00 sqlite> sqlite> EXPLAIN SELECT COUNT() FROM foo WHERE ref_count != 0; addropcode p1 p2 p3 p4 p5 comment -- -- -- -- -- -- -- -- 0 Init0 13 0 00 1 Null0 1 1 00 2 OpenRead0 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 Next0 4 0 01 8 Close 0 0 0 00 9 AggFinal1 0 0 count(0)00 10 Copy1 4 0 00 11 ResultRow 4 1 0 00
