How to optimize Merge Anti Join?
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.