pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcount(*) optimization
5+ messages / 3 participants
[nested] [flat]
* count(*) optimization
@ 2003-01-08 16:04 Nikola Ivacic <nikola@rs-pi.com>
0 siblings, 1 reply; 5+ messages in thread
From: Nikola Ivacic @ 2003-01-08 16:04 UTC (permalink / raw)
To: pgsql-sql
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?
thanks
Nikola
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: count(*) optimization
@ 2003-01-08 16:15 Nikola Ivacic <nikola@rs-pi.com>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 5+ messages in thread
From: Nikola Ivacic @ 2003-01-08 16:15 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-sql
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.
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: count(*) optimization
@ 2003-01-08 16:22 Bruno Wolff III <bruno@wolff.to>
parent: Nikola Ivacic <nikola@rs-pi.com>
0 siblings, 1 reply; 5+ messages in thread
From: Bruno Wolff III @ 2003-01-08 16:22 UTC (permalink / raw)
To: Nikola Ivacic <nikola@rs-pi.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: count(*) optimization
@ 2003-01-08 17:06 Nikola Ivacic <nikola@rs-pi.com>
0 siblings, 1 reply; 5+ messages in thread
From: Nikola Ivacic @ 2003-01-08 17:06 UTC (permalink / raw)
To: Ian Harding <ianh@tpchd.org>; +Cc: pgsql-sql
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)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: count(*) optimization
@ 2003-01-08 17:44 Josh Berkus <josh@agliodbs.com>
parent: Nikola Ivacic <nikola@rs-pi.com>
0 siblings, 0 replies; 5+ messages in thread
From: Josh Berkus @ 2003-01-08 17:44 UTC (permalink / raw)
To: Nikola Ivacic <nikola@rs-pi.com>; Ian Harding <ianh@tpchd.org>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-01-08 17:44 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-08 16:04 count(*) optimization Nikola Ivacic <nikola@rs-pi.com>
2003-01-08 16:22 ` Bruno Wolff III <bruno@wolff.to>
2003-01-08 16:15 ` Nikola Ivacic <nikola@rs-pi.com>
2003-01-08 17:06 Re: count(*) optimization Nikola Ivacic <nikola@rs-pi.com>
2003-01-08 17:44 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox