Skip site navigation (1) Skip section navigation (2)

Re: Much Ado About COUNT(*)

From: "D'Arcy J(dot)M(dot) Cain" <darcy(at)druid(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgman(at)candle(dot)pha(dot)pa(dot)us, jharris(at)tvi(dot)edu,pgsql-hackers(at)postgresql(dot)org
Subject: Re: Much Ado About COUNT(*)
Date: 2005-01-13 18:22:42
Message-ID: 20050113132242.7efe5252.darcy@druid.net (view raw or flat)
Thread:
Lists: pgsql-announcepgsql-hackerspgsql-patches
On Thu, 13 Jan 2005 10:29:16 -0500
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)druid(dot)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.

In response to

Responses

pgsql-announce by date

Next:From: Jonah H. HarrisDate: 2005-01-13 19:20:36
Subject: Re: Much Ado About COUNT(*)
Previous:From: Tom LaneDate: 2005-01-13 15:29:16
Subject: Re: Much Ado About COUNT(*)

pgsql-hackers by date

Next:From: Palle GirgensohnDate: 2005-01-13 18:44:44
Subject: Bug? 8.0 does not use partial index
Previous:From: Hans-Jürgen SchönigDate: 2005-01-13 17:58:31
Subject: some linker troubles with rc5 on sun studio 9 ...

pgsql-patches by date

Next:From: Tom LaneDate: 2005-01-13 18:44:58
Subject: Re: Returning multiple cursors from PL/PgSQL
Previous:From: David FetterDate: 2005-01-13 17:26:57
Subject: Re: Returning multiple cursors from PL/PgSQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group