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

Re: Much Ado About COUNT(*)

From: "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com>
To: "Jonah H(dot) Harris" <jharris(at)tvi(dot)edu>
Cc: <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Much Ado About COUNT(*)
Date: 2005-01-12 18:53:51
Message-ID: 6EE64EF3AB31D5448D0007DD34EEB3412A75AA@Herge.rcsinc.local (view raw, whole thread or download thread mbox)
Lists: pgsql-generalpgsql-hackers
> Tom, Bruce, and others involved in this recurring TODO discussion...
> First, let me start by saying that I understand this has been
> many times before; however, I'd like to see what the current state of
> affairs is regarding the possibility of using a unique index scan to
> speed up the COUNT aggregate.

To sum up:
1.  There are good technical reasons why not to do this.  The pg
aggregate system is very elegant...not worth compromising it for a
specific case.
2.  postgresql can do many things faster than oracle.  If you prefer the
way oracle behaves, use oracle.
3.  workaround #1: just run analyze once in a while (you should do that
anyways) and query pg_Class for the #tuples in a relation.
4.  workaround #2: rig up a materialized view and query that.  This will
be faster than what oracle does, btw, at the price of some coherency.
5.  understand that count(*) from t, although frequently used, is of
dubious value in the general sense.  Sooner or later someone will
optimize this, but in light of the currently available workarounds it
doesn't seem that important.
6.  for large tables, you can get a pretty accurate count by doing:
select count(*) * 10 from t where random() > .9;
on my setup, this shaved about 15% off of the counting time...YMMV.



pgsql-hackers by date

Next:From: Jonah H. HarrisDate: 2005-01-12 18:55:29
Subject: Re: Much Ado About COUNT(*)
Previous:From: Reinhard MaxDate: 2005-01-12 18:36:52
Subject: segfault caused by heimdal (was: SUSE port)

pgsql-general by date

Next:From: Terry Lee TuckerDate: 2005-01-12 20:01:10
Subject: Re: Interval Question
Previous:From: Ed L.Date: 2005-01-12 18:49:12
Subject: Re: vacuum vs open transactions

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