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 A6BC63A4AEA for ; Fri, 19 Nov 2004 13:59:57 +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 09547-08 for ; Fri, 19 Nov 2004 13:59:47 +0000 (GMT) Received: from rproxy.gmail.com (rproxy.gmail.com [64.233.170.194]) by svr1.postgresql.org (Postfix) with ESMTP id 0E09D3A4AF0 for ; Fri, 19 Nov 2004 13:59:50 +0000 (GMT) Received: by rproxy.gmail.com with SMTP id a36so32954rnf for ; Fri, 19 Nov 2004 05:59:50 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type:content-transfer-encoding; b=qtM7sKRN0OTqZ1jCmUbWAT9v/e7zPAY81SNf3rhqzJTnBBVYqf8pArb/Rfsqqp/URHTGK5QvIJqPerMG4hoPr+sq6o3jPss+VMpZSjnKnlZAlMnXiCuITxak7FNO7pB6ElwXYNO9NLStCbgdENEo5BWlLM5oe4Jg3y+y9LN1t1g= Received: by 10.39.3.25 with SMTP id f25mr183344rni; Fri, 19 Nov 2004 05:59:50 -0800 (PST) Received: by 10.38.163.22 with HTTP; Fri, 19 Nov 2004 05:59:48 -0800 (PST) Message-ID: <758d5e7f04111905594cf9c9f8@mail.gmail.com> Date: Fri, 19 Nov 2004 14:59:48 +0100 From: Dawid Kuroczko Reply-To: Dawid Kuroczko To: pgsql-performance@postgresql.org Subject: When to bump up statistics? Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.9 tagged_above=0.0 required=5.0 tests=FROM_ENDS_IN_NUMS X-Spam-Level: X-Archive-Number: 200411/298 X-Sequence-Number: 9252 ALTER TABLE foo ALTER COLUMN bar SET STATISTICS n; ..... 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. I wonder however when it is most uselful to bump it up. Please tell me what you think about it: Is bumping up statistics is only useful for indexed columns? When is it most useful/benefitial to bump them up: 1) huge table with huge number of distinct values (_almost_ unique ;)) 2) huge table with relatively equally distributed values (like each value is in between, say, 30-50 rows). 3) huge table with unequally distributed values (some values are in 1-5 rows, some are in 1000-5000 rows). 4) huge table with small number values (around ~100 distinct values, equally or uneqally distributed). 5) 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?) What are the runtime implications of setting statistics too large -- how much can it affect queries? And finally -- how other RDBMS and RDBM-likes deal with this issue? :) Regards, Dawid