Skip site navigation (1) Skip section navigation (2)

Re: slow queue-like empty table

From: Andrew Sullivan <ajs(at)crankycanuck(dot)ca>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: slow queue-like empty table
Date: 2006-09-28 20:17:10
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
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.


Andrew Sullivan  | ajs(at)crankycanuck(dot)ca
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism. 
                --Brad Holland

In response to

pgsql-performance by date

Next:From: Merlin MoncureDate: 2006-09-28 20:55:57
Subject: Re: Performace Optimization for Dummies
Previous:From: Carlo StonebanksDate: 2006-09-28 20:15:03
Subject: Re: Performace Optimization for Dummies

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group