Hi list, I've been experiencing a weird performance issue lately. I have a very simple (and usually very fast) query: SELECT track_logs.id FROM track_logs WHERE track_logs.track_id = <some id> AND track_logs.track_status_id = 1 AND track_logs.date >= now() - interval '1 hours' FOR UPDATE Whose plan is: "LockRows (cost=0.00..26.73 rows=1 width=14)" " -> Index Scan using idx_track_logs_track_id on track_logs (cost=0.00..26.72 rows=1 width=14)" " Index Cond: (track_id = <some id>)" " Filter: ((track_status_id = 1) AND (date >= (now() - '01:00:00'::interval)))" The same query, without FOR UPDATE, takes just 68 milliseconds. With the FOR UPDATE, it takes like half a minute or more to finish. Now, I understand the for update part may be blocking on some other transaction, and it's probably the case. But I cannot figure out which transaction it would be. There *are*, in fact, connections in <idle in transaction> state, which makes me think those would be the culprit. But for the life of me, I cannot make sense of the pg_locks view, which shows all locks as granted: PID Relation XID TX Mode Granted Start 14751 5551986 154/4038460 AccessShareLock Yes 2011-11-03 12:45:03.551516-05 14751 5526310 154/4038460 RowShareLock Yes 2011-11-03 12:45:03.551516-05 14751 5552008 154/4038460 RowExclusiveLock Yes 2011-11-03 12:45:03.551516-05 14751 5552020 154/4038460 RowExclusiveLock Yes 2011-11-03 12:45:03.551516-05 14751 5552008 154/4038460 AccessShareLock Yes 2011-11-03 12:45:03.551516-05 14751 5525296 154/4038460 RowShareLock Yes 2011-11-03 12:45:03.551516-05 14751 5525292 154/4038460 RowShareLock Yes 2011-11-03 12:45:03.551516-05 14751 5552019 154/4038460 AccessShareLock Yes 2011-11-03 12:45:03.551516-05 14751 5552019 154/4038460 RowExclusiveLock Yes 2011-11-03 12:45:03.551516-05 14751 5552020 154/4038460 AccessShareLock Yes 2011-11-03 12:45:03.551516-05 14751 5525292 154/4038460 RowExclusiveLock Yes 2011-11-03 12:45:03.551516-05 14751 154/4038460 154/4038460 ExclusiveLock Yes 2011-11-03 12:45:03.551516-05 14751 154/4038460 ExclusiveLock Yes 2011-11-03 12:45:03.551516-05 14751 5526308 154/4038460 AccessShareLock Yes 2011-11-03 12:45:03.551516-05 Where should I look? What other information should I provide? -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
