From: Greg Smith <greg@2ndquadrant.com>
To: felix <crucialfelix@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Really really slow select count(*)
Date: Fri, 04 Feb 2011 09:56:12 -0500
Message-ID: <4D4C138C.7010304@2ndquadrant.com> (raw)
In-Reply-To: <AANLkTinAJcm5A+i1O_R87aiYyu8N2OWgoaL9RfR=tUR9@mail.gmail.com>
References: <AANLkTinAJcm5A+i1O_R87aiYyu8N2OWgoaL9RfR=tUR9@mail.gmail.com>
felix wrote:
> explain analyze select count(*) from fastadder_fastadderstatus;
>
> Aggregate (cost=62458.73..62458.74 rows=1 width=0) (actual
> time=77130.000..77130.000 rows=1 loops=1)
> -> Seq Scan on fastadder_fastadderstatus (cost=0.00..61701.18
> rows=303018 width=0) (actual time=50.000..76930.000 rows=302479 loops=1)
> Total runtime: *77250.000 ms*
>
PostgreSQL version? If you're running on 8.3 or earlier, I would be
suspicous that your Free Space Map has been overrun.
What you are seeing is that the table itself is much larger on disk than
it's supposed to be. 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. The best way to fix
all this is to run CLUSTER on the table. 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@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: greg@2ndquadrant.com, crucialfelix@gmail.com
Subject: Re: Really really slow select count(*)
In-Reply-To: <4D4C138C.7010304@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox