postgresql optimize query with 'where not in' clause

Question

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 ;

Answer 1

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.

Answer 2

Assuming a UNIQUE index on id (typically the PRIMARY KEY), an UPSERT should be simplest - and fastest if you insert most of the rows:

INSERT INTO table2
SELECT * FROM t1
ON CONFLICT DO NOTHING;

Else, there are a couple of techniques (NOT EXISTS like @Daniel demonstrates being one of the good options):

• Select rows which are not present in other table

NOT IN is typically the least favorable.

Answer 3

EXCEPT returns all rows that are in the result of query1 but not in the result of query2

On the other hand, please take a loot at EXCEPT command as below

INSERT INTO table2   
SELECT * FROM table1
EXCEPT 
SELECT * FROM table2;