PostgreSQL INSERT INTO 4 million rows takes forever

Question

I use Postgres 14.1 on Windows 10 for development and I have little experience with Postgres.

Recently, I wrote an INSERT query to copy records from one table to another. The source table has less than 4000K records. The problem is that the query takes forever to run in pgAdmin and hits timeouts in my app, no matter how large the timeout limit is. I can't paste the real query but it is a simple "insert if not exists" construct:

INSERT INTO table1 (a, b, c) 
SELECT a, b, c 
FROM table2
WHERE NOT EXISTS (SELECT 1 FROM table1 AS T1 WHERE table2.refid = T1.refid)

Data selection part itself takes few seconds but with the INSERT INTO takes forever.

I tried to remove all indexes and constraints from the target table but it did not change anything. I have tried to limit SELECT result and it runs fine.

INSERT INTO table1 (a, b, c) 
SELECT a, b, c 
FROM table2
WHERE NOT EXISTS (SELECT 1 FROM table1 AS T1 WHERE table2.refid = T1.refid) 
LIMIT 4000000

What is the magic behind LIMIT threshold which is above the actual number of records in the source table?

The current postgresql.conf is as follow:

max_connections = 100
shared_buffers = 2GB
effective_cache_size = 6GB
maintenance_work_mem = 512MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
work_mem = 6990kB
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 6
max_parallel_workers_per_gather = 3
max_parallel_workers = 6
max_parallel_maintenance_workers = 3

UPDATE

After few retries, deleting all rows, and inserting again, the query runs fine without the LIMIT statement and completes within 20 seconds. Still curious what could cause the issue.

Answer 1

If refid is among the columns to be inserted, the best solution would be to use INSERT ... ON CONFLICT:

INSERT INTO table1 (a, b, c, refid)
SELECT a, b, c, refid
FROM table2
ON CONFLICT ON (refid) DO NOTHING;

That requires a unique constraint on table2(refid).