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>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Importance: Normal
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