[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [GENERAL] more problems with count(*) on large table
From:       "Scott Marlowe" <scott.marlowe () gmail ! com>
Date:       2007-09-29 0:17:15
Message-ID: dcc563d10709281717g2abd67e9o28fe05479a4bfc34 () mail ! gmail ! com
[Download RAW message or body]

On 9/28/07, Mike Charnoky <noky@nextbus.com> wrote:
> Hi,
>
> I am still having problems performing a count(*) on a large table.  This
> is a followup from a recent thread:
>
> http://archives.postgresql.org/pgsql-general/2007-09/msg00561.php
>
> Since the last time these problems happened, we have tweaked some
> postgresql config parameters (fsm, etc).  I also recreated the large
> table, with the assumption it was somehow corrupted.
>
> Now, certain count(*) queries are failing to complete for certain time
> ranges (I killed the query after about 24 hours).  The table is indexed
> on a timestamp field.  Here is one query that hangs:
>
> select count(*) from mytable where evtime between '2007-09-26' and
> '2007-09-27';
>
> However, this query runs successfully and takes 2 minutes:
>
> select count(*) from mytable where evtime between '2007-09-25' and
> '2007-09-26';

I had a similar problem pop up recently in my medium large reporting
database.  I found the problem solved by two things, one was upping
the stats target on the time column to 100, and the other was running
regularly scheduled analyzes on the large reporting table, about once
every 8 hours.

autovacuum works fine otherwise, but was not running analyze often enough

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
[prev in list] [next in list] [prev in thread] [next in thread]