SQLite Forum

different query plan result when executed within transaction

different query plan result when executed within transaction

(1) By Mark Wagner (markxwagner) on 2021-06-09 16:13:26 [link] [source]

I wrote some code to automatically run each of my queries through explain query plan to check for full table scans.

This only runs while executing tests.

But I noticed that in some cases I get a different result when the eqp is executed within a transaction. Specifically, when the eqp is run outside of the transaction it correctly shows a search using index. When executed within a transaction it show a full table scan.

I can try to put together an example but thought I'd check ahead if there area any known issues around this.


(2) By Dan Kennedy (dan) on 2021-06-09 18:10:58 in reply to 1 [link] [source]

Not that I know of. AFAIK a query plan should depend on the schema and contents of the sqlite_stat* tables only, not whether or not a transaction is open.


(3) By Simon Slavin (slavin) on 2021-06-10 07:24:20 in reply to 1 [link] [source]

Does the database you're using this on pass PRAGMA integrity_check ?

Do you get this behaviour if you execute one of the queries immediately after BEGIN ?

If not, what instructions are in the transaction before the first query which behaves that way ?

(4) By anonymous on 2021-06-10 07:31:18 in reply to 1 [source]

What happens if the query plan stability guarantee is enabled? (Does the query plan stability guarantee have anything to do with this, anyways?)