SQLite Forum

different query plan result when executed within transaction
Login

different query plan result when executed within transaction

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

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.

Thanks.

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

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.

Dan.

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

Does the database you're using this on pass <code>PRAGMA integrity_check</code> ?

Do you get this behaviour if you execute one of the queries immediately after <code>BEGIN</code> ?

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 [link]

What happens if the [query plan stability guarantee](https://sqlite.org/queryplanner-ng.html#qpstab) is enabled? (Does the query plan stability guarantee have anything to do with this, anyways?)