Really really slow select count(*)

reply was meant for the list

---------- Forwarded message ----------
From: felix <crucialfelix(at)gmail(dot)com>
Date: Fri, Feb 4, 2011 at 4:39 PM
Subject: Re: [PERFORM] Really really slow select count(*)
To: Greg Smith <greg(at)2ndquadrant(dot)com>

On Fri, Feb 4, 2011 at 3:56 PM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:

> PostgreSQL version? If you're running on 8.3 or earlier, I would be
> suspicous that your Free Space Map has been overrun.
>

8.3

>
> What you are seeing is that the table itself is much larger on disk than
> it's supposed to be.
>

which part of the explain told you that ?

> shaun thomas

SELECT relpages*8/1024 FROM pg_class
WHERE relname='fastadder_fastadderstatus';

458MB

way too big. build_cache is text between 500-1k chars

> That can be caused by frequent UPDATEs if you don't have vacuum cleanup
> working effectively, you'll get lots of dead sections left behind from
> UPDATEs in the middle.
>

ok, I just vacuumed it (did this manually a few times as well). and auto is
on.

still:
32840.000ms
and still 458MB

> The best way to fix all this is to run CLUSTER on the table.
>

http://www.postgresonline.com/journal/archives/10-How-does-CLUSTER-ON-improve-index-performance.html

now that would order the data on disk by id (primary key)
the usage of the table is either by a query or by position_in_queue which is
rewritten often (I might change this part of the app and pull it out of this
table)

is this definitely the best way to fix this ?

thanks for your help !

That will introduce a bit of downtime while it holds a lock on the table
> (only a few minutes based on what you've shown here), but the copy you'll
> have afterwards won't be spread all over disk anymore.
>
> --
> Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
> PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
> "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
>
>