in postgresl 9.0 we have a sql query:
SELECT count(*) FROM lane WHERE not exists (SELECT 1 FROM id_map WHERE id_map.new_id=lane.lane_id and id_map.column_name='lane_id' and id_map.table_name='lane') and lane.lane_id is not null;
which usually takes about 1.5 seconds to complete. Here's an explanation plan: http://explain.depesz.com/s/axNN
Sometimes this request freezes and does not end. It can work even 11 hours without success. Then it takes up 100% of the processor.
The only locks that this request executes are "AccessShareLock", and all of them are provided.
SELECT a.datname, c.relname, l.transactionid, l.mode, l.granted, a.usename, a.current_query, a.query_start, age(now(), a.query_start) AS "age", a.procpid FROM pg_stat_activity a JOIN pg_locks l ON l.pid = a.procpid JOIN pg_class c ON c.oid = l.relation ORDER BY a.query_start;
The request is executed as part of the java process, which connects to the database using the connection pool and sequentially performs similar similar requests in this format:
SELECT count(*) FROM {} WHERE not exists (SELECT 1 FROM id_map WHERE id_map.new_id={}.{} and id_map.column_name='{}' and id_map.table_name='{}') and {}.{} is not null
no updates or deletes happen in parallel with this process, so I don’t think the vacuum problem could be the problem here. Before starting the whole process (therefore, before starting 6 queries of this type), all tables are analyzed.
Postgres logs do not show entries for long requests because they never end and therefore can never be logged.
Any idea what might cause this behavior and how to prevent it?
explanation plan without analysis:
Aggregate (cost=874337.91..874337.92 rows=1 width=0) -> Nested Loop Anti Join (cost=0.00..870424.70 rows=1565283 width=0) Join Filter: (id_map.new_id = lane.lane_id) -> Seq Scan on lane (cost=0.00..30281.84 rows=1565284 width=8) Filter: (lane_id IS NOT NULL) -> Materialize (cost=0.00..816663.60 rows=1 width=8) -> Seq Scan on id_map (cost=0.00..816663.60 rows=1 width=8) Filter: (((column_name)::text = 'lane_id'::text) AND ((table_name)::text = 'lane'::text))