Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 768B4133740B
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Fri,  4 Feb 2011 10:56:22 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 61801-01
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Fri,  4 Feb 2011 14:56:15 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.17.9])
	by mail.postgresql.org (Postfix) with ESMTP id E49C41337BA0
	for <pgsql-performance@postgresql.org>;
	Fri,  4 Feb 2011 10:56:13 -0400 (AST)
Received: from [192.168.1.6] (mail.highperformancepostgresql.com
	[71.179.240.8])
	by mrelayeu.kundenserver.de (node=mreu1) with ESMTP (Nemesis)
	id 0LbvAu-1QSRbd26lw-00jjqL; Fri, 04 Feb 2011 15:56:14 +0100
Message-ID: <4D4C138C.7010304@2ndquadrant.com>
Date: Fri, 04 Feb 2011 09:56:12 -0500
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: felix <crucialfelix@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Really really slow select count(*)
References: <AANLkTinAJcm5A+i1O_R87aiYyu8N2OWgoaL9RfR=tUR9@mail.gmail.com>
In-Reply-To: <AANLkTinAJcm5A+i1O_R87aiYyu8N2OWgoaL9RfR=tUR9@mail.gmail.com>
Content-Type: multipart/alternative;
	boundary="------------070604050009000006020307"
X-Provags-ID: V02:K0:9YyKlyT3S6FvsH8bSsBPKfGIHyD7+4HlGU+iS0B0QsR
	2iaK/hcwMw2B1Rubw8Tb/74uj0Fc0yCAzYl+mpGCRcA8kgVcvp
	6UJSSy7q1owcmyUV+CwVRRDuZAzxjYvxiZZpB9OL7dapSX1MVr
	CS6JdoNB1FuZBQZ/RBuVGfqnZ2ZWWiQ1ong/HfRHq6Oe3DlTsx
	mU/VgLK8YMMXn53KLbgyhOlsFOQ/Oofxofu5WrVpNg=
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001
X-Spam-Level: 
X-Archive-Number: 201102/190
X-Sequence-Number: 42328

This is a multi-part message in MIME format.
--------------070604050009000006020307
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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


--------------070604050009000006020307
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
felix wrote:<br>
<blockquote
 cite="mid:AANLkTinAJcm5A+i1O_R87aiYyu8N2OWgoaL9RfR=tUR9@mail.gmail.com"
 type="cite">
  <div>
  <div>explain analyze select count(*) from fastadder_fastadderstatus;</div>
  <div><br>
  </div>
  <div>Aggregate  (cost=62458.73..62458.74 rows=1 width=0) (actual
time=77130.000..77130.000 rows=1 loops=1)</div>
  <div>   -&gt;  Seq Scan on fastadder_fastadderstatus
 (cost=0.00..61701.18 rows=303018 width=0) (actual
time=50.000..76930.000 rows=302479 loops=1)</div>
  <div> Total runtime: <b>77250.000 ms</b></div>
  <div><br>
  </div>
  </div>
</blockquote>
<br>
PostgreSQL version?  If you're running on 8.3 or earlier, I would be
suspicous that your Free Space Map has been overrun.<br>
<br>
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.<br>
<br>
<pre class="moz-signature" cols="72">-- 
Greg Smith   2ndQuadrant US    <a class="moz-txt-link-abbreviated" href="mailto:greg@2ndQuadrant.com">greg@2ndQuadrant.com</a>   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  <a class="moz-txt-link-abbreviated" href="http://www.2ndQuadrant.us">www.2ndQuadrant.us</a>
"PostgreSQL 9.0 High Performance": <a class="moz-txt-link-freetext" href="http://www.2ndQuadrant.com/books">http://www.2ndQuadrant.com/books</a>
</pre>
</body>
</html>

--------------070604050009000006020307--