X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id A962BD1B43D for ; Mon, 5 Jan 2004 18:17:10 +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 27239-09 for ; Mon, 5 Jan 2004 14:16:20 -0400 (AST) Received: from cayenne.palmdigitalmedia.com (cayenne.palmdigitalmedia.com [63.110.43.230]) by svr1.postgresql.org (Postfix) with SMTP id B4F55D1B444 for ; Mon, 5 Jan 2004 14:16:04 -0400 (AST) Received: (qmail 31174 invoked from network); 5 Jan 2004 18:15:46 -0000 Received: from unknown (HELO ?192.168.124.23?) (63.110.43.226) by 0 with SMTP; 5 Jan 2004 18:15:46 -0000 User-Agent: Microsoft-Entourage/10.1.1.2418 Date: Mon, 05 Jan 2004 13:15:56 -0500 Subject: Re: Use my (date) index, darn it! From: John Siracusa To: Postgres Performance Message-ID: In-Reply-To: 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: 200401/38 X-Sequence-Number: 5213 On 1/5/04 11:45 AM, Christopher Browne wrote: > It sounds to me as though the statistics that are being collected > aren't "good enough." That tends to be a sign that the quantity of > statistics (e.g. - bins in the histogram) are insufficient. > > This would be resolved by changing the number of bins (default of 10) > via "ALTER TABLE FOO ALTER COLUMN BAR SET STATISTICS 100" (or some > other value higher than 10). I did that, but I wasn't sure what value to use and what column to increase. I believe I increased the date column itself to 50 or something, but then I wasn't sure what to do next. I re-analyzed the table with the date column set to 50 but it didn't seem to help, so I resorted to clustering. > Clustering would rearrange the contents of the table, and perhaps make > the histogram 'more representative.' Increasing the "SET STATISTICS" > value will quite likely be even more helpful, and is a lot less > expensive than clustering the table... What column(s) should I increase? Do I have to do anything after increasing the statistics, or do I just wait for the stats collector to do its thing? -John