Re: Spped of max

On Tue, May 14, 2002 at 09:18:43PM -0400, Doug Fields wrote:
> >Since PostgreSQL allows user-defined aggregates, this is somewhat
> >difficult to optimize. No one has yet bothered to create special
> >cases for max(), min() and perhaps count(), although it could
> >probably be done.
>
> Perhaps you can suggest the fastest way of getting a table count, if it is
> not
>
> SELECT COUNT(*) FROM x WHERE ...;

Well, if you have a qualification (a WHERE ... clause), then count()
can be fast: it depends on the number of rows that match the
qualification. I can't see an obvious way to optimize count() on a
large subset of a table.

If you don't have a qualification (i.e. SELECT count(*) FROM x), there
are a couple ways to do it: use triggers to increment/decrement a
counter of the number of rows in the table, create a btree-indexed
SERIAL column and do an ORDER BY serial_column DESC LIMIT 1 on it (note
that this is fragile, your sequence could easily have holes), or if you
only need an approximation, you could use the pg_class attribute
"reltuples" for the OID of your table. My impression is that most
people choose the first method.

Cheers,

Neil

--
Neil Conway <neilconway(at)rogers(dot)com>
PGP Key ID: DB3C29FC