SQLite Forum

FOR IN-OPERATOR optimization doesn't occur through view
Login

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;
with info as:
CREATE TABLE IF NOT EXISTS "info" (
    infohash_hex not null primary key,
    obtained_datetime default (datetime('now')),
    name not null );
If I do a query testing if something is in completed_infohash_hex:
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
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 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
I would expect the first form would result in the same query plan as the latter. I believe it's significantly faster too. Thanks!

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