Date: Thu, 28 Sep 2006 16:17:10 -0400
From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-performance@postgresql.org
Subject: Re: slow queue-like empty table
Message-ID: <20060928201710.GF19794@phlogiston.dyndns.org>
References: <20060928065631.GA28138@oppetid.no>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20060928065631.GA28138@oppetid.no>
User-Agent: Mutt/1.5.9i

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