postgresql optimize query with 'where not in' clause
First you may try two things:


Do not use WITH because it's an optimization fence. As of PostgreSQL 10, the result of the CTE query will be materialized even if it would be better not to.
Use NOT EXISTS instead of NOT IN. Long explanation of why on stackoverflow: https://stackoverflow.com/questions/173041/not-in-vs-not-exists




 INSERT INTO table2 
 SELECT * FROM table1
 WHERE NOT EXISTS (SELECT 1 FROM table2 WHERE table2.id=table1.id);


Also, please study and share the result of an EXPLAIN of the query or if possible EXPLAIN ANALYZE.