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?)