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 88D22D1B46E for ; Tue, 6 Jan 2004 22:57: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 93225-06 for ; Tue, 6 Jan 2004 18:57:01 -0400 (AST) Received: from druid.net (druid.net [216.126.72.98]) by svr1.postgresql.org (Postfix) with ESMTP id 142B1D1B433 for ; Tue, 6 Jan 2004 18:56:59 -0400 (AST) Received: by druid.net (Postfix, from userid 1000) id 661BE1A92; Tue, 6 Jan 2004 17:57:07 -0500 (EST) From: "D'Arcy J.M. Cain" To: Shridhar Daithankar , Postgresql Performance Subject: Re: Select max(foo) and select count(*) optimization Date: Tue, 6 Jan 2004 17:57:05 -0500 User-Agent: KMail/1.5.4 References: <200401060718.08444.darcy@druid.net> <200401061750.09530.shridhar_daithankar@persistent.co.in> In-Reply-To: <200401061750.09530.shridhar_daithankar@persistent.co.in> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200401061757.05278.darcy@druid.net> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/76 X-Sequence-Number: 5251 On January 6, 2004 07:20 am, Shridhar Daithankar wrote: > On Tuesday 06 January 2004 17:48, D'Arcy J.M. Cain wrote: > > On January 6, 2004 01:42 am, Shridhar Daithankar wrote: > > cert=# select relpages,reltuples::bigint from pg_class where relname= > > 'certificate'; > > relpages | reltuples > > ----------+----------- > > 399070 | 24858736 > > (1 row) > > > > But: > > > > cert=# select count(*) from certificate; > > [*Crunch* *Crunch* *Crunch*] > > count > > ---------- > > 19684668 > > (1 row) > > > > Am I missing something? Max certificate_id is 20569544 btw. > > Do 'vacuum analyze certificate' and try..:-) Kind of invalidates the part about being accurate then, don't it? Besides, I vacuum that table every day (*) and we have reorganized the schema so that we never update it except in exceptional cases. I would be less surprised if the result was less than the real count since we only insert into that table. 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. -- D'Arcy J.M. Cain | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.