Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id C0EF51337B83
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Fri,  4 Feb 2011 12:27:13 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 26617-03
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Fri,  4 Feb 2011 16:27:06 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mh4.mail.rice.edu (mh4.mail.rice.edu [128.42.199.11])
	by mail.postgresql.org (Postfix) with ESMTP id D6AD71337B2F
	for <pgsql-performance@postgresql.org>;
	Fri,  4 Feb 2011 12:27:05 -0400 (AST)
Received: from mh4.mail.rice.edu (localhost.localdomain [127.0.0.1])
	by mh4.mail.rice.edu (Postfix) with ESMTP id 1F64D28F8F5;
	Fri,  4 Feb 2011 10:27:05 -0600 (CST)
X-Virus-Scanned: by amavis-2.6.4 at mh4.mail.rice.edu, auth channel
Received: from mh4.mail.rice.edu ([127.0.0.1])
	by mh4.mail.rice.edu (mh4.mail.rice.edu [127.0.0.1]) (amavis,
	port 10026)
	with ESMTP id dTQglvbaUweS; Fri,  4 Feb 2011 10:27:05 -0600 (CST)
X-SMTP-Auth: no
X-SMTP-Auth: no
Received: from aart.is.rice.edu (aart.is.rice.edu [168.7.56.48])
	by mh4.mail.rice.edu (Postfix) with ESMTP id E97F928F875;
	Fri,  4 Feb 2011 10:27:04 -0600 (CST)
Received: by aart.is.rice.edu (Postfix, from userid 18612)
	id 07E192D5A6; Fri,  4 Feb 2011 10:27:03 -0600 (CST)
Date: Fri, 4 Feb 2011 10:27:02 -0600
From: Kenneth Marshall <ktm@rice.edu>
To: felix <crucialfelix@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Really really slow select count(*)
Message-ID: <20110204162702.GE1261@aart.is.rice.edu>
References: <AANLkTinAJcm5A+i1O_R87aiYyu8N2OWgoaL9RfR=tUR9@mail.gmail.com>
	<4D4C138C.7010304@2ndquadrant.com>
	<AANLkTi=zNn9hUYAFpgMZSFucfFW92WWbRorRZd1h=SQz@mail.gmail.com>
	<AANLkTikp3_mvRx+NVvPEbUDcML15BDFdFfL6wPvRyKmd@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <AANLkTikp3_mvRx+NVvPEbUDcML15BDFdFfL6wPvRyKmd@mail.gmail.com>
User-Agent: Mutt/1.5.16 (2007-06-09)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-4.21 tagged_above=-10 required=5 tests=BAYES_00=-1.9,
	RCVD_IN_DNSWL_MED=-2.3, T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201102/198
X-Sequence-Number: 42336

On Fri, Feb 04, 2011 at 05:20:27PM +0100, felix wrote:
> reply was meant for the list
> 
> ---------- Forwarded message ----------
> From: felix <crucialfelix@gmail.com>
> Date: Fri, Feb 4, 2011 at 4:39 PM
> Subject: Re: [PERFORM] Really really slow select count(*)
> To: Greg Smith <greg@2ndquadrant.com>
> 
> 
> 
> 
> On Fri, Feb 4, 2011 at 3:56 PM, Greg Smith <greg@2ndquadrant.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
> 

As has been suggested, you really need to CLUSTER the table
to remove dead rows. VACUUM will not do that, VACUUM FULL will
but will take a full table lock and then you would need to
REINDEX to fix index bloat. CLUSTER will do this in one shot.
You almost certainly have your free space map way too small,
which is how you bloated in the first place.

Cheers,
Ken