On 9/28/07, Mike Charnoky <noky@xxxxxxxxxxx> 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
