Re: Much Ado About COUNT(*) - Mailing list pgsql-hackers

From Jonah H. Harris
Subject Re: Much Ado About COUNT(*)
Date
Msg-id 41E6CA04.50708@tvi.edu
Whole thread Raw
In response to Re: Much Ado About COUNT(*)  ("D'Arcy J.M. Cain" <darcy@druid.net>)
List pgsql-hackers
D'Arcy J.M. Cain wrote:

>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.
>
>  
>
This discussion is not solely related to COUNT, but advanced usage of 
the indexes in general.

Did everyone get to read the info on Oracle's fast full index scan?  It 
performs sequential I/O on the indexes, pulling all of the index blocks 
into memory to reduce random I/O to speed up the index scan.


pgsql-hackers by date:

Previous
From: "D'Arcy J.M. Cain"
Date:
Subject: Re: Much Ado About COUNT(*)
Next
From: Hans-Jürgen Schönig
Date:
Subject: some linker troubles with rc5 on sun studio 9 ...