How to optimize Merge Anti Join?

Question

While trying to find the fastest way to do a bulk update of a large table, I came up with the following plan:

having main_table (potentially above 1000 mln rows) and update table up to a few million rows. Data in update are indexed according to main_table (have corresponding id columns).

1. copy rows from main_table into temp_table where id not in update id table (where data has actually changed).
2. simple append of the update table onto temp_table
3. drop main_table
4. rename temp_table to main_table
5. renumerate main_table pk
6. create the next update with python and repeat.

The above is to be done with the following sequence of queries:

CREATE TABLE temp_table (like main_table including all);
INSERT INTO  temp_table (listing_id,date,available,price,timestamp)
    (SELECT listing_id,date,available,price,timestamp
         FROM   main_table c
         WHERE  NOT EXISTS (SELECT
                            FROM   update
                            WHERE  id = c.id));
INSERT INTO temp_table (listing_id,date,available,price,parsing_timestamp)
    (SELECT listing_id,date,available,price,timestamp
     FROM   update c);
DROP  TABLE main_table cascade;
ALTER TABLE temp_table RENAME TO main_table;
ALTER TABLE main_table DROP COLUMN id
ALTER TABLE main_table ADD COLUMN id SERIAL PRIMARY KEY;
DROP  TABLE update cascade;

While debugging the queries, I have found the slow queries with explain:

EXPLAIN (ANALYZE, BUFFERS) CREATE TABLE temp_table (like main_table including all);
INSERT INTO  temp_table (listing_id,date,available,price,timestamp)
    (SELECT listing_id,date,available,price,timestamp
         FROM   main_table c
         WHERE  NOT EXISTS (SELECT
                            FROM   update
                            WHERE  id = c.id));

This is the result of EXPLAIN (ANALYZE, BUFFERS) as requested in comments:

QUERY PLAN
Merge Anti Join (cost=513077.42..4789463.48 rows=109800833 width=40) (actual time=1216.018..48520.564 rows=112757269 loops=1)
Merge Cond: (c.id = update.id)
Buffers: shared hit=4 read=1359891 written=2838, temp read=8701 written=15077
-> Index Scan using cals_pkey on cals c (cost=0.57..3958576.01 rows=113119496 width=44) (actual time=0.857..29573.798 rows=113119497 loops=1)
Buffers: shared hit=1 read=1330192 written=2838
 -> Sort (cost=513076.85..521373.51 rows=3318663 width=8) (actual time=1215.147..1260.191 rows=362229 loops=1)
Sort Key: update.id
Sort Method: external merge Disk: 58480kB
Buffers: shared hit=3 read=29699, temp read=8701 written=15077
-> Seq Scan on update (cost=0.00..62881.63 rows=3318663 width=8) (actual time=0.179..423.100 rows=3318663 loops=1)
Buffers: shared read=29695
Planning Time: 0.259 ms
Execution Time: 52757.349 ms

How to optimize the queries?

Answer 1

Concerning the sort performance in the query, there is this:

Sort Method: external merge Disk: 58480kB

Meaning, there is not enough work_mem to sort in RAM. The operation spills to disk, which is typically a lot more expensive. If you have more RAM to spare, increase the setting for the current session (only!).

See:

• Improving sort performance in GROUP BY clause

Check with SHOW work_mem;, then add 100 MB to the number you get and use it below instead of '???MB'. If you still see mention of "disk" in the EXPLAIN output, add more.

But there is more: You create the new table with:

CREATE TABLE temp_table (like main_table including all);

INCLUDING ALL, as the wording suggests, also includes constraints and indexes. Obviously, at least a PK constraint as indicated by cals_pkey in the EXPLAIN output. I assume a column:

id bigserial PRIMARY KEY

It's a lot cheaper to create an index on a filled table than to update it incrementally. The index is also typically better optimized. So you should drop the PK (and all other indexes and constraints) before you write to the table, and recreate them afterwards.

Also, dropping and re-creating the id column on a huge table is needlessly expensive. Create new values for id on the fly with row_number() (do you really need to renumber?). Then update the associated SEQUENCE accordingly.

Related:

• Copy table with separate sequences for serial columns

• How to copy structure and contents of a table, but with separate sequence?

Assuming ...

• you need to renumber all rows
• no concurrent load on the table
• parsing_timestamp is a typo in the question and should really be the same column timestamp. Else, adapt accordingly.

... the operation could look something like this:

BEGIN;

SET LOCAL work_mem = '???MB';  -- see above

CREATE TABLE temp_table (LIKE main_table INCLUDING ALL);

ALTER TABLE temp_table DROP CONSTRAINT temp_table_pkey;  -- before!
-- drop more indexes or constraints?

INSERT INTO temp_table 
      (id                  , listing_id,date,available,price,timestamp)
SELECT row_number() OVER (), listing_id,date,available,price,timestamp
FROM  (
   SELECT listing_id,date,available,price,timestamp  -- No parentheses around SELECT
   FROM   main_table c
   WHERE  NOT EXISTS (SELECT FROM update WHERE id = c.id);

   UNION ALL      
   SELECT listing_id,date,available,price,timestamp  -- parsing_timestamp ???
   FROM   update
   ) sub

DROP  TABLE main_table CASCADE;
DROP  TABLE update CASCADE;

ALTER TABLE temp_table RENAME TO main_table;
ALTER TABLE main_table ADD CONSTRAINT main_table_pkey PRIMARY KEY (id);  -- after!
-- recreate more indexes or constraints?

SELECT setval(pg_get_serial_sequence('main_table', 'id'), max(id)) FROM main_table;  -- reset sequence

COMMIT;

Also, some more of the advice for "Populating a Database" in the manual may well apply to your case. Maybe more maintenance_work_mem, maybe ANALYZE or VACUUM ANALYZE afterwards, ... Have a look.