SQLite Forum

Is there a way to mark an index 'explicit' only?
Login

Is there a way to mark an index 'explicit' only?

(1) By Deon Brewis (deonb) on 2021-05-03 05:56:08 [source]

I have an index that I only need for one specific query.

However, for some reason the SQLite planner loves the index and uses it for all kinds of general purpose queries. And it slows down everything else by many orders of magnitude.

If I delete the index everything is great, but I need it in one specific case.

Can I somehow mark it so that the optimized ignores it unless I specify "indexed by"?

(2) By Deon Brewis (deonb) on 2021-05-03 06:05:22 in reply to 1 [link] [source]

PS: It appears the optimizer loves it because it is both covering AND matches the 'ORDER BY' of most of my other queries directly. But of course then it ignores the 'WHERE' clauses and does a table scan - through a million rows.

The 'WHERE' clause generally filters things down to 5 items or so, so it's MUCH better to do searches via an index followed by b-tree ordering. But looks the optimizer can't figure out the expected results and does the table scan in order to get the ordering.

(3) By Gunter Hick (gunter_hick) on 2021-05-03 07:16:12 in reply to 2 [link] [source]

See https://sqlite.org/optoverview.html

- Run ANALYZE to create STAT table entries.
- Fudge STAT table entries to convince the QP
- Use CROSS JOIN to force table order
- Use unary + to disqualify columns

(4) By Gunter Hick (gunter_hick) on 2021-05-03 07:27:20 in reply to 2 [link] [source]

See also https://sqlite.org/queryplanner-ng.html Section 5

(5.1) By Simon Slavin (slavin) on 2021-05-03 12:25:39 edited from 5.0 in reply to 2 [link] [source]

Please run ANALYZE on that database, then see whether the query optimizer is still making poor choices.

If it is, I might argue that you have uncovered a bug in SQLite, and we may get a fix. You should not have to worry about this level of detail yourself. SQLite should be doing it for you.

(6) By Deon Brewis (deonb) on 2021-05-07 04:30:14 in reply to 5.1 [link] [source]

Yeah I've run ANALYZE many times. I can share the database with you if you want, but it's 4 GB, and I haven't figured out a way to make an isolated repro.