X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 20680D1BA13 for ; Sun, 22 Feb 2004 21:58:33 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 71289-05 for ; Sun, 22 Feb 2004 17:58:33 -0400 (AST) Received: from rwcrmhc11.comcast.net (rwcrmhc11.comcast.net [204.127.198.35]) by svr1.postgresql.org (Postfix) with ESMTP id 2EE13D1BABF for ; Sun, 22 Feb 2004 17:58:31 -0400 (AST) Received: from [10.0.1.2] (h000393e3ce55.ne.client2.attbi.com[24.91.235.158]) by comcast.net (rwcrmhc11) with SMTP id <2004022221583201300ddfgqe>; Sun, 22 Feb 2004 21:58:32 +0000 User-Agent: Microsoft-Entourage/10.1.4.030702.0 Date: Sun, 22 Feb 2004 16:58:30 -0500 Subject: Re: Column correlation drifts, index ignored again From: John Siracusa To: Postgres Performance Message-ID: In-Reply-To: <200402221105.57068.josh@agliodbs.com> Mime-version: 1.0 Reply-To: siracusa@mindspring.com Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/251 X-Sequence-Number: 5775 On 2/22/04 2:05 PM, Josh Berkus wrote: > On Saturday 21 February 2004 16:18, John Siracusa wrote: >> Next, thanks to my earlier thread, I clustered the table on the date >> column and then "SET STATISTICS" on the date column to be 100. That >> did the trick, and I stopped explicitly disabling seqscan. > > 100? Are you sure you don't mean some other number? 100 is not very high > for problem analyze issues. You might try 500. IIRC, 100 was the number suggested in the earlier thread. I did set it to 500 yesterday, I believe. We'll see how that goes. > Generally when I have a problem query I raise stats to something like 1000 and > drop it down until the problem behaviour starts re-appearing. Since this problem takes a long time to appear (months), that cycle could take a long time... :) >> date_trunc('day', date) AS date > > Have you tried putting an index on date_trunc('day', date) and querying on > that instead of using this: > >> date BETWEEN '2004-02-01 00:00:00' AND '2004-02-28 23:59:59' No, but then I'd just have a different index to persuade the planner to use :) Not every query does date_trunc() stuff, but they all do date ranges, often at a granularity of seconds. -John