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
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 01 usesStmtJournal=0 14Integer0 3 000 r[3]=0 15Goto 0 1 000 0 -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Paul Sent: Thursday, December 01,
