From: Tobias Brox <tobias@nordicbet.com>
To: pgsql-performance@postgresql.org
Subject: slow queue-like empty table
Date: Thu, 28 Sep 2006 08:56:31 +0200
Message-ID: <20060928065631.GA28138@oppetid.no> (raw)
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 total rows
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.518 rows=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 ...
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tobias@nordicbet.com
Subject: Re: slow queue-like empty table
In-Reply-To: <20060928065631.GA28138@oppetid.no>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox