FOR IN-OPERATOR optimization doesn't occur through view
(1) By anacrolix on 2021-07-23 01:02:40 [link] [source]
I have a view like this:
CREATE VIEW completed_infohash_hex as select infohash_hex from info;
If I do a query testing if something is in completed_infohash_hex:
CREATE TABLE IF NOT EXISTS "info" ( infohash_hex not null primary key, obtained_datetime default (datetime('now')), name not null );
But if I use the table directly, selecting the column to match, just as the view should:
sqlite> explain query plan select infohash_hex, count(*) from sample where infohash_hex not in (select * from completed_infohash_hex) group by infohash_hex; QUERY PLAN |--SCAN sample USING COVERING INDEX sqlite_autoindex_sample_1 `--LIST SUBQUERY 1 `--SCAN info USING COVERING INDEX sqlite_autoindex_info_1
I would expect the first form would result in the same query plan as the latter. I believe it's significantly faster too. Thanks!
sqlite> explain query plan select infohash_hex, count(*) from sample where infohash_hex not in (select infohash_hex from info) group by infohash_hex; QUERY PLAN |--SCAN sample USING COVERING INDEX sqlite_autoindex_sample_1 `--USING INDEX sqlite_autoindex_info_1 FOR IN-OPERATOR
(2) By anacrolix on 2021-07-25 13:33:46 in reply to 1 [source]
Is there a document I missed on how these things are calculated, or why they are different when views are involved?