2

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.

3
  • @JorgeCampos The answer you pointed to is different, the op has performance issues and far more records to insert. I agree that in such cases one statement can not perform well. My case is a bit different. Why INSERT with LIMIT 4000K takes few seconds and without does not complete. Please note that the source has less than 4000K Dec 1, 2021 at 18:55
  • It may just be a matter of lack of resources (memory) for the full one. Everything you do on your database runs within a session, meaning everything is being stored to be fully committed at the end of the process. That's the reason I suggested the batch execution Dec 1, 2021 at 21:42
  • Available RAM on server, available disk space, avg row size and cardinality of both tables before the insert? Concurrent operations (in the db cluster and on the whole db server)? pg_stat_activity, pg_locks, and the physical size of tables might also allow insights. Finally, hardware issues? Dec 2, 2021 at 12:05

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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