Re: Much Ado About COUNT(*)

On Thu, 13 Jan 2005 10:29:16 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Wrong.  The WAL recovery environment is not capable of executing
> arbitrary user-defined functions, therefore it cannot compute index
> entries on its own.  The *only* way we can do this is if the WAL
> record stream tells exactly what to do and which physical tuple to do
> it to.

I'm not sure why everyone wants to push this into the database anyway. 
If I need to know the count of something, I am probably in a better
position to decide what and how than the database can ever do.  For
example, I recently had to track balances for certificates in a database
with 25M certificates with multiple transactions on each.  In this case
it is a SUM() instead of a count but the idea is the same.  We switched
from the deprecated money type to numeric and the calculations started
taking too long for our purposes.  We created a new table to track
balances and created rules to keep it updated.  All the complexity and
extra work is limited to changes to that one table and does exactly what
we need it to do.  It even deals with transactions that get cancelled
but remain in the table.

If you need the count of entire tables, a simple rule on insert and
delete can manage that for you.  A slightly more complicated set of
rules can keep counts based on the value of some field, just like we did
for the certificate ID in the transactions.  Getting the database to
magically track this based on arbitrary business rules is guaranteed to
be complex and still not handle everyone's requirements.

-- 
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.