From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-performance@postgresql.org
Subject: Re: slow queue-like empty table
Date: Thu, 28 Sep 2006 16:17:10 -0400
Message-ID: <20060928201710.GF19794@phlogiston.dyndns.org> (raw)
In-Reply-To: <20060928065631.GA28138@oppetid.no>
References: <20060928065631.GA28138@oppetid.no>
On Thu, Sep 28, 2006 at 08:56:31AM +0200, Tobias Brox wrote:
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: "my_queue": found 0 removable, 34058 nonremovable row versions in 185 pages
^^^^^^^
You have a lot of dead rows that can't be removed. You must have a
lot of other transactions in process. Note that nobody needs to be
_looking_ at those rows for them to be unremovable. The transactions
just have to be old enough.
> -------------------------------------------------------------------------------------------------------------------------------------------------------
> 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)
I'm amazed this does an indexscan on an empty table.
If this table is "hot", my bet is that you have attempted to optimise
in an area that actually isn't an optimisation under PostgreSQL.
That is, if you're putting data in there, a daemon is constantly
deleting from it, but all your other transactions depend on knowing
the value of the "unprocessed queue", the design just doesn't work
under PostgreSQL. It turns out to be impossible to keep the table
vacuumed well enough for high performance.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism.
--Brad Holland
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: ajs@crankycanuck.ca
Subject: Re: slow queue-like empty table
In-Reply-To: <20060928201710.GF19794@phlogiston.dyndns.org>
* 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