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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
