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 DB9A53A4C6D for ; Fri, 19 Nov 2004 22:12:21 +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 24544-02 for ; Fri, 19 Nov 2004 22:12:11 +0000 (GMT) Received: from davinci.ethosmedia.com (server226.ethosmedia.com [209.128.84.226]) by svr1.postgresql.org (Postfix) with ESMTP id E415A3A3D19 for ; Fri, 19 Nov 2004 20:32:35 +0000 (GMT) Received: from [64.81.245.111] (account josh@agliodbs.com HELO temoku.sf.agliodbs.com) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.1.8) with ESMTP id 6686229; Fri, 19 Nov 2004 12:31:49 -0800 From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: pgsql-performance@postgresql.org, Dawid Kuroczko Subject: Re: When to bump up statistics? Date: Fri, 19 Nov 2004 12:32:59 -0800 User-Agent: KMail/1.6.2 References: <758d5e7f04111905594cf9c9f8@mail.gmail.com> In-Reply-To: <758d5e7f04111905594cf9c9f8@mail.gmail.com> MIME-Version: 1.0 Content-Disposition: inline Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Message-Id: <200411191232.59600.josh@agliodbs.com> 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/302 X-Sequence-Number: 9256 Dawid, > I wonder what are the implications of using this statement, > I know by using, say n=100, ANALYZE will take more time, > pg_statistics will be bigger, planner will take longer time, > on the other hand it will make better decisions... Etc, etc. Yep. And pg_statistics will need to be vacuumed more often. > Is bumping up statistics is only useful for indexed columns? No. It's potentially useful for any queried column. > 1) huge table with huge number of distinct values (_almost_ > unique ;)) Yes. > 2) huge table with relatively equally distributed values > (like each value is in between, say, 30-50 rows). Not usually. > 3) huge table with unequally distributed values (some > values are in 1-5 rows, some are in 1000-5000 rows). Yes. > 4) huge table with small number values (around ~100 > distinct values, equally or uneqally distributed). Not usually, especially if they are equally distributed. > 5) boolean column. Almost never, just as it is seldom useful to index a boolean column. > I think SET STATISTICS 100 is very useful for case with > unequally distributed values, but I wonder what about > the other cases. And as a side note -- what are the > reasonable bounds for statistics (between 10 and 100?) Oh, no, I've used values up to 500 in production, and we've tested up to the max on DBT-3. In my experience, if the default (10) isn't sufficient, you often have to go up to > 250 to get a different plan. > What are the runtime implications of setting statistics > too large -- how much can it affect queries? It won't affect select queries. It will affect ANALYZE time (substantially in the aggregate) and maintenance on the pg_statistics table. > And finally -- how other RDBMS and RDBM-likes deal > with this issue? :) Most don't allow such fine-tuned adjustment. MSSQL, for example, allows only setting it per-table or maybe even database-wide, and on that platform it doesn't seem to have much effect on query plans. Oracle prefers to use HINTS, which are a brute-force method to manage query plans. -- --Josh Josh Berkus Aglio Database Solutions San Francisco