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