Hello,

I encounter performance problems related to BLOOM filter in recursive queries, with SQLite version 3.40.0.

I have a huge table named "objs" that has 3 columns, "s", "p" and "o" (corresponding to the subject, predicate and object members of RDF triples).

Here is my query:

WITH RECURSIVE transit(x)
AS (  SELECT s FROM objs WHERE p=9 AND o=32805
UNION SELECT objs.s FROM objs, transit WHERE objs.p=9 AND objs.o=transit.x)
SELECT x FROM transit;

and the corresponding query plan :

QUERY PLAN
|--CO-ROUTINE transit
|  |--SETUP
|  |  `--SEARCH objs USING COVERING INDEX index_objs_op (o=? AND p=?)
|  `--RECURSIVE STEP
|     |--SCAN transit
|     |--BLOOM FILTER ON objs (o=? AND p=?)
|     `--SEARCH objs USING INDEX index_objs_op (o=? AND p=?)
`--SCAN transit

Performance measured in CLI:
Run Time: real 0.034 user 0.026844 sys 0.006627


If I disable BLOOM filter with ".testctrl optimizations 0x80000", the query plan is:
QUERY PLAN
|--CO-ROUTINE transit
|  |--SETUP
|  |  `--SEARCH objs USING COVERING INDEX index_objs_op (o=? AND p=?)
|  `--RECURSIVE STEP
|     |--SCAN transit
|     `--SEARCH objs USING COVERING INDEX index_objs_op (o=? AND p=?)
`--SCAN transit

and the performance:
Run Time: real 0.001 user 0.000503 sys 0.000116

which is about 34 times faster. Since my application runs many similar queries with different values, it has a high impact.

I also noticed similar performance problems on other, more complex, queries, all of them involving RECURSIVE and BLOOM filter in the query plan.

The entire database can be downloaded here:

https://filesender.renater.fr/?s=download&token=196d5780-222d-4fe9-a3d0-657218ea74d4


Additional question: is there a way to disable BLOOM filter outside of the CLI (e.g. via a PRAGMA) ?

Best regards,
Jean-Baptiste LAMY