Check if fts has huge matching and another index has a tiny selection.

results.txt

---------------------------------------------------------------
ic1 (fts indexed with index int column):
QUERY PLAN
`--SCAN fts1 VIRTUAL TABLE INDEX 0:

real	0m0,245s
user	0m0,229s
sys	0m0,016s

---------------------------------------------------------------
ic2 (fts indexed no index int column):
QUERY PLAN
`--SCAN fts1 VIRTUAL TABLE INDEX 0:

real	0m0,223s
user	0m0,219s
sys	0m0,004s

---------------------------------------------------------------
ic3 (fts unindexed with index int column):
QUERY PLAN
`--SCAN fts1 VIRTUAL TABLE INDEX 0:

real	0m0,223s
user	0m0,207s
sys	0m0,016s

---------------------------------------------------------------
ic4 (fts unindexed no index int column):
QUERY PLAN
`--SCAN fts1 VIRTUAL TABLE INDEX 0:

real	0m0,231s
user	0m0,223s
sys	0m0,008s

---------------------------------------------------------------
only fts:
QUERY PLAN
`--SCAN fts1 VIRTUAL TABLE INDEX 32:M5

real	0m1,298s
user	0m1,262s
sys	0m0,036s

---------------------------------------------------------------
both conditions in one expr (ic1):
QUERY PLAN
`--SCAN fts1 VIRTUAL TABLE INDEX 32:M5

real	0m2,280s
user	0m2,252s
sys	0m0,028s

---------------------------------------------------------------
both conditions in one expr (ic2):
QUERY PLAN
`--SCAN fts1 VIRTUAL TABLE INDEX 32:M5

real	0m2,322s
user	0m2,278s
sys	0m0,044s

---------------------------------------------------------------
both conditions in one expr (ic3):
QUERY PLAN
`--SCAN fts1 VIRTUAL TABLE INDEX 32:M5

real	0m2,350s
user	0m2,297s
sys	0m0,040s

---------------------------------------------------------------
both conditions in one expr (ic4):
QUERY PLAN
`--SCAN fts1 VIRTUAL TABLE INDEX 32:M5

real	0m2,373s
user	0m2,344s
sys	0m0,028s

---------------------------------------------------------------
using 'with' for general condition (ic1):
QUERY PLAN
`--SCAN fts1 VIRTUAL TABLE INDEX 32:M5

real	0m2,389s
user	0m2,357s
sys	0m0,032s

---------------------------------------------------------------
both conditions join (ic1):
QUERY PLAN
|--SCAN fts1 VIRTUAL TABLE INDEX 32:M5
`--SEARCH t1 USING INTEGER PRIMARY KEY (rowid=?)

real	0m1,711s
user	0m1,662s
sys	0m0,040s

---------------------------------------------------------------
both conditions from t1 join fts1 (ic1):
QUERY PLAN
|--SCAN fts1 VIRTUAL TABLE INDEX 32:M5
`--SEARCH t1 USING INTEGER PRIMARY KEY (rowid=?)

real	0m1,722s
user	0m1,683s
sys	0m0,036s

---------------------------------------------------------------
both conditions from with t1 join fts1 (ic1):
QUERY PLAN
|--SCAN fts1 VIRTUAL TABLE INDEX 32:M5
`--SEARCH t1 USING INTEGER PRIMARY KEY (rowid=?)

real	0m1,833s
user	0m1,805s
sys	0m0,028s

---------------------------------------------------------------
both conditions from with MATERIALIZED t1 join fts1 (ic1):
QUERY PLAN
|--SCAN fts1 VIRTUAL TABLE INDEX 32:M5
`--SEARCH t1 USING INTEGER PRIMARY KEY (rowid=?)

real	0m1,855s
user	0m1,813s
sys	0m0,040s

