Message-ID: <1112989189.4256de0583b33@webmail.telus.net>
Date: Fri,  8 Apr 2005 12:39:49 -0700
From: Mischa Sandberg <mischa.sandberg@telus.net>
To: Scott Marlowe <smarlowe@g2switchworks.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: getting count for a specific querry
References: <000001c53c5d$9f07f0d0$797ba8c0@jfradkin>
	<1112983840.20921.77.camel@state.g2switchworks.com>
In-Reply-To: <1112983840.20921.77.camel@state.g2switchworks.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
User-Agent: Internet Messaging Program (IMP) 3.1-cvs

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.
> 
> 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 
   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=nnnn" string returns a number higher than the 
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=nnnn...)" 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.

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