Re: slow queue-like empty table

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxxx
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism. 
                --Brad Holland


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux