Hi,
After I updated sqlite with 3.11 to 3.12, I noticed that several
requests to the virtual tables have become much slower.
I was able to replicate the problem on conventional tables.
--Make tables
DROP TABLE IF EXISTS test;
CREATE TABLE test(id,val);
INSERT INTO test
WITH RECURSIVE
cte(id,val) AS (
VALUES(1 , random()%1000) UNION ALL
SELECT id+1,random()%1000 FROM cte WHERE ID<10000000)
select * from cte;
CREATE INDEX idx_id ON test(id);
CREATE INDEX idx_val ON test(val);
DROP TABLE IF EXISTS sel;
CREATE TABLE sel(val);
INSERT INTO sel
WITH RECURSIVE
cte(id) AS (
VALUES(1) UNION ALL
SELECT id+1 FROM cte WHERE ID<1000)
select * from cte;
ANALYZE;
-- query with index idx_val
explain SELECT count(*) FROM test WHERE id BETWEEN 10000 AND 2000000 AND
val IN (SELECT val from sel);
SELECT count(*) FROM test WHERE id BETWEEN 10000 AND 2000000 AND val IN
(SELECT val from sel);
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 31 0 00 Start at 31
1 Null 0 1 1 00 r[1..1]=NULL
2 OpenRead 0 8 0 2 00 root=8 iDb=0;
test
3 OpenRead 2 67443 0 k(2,,) 02 root=67443
iDb=0; idx_val
4 Once 0 13 0 00
5 OpenEphemeral 4 1 0 k(1,B) 00 nColumn=1
6 OpenRead 1 2 0 1 00 root=2 iDb=0; sel
7 Rewind 1 12 0 00
8 Column 1 0 3 00 r[3]=sel.val
9 MakeRecord 3 1 4 A 00
r[4]=mkrec(r[3])
10 IdxInsert 4 4 0 00 key=r[4]
11 Next 1 8 0 01
12 Close 1 0 0 00
13 Rewind 4 25 0 00
14 Column 4 0 2 00 r[2]=
15 IsNull 2 24 0 00 if
r[2]==NULL goto 24
16 SeekGE 2 24 2 1 00 key=r[2]
17 IdxGT 2 24 2 1 00 key=r[2]
18 Seek 2 0 0 00 Move 0 to
2.rowid
19 Column 0 0 4 00 r[4]=test.id
20 Lt 6 23 4 (BINARY) 51 if
r[6]<r[4] goto 23
21 Gt 7 23 4 (BINARY) 51 if
r[7]>r[4] goto 23
22 AggStep0 0 0 1 count(0) 00 accum=r[1]
step(r[0])
23 Next 2 17 0 00
24 NextIfOpen 4 14 0 00
25 Close 0 0 0 00
26 Close 2 0 0 00
27 AggFinal 1 0 0 count(0) 00 accum=r[1] N=0
28 Copy 1 8 0 00 r[8]=r[1]
29 ResultRow 8 1 0 00 output=r[8]
30 Halt 0 0 0 00
31 Transaction 0 0 393 0 01 usesStmtJournal=0
32 TableLock 0 8 0 test 00 iDb=0 root=8
write=0
33 TableLock 0 2 0 sel 00 iDb=0 root=2
write=0
34 Integer 10000 6 0 00 r[6]=10000
35 Integer 2000000 7 0 00 r[7]=2000000
36 Goto 0 1 0 00
Run Time: real 0.147 user 0.000000 sys 0.000000
994089
Run Time: real 29.520 user 5.781250 sys 22.703125
-- query without index idx_val
explain SELECT count(*) FROM test WHERE id BETWEEN 10000 AND 2000000 AND
+val IN (SELECT val from sel);
SELECT count(*) FROM test WHERE id BETWEEN 10000 AND 2000000 AND +val IN
(SELECT val from sel);
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 31 0 00 Start at 31
1 Null 0 1 1 00 r[1..1]=NULL
2 OpenRead 0 8 0 2 00 root=8 iDb=0;
test
3 OpenRead 2 9 0 k(2,,) 00 root=9 iDb=0;
idx_id
4 Integer 10000 2 0 00 r[2]=10000
5 SeekGE 2 25 2 1 00 key=r[2]
6 Integer 2000000 2 0 00 r[2]=2000000
7 IdxGT 2 25 2 1 00 key=r[2]
8 Seek 2 0 0 00 Move 0 to
2.rowid
9 Noop 0 0 0 00 begin IN expr
10 Once 0 19 0 00
11 OpenEphemeral 4 1 0 k(1,B) 00 nColumn=1
12 OpenRead 1 2 0 1 00 root=2
iDb=0; sel
13 Rewind 1 18 0 00
14 Column 1 0 3 00 r[3]=sel.val
15 MakeRecord 3 1 4 A 00
r[4]=mkrec(r[3])
16 IdxInsert 4 4 0 00 key=r[4]
17 Next 1 14 0 01
18 Close 1 0 0 00
19 Column 0 1 4 00 r[4]=test.val
20 IsNull 4 24 0 00 if
r[4]==NULL goto 24
21 Affinity 4 1 0 A 00 affinity(r[4])
22 NotFound 4 24 4 1 00 key=r[4];
end IN expr
23 AggStep0 0 0 1 count(0) 00 accum=r[1]
step(r[0])
24 Next 2 7 0 00
25 Close 0 0 0 00
26 Close 2 0 0 00
27 AggFinal 1 0 0 count(0) 00 accum=r[1] N=0
28 Copy 1 5 0 00 r[5]=r[1]
29 ResultRow 5 1 0 00 output=r[5]
30 Halt 0 0 0 00
31 Transaction 0 0 393 0 01 usesStmtJournal=0
32 TableLock 0 8 0 test 00 iDb=0 root=8
write=0
33 TableLock 0 2 0 sel 00 iDb=0 root=2
write=0
34 Goto 0 1 0 00
Run Time: real 0.138 user 0.015625 sys 0.000000
994089
Run Time: real 1.868 user 1.656250 sys 0.140625
I guess that aggressiveness in optimization is too high and its priority
should be somehow reduced.
---
??? ????????? ????????? ?? ?????? ??????????? Avast.
https://www.avast.com/antivirus
