Re: Much Ado About COUNT(*)

From: "Jonah H(dot) Harris" <jharris(at)tvi(dot)edu>
To: "D'Arcy J(dot)M(dot) Cain" <darcy(at)druid(dot)net>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgman(at)candle(dot)pha(dot)pa(dot)us, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Much Ado About COUNT(*)
Date: 2005-01-13 19:20:36
Message-ID: 41E6CA04.50708@tvi.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-announce pgsql-hackers pgsql-patches

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.

In response to

Browse pgsql-announce by date

  From Date Subject
Next Message Stuart Bishop 2005-01-16 06:30:11 Re: PostgreSQL 8.0.0 Release Candidate 4
Previous Message D'Arcy J.M. Cain 2005-01-13 18:22:42 Re: Much Ado About COUNT(*)

Browse pgsql-hackers by date

  From Date Subject
Next Message Brad Nicholson 2005-01-13 20:05:18 Port Report: Linux SuSE Enterprise Server 9 (x86_64)
Previous Message Marc G. Fournier 2005-01-13 18:52:40 Re: [HACKERS] Win32 config file extension, capitalization

Browse pgsql-patches by date

  From Date Subject
Next Message David Fetter 2005-01-13 19:30:38 Re: Returning multiple cursors from PL/PgSQL
Previous Message Peter Eisentraut 2005-01-13 19:05:35 Re: Translation updates