Re: why does count take so long?

The problem with that approach is that while there may only be one or two
tables that you want to do max(field) on, it would require that all tables
be kept track of, increasing overhead. Keep in mind, select id from table
order by id desc limit 1 is a snap, hits the indexes, and runs fast.

also, I don't think postgresql keeps "live" statistics, only "dead" ones
i.e. they are only gathered by running analyze. If you just need an
approximate account, you can already get that from the statistics table.

Now, if there were a simple way of assigning such behaviour to a table,
(i.e. with count or something like that) via a trigger, that might be
worth looking into, but keep in mind, it creates a serious bottleneck
during inserts/updates/deletes.

On Tue, 9 Sep 2003, Jean-Luc Lachance wrote:

> How about keeping counts of inserts, deletes and updates per table per
> transaction as part of the live statistics?
>
>
>
> Tom Lane wrote:
> >
> > I said:
> > > Greg Stark <gsstark(at)mit(dot)edu> writes:
> > >> Things like count(*) could use int4 until it overflows though.
> >
> > > I don't see a reasonable way for an aggregate to change state datatype
> > > on the fly; otherwise this would be a great solution.
> >
> > On the other hand, the cost is imposed by the generic aggregate
> > definition that says the aggregate state transition function is an
> > ordinary function. This is fine for user-defined aggregates, but there
> > is no law that says that all the built-in aggregates must use that same
> > API. We could probably invent some API that allows COUNT(*) to keep its
> > running count someplace where it needn't be re-palloc'd on every cycle.
> > Something to think about for 7.5 (too late for 7.4 I fear).
> >
> > regards, tom lane
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>