From: Owen Jacobson <ojacobson@osl.com>
To: pgsql-sql@postgresql.org
Subject: Re: **SPAM** Faster count(*)?
Date: Wed, 10 Aug 2005 09:27:23 -0700
Message-ID: <000001c59dc8$641169d0$9b00015a@osl.com> (raw)
In-Reply-To: <21626.1123642154@sss.pgh.pa.us>
Tom Lane wrote:
> dracula007@atlas.cz writes:
> > I believe running count(*) means fulltable scan, and there's no way
> > to do it without it. But what about some "intermediate" table, with
> > the necessary counts?
>
> There's a fairly complete discussion in the PG list archives of a
> reasonably-efficient scheme for maintaining such counts via triggers.
> It wasn't efficient enough that we were willing to impose the overhead
> on every application ... but if you really NEED a fast count(*) you
> could implement it. I'd like to see someone actually do it and put
> up working code on pgfoundry; AFAIK it's only a paper design so far.
>
> If you only want a very-approximate count, the best bet is to rely on
> the planner's estimates, eg
>
> regression=# explain select * from tenk1;
> QUERY PLAN
> -------------------------------------------------------------
> Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244)
> ^^^^^
>
> Current best practice is to run the explain and parse out the "rows"
> figure using a perl (or axe-of-choice) regexp, though we could be
> persuaded to supply a simpler API if there's enough demand for it.
Yick. Ok, given all of that, I've rewritten the trigger in question to fire
on different, indexable criteria (difference between "earliest" and "latest"
rows in the table).
Thanks, everyone.
Owen
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: ojacobson@osl.com
Subject: Re: **SPAM** Faster count(*)?
In-Reply-To: <000001c59dc8$641169d0$9b00015a@osl.com>
* 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