Really really slow select count(*)

[felix <crucialfelix@xxxxxxxxx>]

reply was meant for the list

---------- Forwarded message ----------
From: felix <crucialfelix@xxxxxxxxx>
Date: Fri, Feb 4, 2011 at 4:39 PM
Subject: Re: Really really slow select count(*)
To: Greg Smith <greg@xxxxxxxxxxxxxxx>

On Fri, Feb 4, 2011 at 3:56 PM, Greg Smith <greg@xxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxx   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books