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.
pg_stat_activity
,pg_locks
, and the physical size of tables might also allow insights. Finally, hardware issues?