From: Tom Lane <tgl@sss.pgh.pa.us>
To: dracula007@atlas.cz
Cc: pgsql-sql@postgresql.org
Subject: Re: **SPAM** Faster count(*)?
Date: Tue, 09 Aug 2005 22:49:14 -0400
Message-ID: <21626.1123642154@sss.pgh.pa.us> (raw)
In-Reply-To: <196456155.20050810012958@karneval.cz>
References: <000101c59d33$36a4a560$9b00015a@osl.com>
<196456155.20050810012958@karneval.cz>
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.
regards, tom lane
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: tgl@sss.pgh.pa.us, dracula007@atlas.cz
Subject: Re: **SPAM** Faster count(*)?
In-Reply-To: <21626.1123642154@sss.pgh.pa.us>
* 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