---------------------------------------------------------------
t1 ic3:
QUERY PLAN
`--SEARCH t1 USING INDEX t1_ic1_ndx (ic1>?)

real	0m0,001s
user	0m0,001s
sys	0m0,000s

---------------------------------------------------------------
t1 ic3 -- (10) --> fts:
QUERY PLAN
|--SCAN fts1 VIRTUAL TABLE INDEX 32:=M5
|--LIST SUBQUERY 1
|  `--SEARCH t1 USING COVERING INDEX t1_ic3_ndx (ic3>?)
`--USE TEMP B-TREE FOR ORDER BY

real	0m0,983s
user	0m0,967s
sys	0m0,016s

---------------------------------------------------------------
t1 ic3 -- (100) --> fts:
QUERY PLAN
|--SCAN fts1 VIRTUAL TABLE INDEX 32:=M5
|--LIST SUBQUERY 1
|  `--SEARCH t1 USING COVERING INDEX t1_ic3_ndx (ic3>?)
`--USE TEMP B-TREE FOR ORDER BY

real	0m10,145s
user	0m9,835s
sys	0m0,276s

sqlite_fts_explains.sh

#!/bin/bash

# Generates rows with token 'aaa' in each row + int columns.
# Check if fts has huge matching and another index has a tiny selection.
# Run: ./fts_explain.sh 2>&1 | tee results.txt

if [[ "${rebuild}" != "" ]]; then

rm -f a.db

sqlite3 a.db <<EOF
create table t1 (id integer primary key autoincrement, ic1 integer, ic2 integer, ic3 integer, ic4 integer, tc text);
create index t1_ic1_ndx on t1 (ic1);
create index t1_ic3_ndx on t1 (ic3);
create virtual table fts1 using fts5 (ic1, ic2, ic3 unindexed, ic4 unindexed, tc, content='t1', content_rowid='id');
EOF

i=1
for k in {1..1000}; do
    echo "insert ${k} page"
    echo -n "" > /tmp/fts_values_buf
    for j in {1..1000}; do
        d=
        [ $j -gt 1 ] && d=", "
        echo -en "${d}\n(${i}, ${i}, ${i}, ${i}, 'aaa')" >> /tmp/fts_values_buf
        ((++i))
    done
    V=$(cat /tmp/fts_values_buf)
    echo "insert into t1 (ic1, ic2, ic3, ic4, tc) values ${V}; insert into fts1 (ic1, ic2, ic3, ic4, tc) values ${V};" | sqlite3 a.db;
done;

fi

echo "---------------------------------------------------------------"
echo "ic1 (fts indexed with index int column):"
sqlite3 a.db "EXPLAIN QUERY PLAN select * from fts1 where ic1 > 999990 limit 10;"
time sqlite3 a.db "select * from fts1 where ic1 > 999990 limit 10;" > /dev/null

echo ""
echo "---------------------------------------------------------------"
echo "ic2 (fts indexed no index int column):"
sqlite3 a.db "EXPLAIN QUERY PLAN select * from fts1 where ic2 > 999920;"
time sqlite3 a.db "select * from fts1 where ic2 > 999920;" > /dev/null

echo ""
echo "---------------------------------------------------------------"
echo "ic3 (fts unindexed with index int column):"
sqlite3 a.db "EXPLAIN QUERY PLAN select * from fts1 where ic3 > 999953 limit 10;"
time sqlite3 a.db "select * from fts1 where ic3 > 999953 limit 10;" > /dev/null

echo ""
echo "---------------------------------------------------------------"
echo "ic4 (fts unindexed no index int column):"
sqlite3 a.db "EXPLAIN QUERY PLAN select * from fts1 where ic4 > 999991 limit 10;"
time sqlite3 a.db "select * from fts1 where ic4 > 999991 limit 10;" > /dev/null

echo ""
echo "---------------------------------------------------------------"
echo "only fts:"
sqlite3 a.db "EXPLAIN QUERY PLAN select * from fts1 where fts1 match 'aba' order by rank limit 10;"
time sqlite3 a.db "select * from fts1 where fts1 match 'aaa' order by rank limit 10;" > /dev/null

echo ""
echo "---------------------------------------------------------------"
echo "both conditions in one expr (ic1):"
sqlite3 a.db "EXPLAIN QUERY PLAN select * from fts1 where fts1 match 'aaa' and ic1 > 999992 order by rank limit 10;"
time sqlite3 a.db "select * from fts1 where fts1 match 'aaa' and ic1 > 999992 order by rank limit 10;" > /dev/null

echo ""
echo "---------------------------------------------------------------"
echo "both conditions in one expr (ic2):"
sqlite3 a.db "EXPLAIN QUERY PLAN select * from fts1 where fts1 match 'aaa' and ic2 > 999992 order by rank limit 10;"
time sqlite3 a.db "select * from fts1 where fts1 match 'aaa' and ic2 > 999992 order by rank limit 10;" > /dev/null

echo ""
echo "---------------------------------------------------------------"
echo "both conditions in one expr (ic3):"
sqlite3 a.db "EXPLAIN QUERY PLAN select * from fts1 where fts1 match 'aaa' and ic3 > 999992 order by rank limit 10;"
time sqlite3 a.db "select * from fts1 where fts1 match 'aaa' and ic3 > 999992 order by rank limit 10;" > /dev/null

echo ""
echo "---------------------------------------------------------------"
echo "both conditions in one expr (ic4):"
sqlite3 a.db "EXPLAIN QUERY PLAN select * from fts1 where fts1 match 'aaa' and ic4 > 999992 order by rank limit 10;"
time sqlite3 a.db "select * from fts1 where fts1 match 'aaa' and ic4 > 999992 order by rank limit 10;" > /dev/null

echo ""
echo "---------------------------------------------------------------"
echo "using 'with' for general condition (ic1):"
sqlite3 a.db "EXPLAIN QUERY PLAN with a as (select fts1, rank, * from fts1 where ic1 > 999993) select * from a where fts1 match 'aaa' order by a.rank limit 10;"
time sqlite3 a.db "with a as (select fts1, rank, * from fts1 where ic1 > 999993) select * from a where fts1 match 'aaa' order by a.rank limit 10;" > /dev/null

echo ""
echo "---------------------------------------------------------------"
echo "both conditions join (ic1):"
SQL="select * from fts1 join t1 on fts1.rowid==t1.id where fts1 match 'aaa' and t1.ic1 > 999992 order by rank;"
sqlite3 a.db "EXPLAIN QUERY PLAN ${SQL}"
time sqlite3 a.db "${SQL}" > /dev/null

echo ""
echo "---------------------------------------------------------------"
echo "both conditions from t1 join fts1 (ic1):"
SQL="select * from (select id from t1 where ic1 > 999992) as a join fts1 on fts1.rowid==a.id where fts1 match 'aaa' order by rank limit 10;"
sqlite3 a.db "EXPLAIN QUERY PLAN ${SQL}"
time sqlite3 a.db "${SQL}" > /dev/null

echo ""
echo "---------------------------------------------------------------"
echo "both conditions from with t1 join fts1 (ic1):"
SQL="with a as (select id from t1 where ic1 > 999992) select * from a join fts1 on fts1.rowid==a.id where fts1 match 'aaa' order by rank limit 10;"
sqlite3 a.db "EXPLAIN QUERY PLAN ${SQL}"
time sqlite3 a.db "${SQL}" > /dev/null

echo ""
echo "---------------------------------------------------------------"
echo "both conditions from with MATERIALIZED t1 join fts1 (ic1):"
SQL="with a as MATERIALIZED (select id from t1 where ic1 > 999992) select * from a join fts1 on fts1.rowid==a.id where fts1 match 'aaa' order by rank limit 10;"
sqlite3 a.db "EXPLAIN QUERY PLAN ${SQL}"
time sqlite3 a.db "${SQL}" > /dev/null

echo ""
echo "---------------------------------------------------------------"
echo "t1 ic3:"
SQL="select * from t1 where ic1 > 999992 limit 10;"
sqlite3 a.db "EXPLAIN QUERY PLAN ${SQL}"
time sqlite3 a.db "${SQL}" > /dev/null

echo ""
echo "---------------------------------------------------------------"
echo "t1 ic3 -- (10) --> fts:"
SQL="select * from fts1 where fts1.rowid in (select id from t1 where ic3 > 999990) AND fts1 match 'aaa' order by rank limit 10;"
sqlite3 a.db "EXPLAIN QUERY PLAN ${SQL}"
time sqlite3 a.db "${SQL}" > /dev/null

echo ""
echo "---------------------------------------------------------------"
echo "t1 ic3 -- (100) --> fts:"
SQL="select * from fts1 where fts1.rowid in (select id from t1 where ic3 > 999900) AND fts1 match 'aaa' order by rank limit 10;"
sqlite3 a.db "EXPLAIN QUERY PLAN ${SQL}"
time sqlite3 a.db "${SQL}" > /dev/null