X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 119FA3A44CC for ; Thu, 18 Nov 2004 00:20:20 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 72656-06 for ; Thu, 18 Nov 2004 00:20:08 +0000 (GMT) Received: from flake.decibel.org (flake.decibel.org [66.143.173.58]) by svr1.postgresql.org (Postfix) with ESMTP id 0FE6A3A4113 for ; Thu, 18 Nov 2004 00:20:10 +0000 (GMT) Received: by flake.decibel.org (Postfix, from userid 1001) id 194CF1C90B; Thu, 18 Nov 2004 00:20:09 +0000 (GMT) Date: Wed, 17 Nov 2004 18:20:09 -0600 From: "Jim C. Nasby" To: Simon Riggs Cc: David Brown , pgsql-performance@postgresql.org Subject: Re: Analyzer is clueless Message-ID: <20041118002008.GL80532@decibel.org> References: <20041117160824.B694D3A3ECC@svr1.postgresql.org> <1100730768.4113.7709.camel@localhost.localdomain> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <1100730768.4113.7709.camel@localhost.localdomain> X-Operating-System: FreeBSD 4.10-RELEASE-p3 i386 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/272 X-Sequence-Number: 9226 On Wed, Nov 17, 2004 at 10:32:48PM +0000, Simon Riggs wrote: > The main issue is that PostgreSQL's default histogram statistics setting > is lower than other RDBMS. This means that it is less able to > discriminate between cases such as yours that are close to the edge. > This is a trade-off between run-time of the ANALYZE command and the > benefit it produces. As Joshua suggests, increasing the statistics > target for this table will likely allow the optimizer to correctly > determine the selectivity of the index and take the right path. Is there still a good reason to have the histogram stats so low? Should the default be changed to more like 100 at this point? Also, how extensively does the planner use n_distinct, null_frac, reltuples and the histogram to see what the odds are of finding a unique value or a low number of values? I've seen cases where it seems the planer doesn't think it'll be getting a unique value or a small set of values even though stats indicates that it should be. One final question... would there be interest in a process that would dynamically update the histogram settings for tables based on how distinct/unique each field was? -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"