From nikola@rs-pi.com Wed Jan  8 16:04:31 2003
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id EFCDA475D70
	for <pgsql-sql@postgresql.org>; Wed,  8 Jan 2003 11:04:31 -0500 (EST)
Received: from rs-pi.com (unknown [193.95.196.91])
	by postgresql.org (Postfix) with ESMTP id 5CABA475CA6
	for <pgsql-sql@postgresql.org>; Wed,  8 Jan 2003 11:04:31 -0500 (EST)
Received: from spot (fw.rs-pi.com [193.95.196.154])
	by rs-pi.com (Postfix) with SMTP id B25F3AF40
	for <pgsql-sql@postgresql.org>; Wed,  8 Jan 2003 17:10:24 +0100 (CET)
Message-ID: <024d01c2b72f$a6a7dcc0$1100a8c0@rspi>
From: "Nikola Ivacic" <nikola@rs-pi.com>
To: <pgsql-sql@postgresql.org>
Subject: count(*) optimization
Date: Wed, 8 Jan 2003 17:04:40 +0100
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_024A_01C2B738.08556160"
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/100
X-Sequence-Number: 10965

This is a multi-part message in MIME format.

------=_NextPart_000_024A_01C2B738.08556160
Content-Type: text/plain;
	charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable

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=20
or is the only way to optimize count() function to index it?=20

thanks

Nikola

------=_NextPart_000_024A_01C2B738.08556160
Content-Type: text/html;
	charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-2">
<META content=3D"MSHTML 6.00.2600.0" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hi</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I have trouble executing count(*)=20
statement.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>On a large dataset it takes quite a while =
to return=20
result.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Is there any other way to get number of tu=
pples in=20
relation </FONT></DIV>
<DIV><FONT face=3DArial size=3D2>or is the only way to&nbsp;optimize count(=
)=20
function to index&nbsp;it?</FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>thanks</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Nikola</FONT></DIV></BODY></HTML>

------=_NextPart_000_024A_01C2B738.08556160--




From nikola@rs-pi.com Wed Jan  8 16:15:28 2003
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 97DE84763FA
	for <PGSQL-sql@postgresql.org>; Wed,  8 Jan 2003 11:15:28 -0500 (EST)
Received: from rs-pi.com (unknown [193.95.196.91])
	by postgresql.org (Postfix) with ESMTP id F0182475E3E
	for <PGSQL-sql@postgresql.org>; Wed,  8 Jan 2003 11:15:13 -0500 (EST)
Received: from spot (fw.rs-pi.com [193.95.196.154])
	by rs-pi.com (Postfix) with SMTP
	id B2CE7AF40; Wed,  8 Jan 2003 17:21:07 +0100 (CET)
Message-ID: <02b301c2b731$25d82d00$1100a8c0@rspi>
From: "Nikola Ivacic" <nikola@rs-pi.com>
To: "Bruno Wolff III" <bruno@wolff.to>
Cc: <PGSQL-sql@postgresql.org>
References: <024d01c2b72f$a6a7dcc0$1100a8c0@rspi>
	<20030108162258.GA9033@wolff.to>
Subject: Re: count(*) optimization
Date: Wed, 8 Jan 2003 17:15:23 +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/102
X-Sequence-Number: 10967

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" <bruno@wolff.to>
To: "Nikola Ivacic" <nikola@rs-pi.com>
Cc: <pgsql-sql@postgresql.org>
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 <nikola@rs-pi.com> 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.
>




From bruno@wolff.to Wed Jan  8 16:12:05 2003
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 1F668475C8B
	for <pgsql-sql@postgresql.org>; Wed,  8 Jan 2003 11:12:05 -0500 (EST)
Received: from wolff.to (wolff.to [66.93.249.74])
	by postgresql.org (Postfix) with SMTP id B21ED476EF2
	for <pgsql-sql@postgresql.org>; Wed,  8 Jan 2003 11:10:56 -0500 (EST)
Received: (qmail 9065 invoked by uid 500); 8 Jan 2003 16:22:58 -0000
Date: Wed, 8 Jan 2003 10:22:58 -0600
From: Bruno Wolff III <bruno@wolff.to>
To: Nikola Ivacic <nikola@rs-pi.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: count(*) optimization
Message-ID: <20030108162258.GA9033@wolff.to>
Mail-Followup-To: Nikola Ivacic <nikola@rs-pi.com>,
	pgsql-sql@postgresql.org
References: <024d01c2b72f$a6a7dcc0$1100a8c0@rspi>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <024d01c2b72f$a6a7dcc0$1100a8c0@rspi>
User-Agent: Mutt/1.3.25i
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200301/101
X-Sequence-Number: 10966

On Wed, Jan 08, 2003 at 17:04:40 +0100,
  Nikola Ivacic <nikola@rs-pi.com> 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.



From nikola@rs-pi.com Wed Jan  8 17:05:48 2003
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id B34A4475E1E
	for <pgsql-sql@postgresql.org>; 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 <pgsql-sql@postgresql.org>; 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" <nikola@rs-pi.com>
To: "Ian Harding" <ianh@tpchd.org>
Cc: <pgsql-sql@postgresql.org>
References: <se1bdfc6.092@mail.tpchd.org>
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" <ianh@tpchd.org>
To: <nikola@rs-pi.com>; <bruno@wolff.to>
Cc: <PGSQL-sql@postgresql.org>
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" <nikola@rs-pi.com> 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" <bruno@wolff.to>
To: "Nikola Ivacic" <nikola@rs-pi.com>
Cc: <pgsql-sql@postgresql.org>
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 <nikola@rs-pi.com> 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)






From josh@agliodbs.com Wed Jan  8 17:44:16 2003
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id AE4A3475E3E
	for <pgsql-sql@postgresql.org>; Wed,  8 Jan 2003 12:44:16 -0500 (EST)
Received: from davinci.ethosmedia.com (unknown [209.10.40.251])
	by postgresql.org (Postfix) with ESMTP id EFD3D475C8B
	for <pgsql-sql@postgresql.org>; Wed,  8 Jan 2003 12:44:15 -0500 (EST)
Received: from [63.195.55.98] (account <josh@agliodbs.com>)
	by davinci.ethosmedia.com (CommuniGate Pro WebUser 4.0.2)
	with HTTP id 2307493; Wed, 08 Jan 2003 09:44:19 -0800
From: "Josh Berkus" <josh@agliodbs.com>
Subject: Re: count(*) optimization
To: "Nikola Ivacic" <nikola@rs-pi.com>,
	"Ian Harding" <ianh@tpchd.org>
Cc: <pgsql-sql@postgresql.org>
X-Mailer: CommuniGate Pro Web Mailer v.4.0.2
Date: Wed, 08 Jan 2003 09:44:19 -0800
Message-ID: <web-2307493@davinci.ethosmedia.com>
In-Reply-To: <02cd01c2b738$39cc0a00$1100a8c0@rspi>
MIME-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200301/108
X-Sequence-Number: 10973

Nikola,

> 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

Well, pgAdmin and phpPgAdmin have their own mailing lists.  I'd suggest
posting there.   Ideally, these programs should have an option that
lets you use the Analyze stats instead of a real count if you want
speed over accuracy.  If nobody requests it, though, neither
development team is likely to consider it.

Of course, it's also possible that these interfaces are slow for lots
of other reasons.  For example, I've found pgAdminII to be sluggish on
a large, busy network because MS-ODBC is quite vulnerable to traffic
conflicts.  And phpPgAdmin, at least several versions ago, had a
tendency to issue a seperate query for each single bit of information,
mySQL-style (they may have fixed this, though).

-Josh Berkus