pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
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
Date: Fri, 8 Apr 2005 12:39:49 -0700
Message-ID: <1112989189.4256de0583b33@webmail.telus.net> (raw)
In-Reply-To: <1112983840.20921.77.camel@state.g2switchworks.com>
References: <000001c53c5d$9f07f0d0$797ba8c0@jfradkin>
<1112983840.20921.77.camel@state.g2switchworks.com>
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
view thread (38+ messages) latest in thread
Message-ID: <1112989189.4256de0583b33@webmail.telus.net> Permalink: ../1112989189.4256de0583b33@webmail.telus.net/ Also on: postgresql.org/message-id/1112989189.4256de0583b33@webmail.telus.net ·
reply
Reply instructions: You may reply publicly to this message via plain-text email using any one of the following methods: * Reply to all the recipients using the --to and --cc options: reply via email To: pgsql-sql@postgresql.org Cc: mischa.sandberg@telus.net, smarlowe@g2switchworks.com Subject: Re: getting count for a specific querry In-Reply-To: <1112989189.4256de0583b33@webmail.telus.net> * Save the following mbox file, import it into your mail client, and reply-to-all from there: mbox
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