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

Re: [HACKERS] Much Ado About COUNT(*)

From: Greg Stark <gsstark(at)mit(dot)edu>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: [HACKERS] Much Ado About COUNT(*)
Date: 2005-01-15 03:42:05
Message-ID: 87y8evtl2q.fsf@stark.xeocode.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
"Frank D. Engel, Jr." <fde101(at)fjrhome(dot)net> writes:

> Yep, that could cause problems.  Okay, now I'm joining the program.
> 
> The only thing I can see that would fix this 
> ...

There are well understood mechanisms to fix this. It's a "SMOP" or "simple
matter of programming". What you would do is insert into a summary table a
record that indicates how many records you've inserted into the master table.
Periodically you have some daemon collect up those records and replace them
with a single record.

But this can be done already by hand and it's not clear having the database do
it automatically is necessarily a good idea. It would impose a cost on every
insert when most of the time it wouldn't be useful.

Moreover this is just a special case of a general problem called "materialized
views". If it were added to the database it would probably be more worthwhile
implementing a more general feature that could handle other aggregate
functions besides count(*) as well as other types of queries besides simple
unqualified aggregates.

-- 
greg


In response to

pgsql-hackers by date

Next:From: Alvaro HerreraDate: 2005-01-15 04:26:07
Subject: Re: sparse (static analyzer) report
Previous:From: Rod TaylorDate: 2005-01-15 03:15:03
Subject: Re: PostgreSQL Specification

pgsql-general by date

Next:From: John DeSoiDate: 2005-01-15 04:43:45
Subject: Re: speaks psql unicode?
Previous:From: Jeff DavisDate: 2005-01-15 03:15:49
Subject: Re: Problems with a trigger

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