different query plan result when executed within transaction
(1) By Mark Wagner (markxwagner) on 2021-06-09 16:13:26 [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.
Thanks.
(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.
Dan.
(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 [link] [source]
What happens if the query plan stability guarantee is enabled? (Does the query plan stability guarantee have anything to do with this, anyways?)