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 D15DCD1B446 for ; Tue, 6 Jan 2004 12:18:53 +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 62220-06 for ; Tue, 6 Jan 2004 08:18:13 -0400 (AST) Received: from druid.net (druid.net [216.126.72.98]) by svr1.postgresql.org (Postfix) with ESMTP id 61406D1B465 for ; Tue, 6 Jan 2004 08:18:04 -0400 (AST) Received: by druid.net (Postfix, from userid 1000) id A26C31A9E; Tue, 6 Jan 2004 07:18:09 -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 07:18:08 -0500 User-Agent: KMail/1.5.4 References: <1073332325.8958.8.camel@jester> <200401061212.21344.shridhar_daithankar@persistent.co.in> In-Reply-To: <200401061212.21344.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: <200401060718.08444.darcy@druid.net> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/64 X-Sequence-Number: 5239 On January 6, 2004 01:42 am, Shridhar Daithankar wrote: > On Tuesday 06 January 2004 01:22, Rod Taylor wrote: > > Anyway, with Rules you can force this: > > > > ON INSERT UPDATE counter SET tablecount = tablecount + 1; > > > > ON DELETE UPDATE counter SET tablecount = tablecount - 1; > > That would generate lot of dead tuples in counter table. How about > > select relpages,reltuples from pg_class where relname=; > > Assuming the stats are recent enough, it would be much faster and > accurate.. Well, I did this: cert=# select relpages,reltuples from pg_class where relname= 'certificate'; relpages | reltuples ----------+------------- 399070 | 2.48587e+07 (1 row) Casting seemed to help: 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. -- 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.