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

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 (view raw or flat)
Thread:
Lists: pgsql-announcepgsql-hackerspgsql-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

pgsql-announce by date

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

pgsql-hackers by date

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

pgsql-patches by date

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

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