Date: Fri, 08 Apr 2005 15:51:02 -0400
From: "Joel Fradkin" <jfradkin@wazagua.com>
Subject: Re: getting count for a specific querry
In-reply-to: <1112989189.4256de0583b33@webmail.telus.net>
To: "'Mischa Sandberg'" <mischa.sandberg@telus.net>,
	"'Scott Marlowe'" <smarlowe@g2switchworks.com>
Cc: <pgsql-sql@postgresql.org>
Message-id: <005901c53c74$4ba5d800$797ba8c0@jfradkin>
MIME-version: 1.0
Content-type: text/plain; charset=us-ascii
Content-transfer-encoding: quoted-printable
Importance: Normal

I will also look at doing it the way you describe, they do have wide
liberty. Thanks so much for the ideas. Sorry I did not do a perusal of =
the
archives first (I normally try that, but think I am brain dead today).

Joel Fradkin
=20
Wazagua, Inc.
2520 Trailmate Dr
Sarasota, Florida 34243
Tel.  941-753-7111 ext 305
=20
jfradkin@wazagua.com
www.wazagua.com
Powered by Wazagua
Providing you with the latest Web-based technology & advanced tools.
C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc
 This email message is for the use of the intended recipient(s) and may
contain confidential and privileged information.  Any unauthorized =
review,
use, disclosure or distribution is prohibited.  If you are not the =
intended
recipient, please contact the sender by reply email and delete and =
destroy
all copies of the original message, including attachments.
=20

=20

-----Original Message-----
From: pgsql-sql-owner@postgresql.org =
[mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Mischa Sandberg
Sent: Friday, April 08, 2005 2:40 PM
To: Scott Marlowe
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] getting count for a specific querry

Quoting Scott Marlowe <smarlowe@g2switchworks.com>:

> On Fri, 2005-04-08 at 12:08, Joel Fradkin wrote:
> > I might have to add a button to do the count on command so they =
don't
get
> > the hit.
> > I would want it to return the count of the condition, not the =
currently
> > displayed number of rows.
>=20
> Judging postgresql on one single data point (count(*) performance) is
> quite unfair.  Unless your system only operates on static data and is
> used to mostly do things like counting, in which case, why are you =
using
> a database?

For the general discussion of slowness of count(*),
and given no entry on the subject in=20
   http://www.postgresql.org/docs/faqs.FAQ.html
... I guess everyone has to be pointed at:
 http://archives.postgresql.org/pgsql-hackers/2005-01/msg00247.php

However, the gist of this person's problem is that an adhoc query,
NOT just a 'select count(*) from table', can take remarkably long.
Again, the problem is that PG can't just scan an index.
----------------------
One workaround for this is to use EXPLAIN.
THIS APPLIES TO CASES WHERE THE USER HAS WIDE LIBERTY IN QUERIES.
It's pointless overhead, otherwise.

default_statistics_target is cranked up to 200 on all such tables,
and pg_autovacuum is running. (If there were anything to improve,
it would be refining the thresholds on this).

If the "(cost...rows=3Dnnnn" string returns a number higher than the=20
QUERY row limit, the user is derailed ("That's not specific enough to =
answer
immediately; do you want an emailed report?").

Otherwise, it runs EXPLAIN ANALYZE, which is still faster than the query
itself.
If the "(actual...rows=3Dnnnn...)" is higher than the RESULT row limit =
(PAGE
limit).

It then runs the query, with the PAGE rows offset and limit --- and =
happily,
practically everything that query needs is now in shared_buffers.
The count from the EXPLAIN analyze is displayed in the web page.

--=20
"Dreams come true, not free." -- S.Sondheim, ITW


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org