having the following query, my plan was to filter with the subquery first and then to
INSERT INTO cals_new (listing_id,date,available,price,timestamp) (SELECT listing_id,date,available,price,timestamp FROM cals c WHERE NOT EXISTS ( SELECT FROM update WHERE id = c.id ));
EXPLAIN gives the following:
Insert on cals_new (cost=513077.42..5338467.65 rows=109800833 width=44) -> Merge Anti Join (cost=513077.42..5338467.65 rows=109800833 width=44) Merge Cond: (c.id = update.id) -> Index Scan using cals_pkey on cals c (cost=0.57..3958576.01 rows=113119496 width=44) -> Sort (cost=513076.85..521373.51 rows=3318663 width=8) Sort Key: update.id -> Seq Scan on update (cost=0.00..62881.63 rows=3318663 width=8)
If I understend the
EXPLAIN output correctly, it does the expansive
INSERT first, and only filters after.
How to optimise the query so that it works as expected? More specifically, filters with the subquery first and then does the