slow queue-like empty table - Mailing list pgsql-performance

From Tobias Brox
Subject slow queue-like empty table
Date
Msg-id 20060928065631.GA28138@oppetid.no
Whole thread Raw
Responses Re: slow queue-like empty table  (Tobias Brox <tobias@nordicbet.com>)
Re: slow queue-like empty table  (Andrew Sullivan <ajs@crankycanuck.ca>)
List pgsql-performance
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 ...


pgsql-performance by date:

Previous
From: "Jim C. Nasby"
Date:
Subject: Re: Problems with inconsistant query performance.
Next
From: Tobias Brox
Date:
Subject: Re: slow queue-like empty table