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 B8113D1B4C2 for ; Tue, 6 Jan 2004 23:20:49 +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 02715-03 for ; Tue, 6 Jan 2004 19:20:03 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 56807D1B438 for ; Tue, 6 Jan 2004 19:20:00 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id i06NJt19002699; Tue, 6 Jan 2004 18:19:56 -0500 (EST) To: "D'Arcy J.M. Cain" Cc: Shridhar Daithankar , Postgresql Performance Subject: Re: Select max(foo) and select count(*) optimization In-reply-to: <200401061757.05278.darcy@druid.net> References: <200401060718.08444.darcy@druid.net> <200401061750.09530.shridhar_daithankar@persistent.co.in> <200401061757.05278.darcy@druid.net> Comments: In-reply-to "D'Arcy J.M. Cain" message dated "Tue, 06 Jan 2004 17:57:05 -0500" Date: Tue, 06 Jan 2004 18:19:55 -0500 Message-ID: <2698.1073431195@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/78 X-Sequence-Number: 5253 "D'Arcy J.M. Cain" writes: > In any case, if I have to vacuum a 20,000,000 row table to get an accurate > count then I may as well run count(*) on it. > (*): Actually I only analyze but I understand that that should be sufficient. ANALYZE without VACUUM will deliver a not-very-accurate estimate, since it only looks at a sample of the table's pages and doesn't grovel through every one. Any of the VACUUM variants, on the other hand, will set pg_class.reltuples reasonably accurately (as the number of rows actually seen and left undeleted by the VACUUM pass). There are pathological cases where ANALYZE's estimate of the overall row count can be horribly bad --- mainly, when the early pages of the table are empty or nearly so, but there are well-filled pages out near the end. I have a TODO item to try to make ANALYZE less prone to getting fooled that way... regards, tom lane