postgresql optimize query with 'where not in' clause I have query to select all rows from table1 that they are not in tabl2 then insert them in table2.
This query is taking log time to execute since I have millions of rows in table2.

Is there better way to optimize or rewrite this query to make it execute faster.

WITH t1 AS (
select *
from table1
where id  not in (select id table2))
insert into table2
select * from t1 ;
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.