Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id B34A4475E1E for ; Wed, 8 Jan 2003 12:05:48 -0500 (EST) Received: from rs-pi.com (unknown [193.95.196.91]) by postgresql.org (Postfix) with ESMTP id 2A3C6475C8B for ; Wed, 8 Jan 2003 12:05:48 -0500 (EST) Received: from spot (fw.rs-pi.com [193.95.196.154]) by rs-pi.com (Postfix) with SMTP id 301A0AF60; Wed, 8 Jan 2003 18:11:47 +0100 (CET) Message-ID: <02cd01c2b738$39cc0a00$1100a8c0@rspi> From: "Nikola Ivacic" To: "Ian Harding" Cc: References: Subject: Re: count(*) optimization Date: Wed, 8 Jan 2003 18:06:03 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/105 X-Sequence-Number: 10970 I am not sure pgAdmin uses count(*) and I didn't have time to check phpPgAdmin also, I presumed it from similarly slow response I get in psql Nikola ----- Original Message ----- From: "Ian Harding" To: ; Cc: Sent: Wednesday, January 08, 2003 5:20 PM Subject: Re: [SQL] count(*) optimization Bummer. I seem to remember PGAccess using the numbers generated by ANALYZE to show the number of records. I noticed it was inaccurate until you ran a vaccum. I guess pgAdmin chose accuracy over speed in this case. Ian Harding Programmer/Analyst II Tacoma-Pierce County Health Department iharding@tpchd.org (253) 798-3549 >>> "Nikola Ivacic" 01/08/03 08:15AM >>> The problem is in other software I use, witch uses count(*) function to display relation properties pgAdmin II phpPgAdmin Nikola ----- Original Message ----- From: "Bruno Wolff III" To: "Nikola Ivacic" Cc: Sent: Wednesday, January 08, 2003 5:22 PM Subject: Re: [SQL] count(*) optimization > On Wed, Jan 08, 2003 at 17:04:40 +0100, > Nikola Ivacic wrote: > > Hi > > > > I have trouble executing count(*) statement. > > On a large dataset it takes quite a while to return result. > > Is there any other way to get number of tupples in relation > > or is the only way to optimize count() function to index it? > > Indexes won't help count. > > If you retrieve counts a lot more than you do updates that will change their > value, then you may want to keep the counts in another table and use > triggers to keep them updated. > ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)