I have a query which really should be lightning fast (limit 1 from
index), but which isn't. I've checked the pg_locks table, there are no
locks on the table. The database is not under heavy load at the moment,
but the query seems to draw CPU power. I checked the pg_locks view, but
found nothing locking the table. It's a queue-like table, lots of rows
beeing added and removed to the queue. The queue is currently empty.
Have a look:
NBET=> vacuum verbose analyze my_queue;
INFO: vacuuming "public.my_queue"
INFO: index "my_queue_pkey" now contains 34058 row
versions in 390 pages
DETAIL: 288 index pages have been deleted, 285 are current
ly reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "my_queue": found 0 removable, 34058 nonremovable row versions in 185 pages
DETAIL: 34058 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing "public.my_queue"
INFO: "my_queue": scanned 185 of 185 pages, containing 0 live rows and 34058 dead rows; 0 rows in sample, 0 estimated
totalrows
VACUUM
NBET=> explain analyze select bet_id from my_queue order by bet_id limit 1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..0.04 rows=1 width=4) (actual time=402.525..402.525 rows=0 loops=1)
-> Index Scan using my_queue_pkey on stats_bet_queue (cost=0.00..1314.71 rows=34058 width=4) (actual
time=402.518..402.518rows=0 loops=1)
Total runtime: 402.560 ms
(3 rows)
NBET=> select count(*) from my_queue;
count
-------
0
(1 row)
It really seems like some transaction is still viewing the queue, since
it found 38k of non-removable rows ... but how do I find the pid of the
transaction viewing the queue? As said, the pg_locks didn't give me any
hints ...
