0

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 ;
3

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):

NOT IN is typically the least favorable.

2

First you may try two things:


 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.

1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.