From: Tom Lane <tgl@sss.pgh.pa.us>
To: Chris Browne <cbbrowne@acm.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: Make COUNT(*) Faster?
Date: Fri, 08 Jul 2005 00:27:23 -0400
Message-ID: <8508.1120796843@sss.pgh.pa.us> (raw)
In-Reply-To: <60vf3mklue.fsf@dba2.int.libertyrms.com>
References: <9EF2931D-1097-470D-8ECB-59D00166C96F@apple.com>
<60vf3mklue.fsf@dba2.int.libertyrms.com>
Chris Browne <cbbrowne@acm.org> writes:
> vmehta@apple.com (Varun Mehta) writes:
>> If I run an EXPLAIN on this query I can see that it is doing a
>> sequential scan, which seems quite needless, as surely this
>> information is cached in some secret location.
> [ example scenario snipped ]
> If there were some "single secret place" with a count, how would you
> suggest it address those 78 tuples and 16 transactions that aren't yet
> (and maybe never will be) part of the count?
It's worse than that: once some of those transactions have committed,
the right answer is observer-dependent, since some onlooker transactions
may see those guys as committed while others think they are not yet
committed. So there could certainly not be just one secret place...
There are solutions suggested in the archives, but they all amount
to making COUNT(*)-with-no-WHERE-or-GROUP-BY-clause fast at the price
of nontrivial distributed overhead for all updates --- overhead that
would be paid whether or not the application ever did such a COUNT.
That's not a tradeoff we've wanted to make in general. You can
implement it yourself via triggers for specific tables that you think
it's worth doing for.
Also, if an approximate answer is good enough, there are a whole other
set of possible solutions.
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, cbbrowne@acm.org
Subject: Re: Make COUNT(*) Faster?
In-Reply-To: <8508.1120796843@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