SQLite Forum

Query performance regression after v3.33.0 (OP_SeekScan?)
Login
My application hit a severe performance regression upgrading SQLite to v3.35.4 from v3.33.0, I think possibly due to the new OP_SeekScan feature. I've distilled a reproduction in this gist:

https://gist.github.com/mlin/a5e9ef7ca0d6583b8397a0fb324e4327

Briefly, our database stores a graph, with nodes, edges connecting them, and indexes thereof. Then we get a 1-column temporary table providing a certain subset of node IDs, and we wish to query for those edges touching only nodes included in the subset:

```
SELECT count(_rowid_)
FROM edge
WHERE node_from IN temp.sub_nodes AND node_to IN temp.sub_nodes
```

With the numerous constants in the Python script, SQLite v3.35.5 runs this query roughly 500X slower than v3.31.1. (My [Rust application](https://github.com/mlin/gfabase), on which the repro script is based, hit the problem after upgrading from v3.33.0 to v3.35.4)

The high-level "explain query plans" are identical between the two versions, but, the opcode-level "explain" results differ (shown in the gist), notably including the new SeekScan operation. I speculate perhaps the *two* "IN" clauses in our query lead to some kind of quadratic cost to the new scan attempt?

Thanks in advance for advice/attention.