Re: Aggregates and Indexes

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> For Postgres custom aggregates, using a standard index is impossible, for
> reasons I think are obvious.

> That leaves MAX, MIN, and COUNT. All of these aggregates should, in an
> ideal world, be index-responsive for large data sets.

While it's fairly clear how one might use an index for MAX/MIN
(basically, make the optimizer transform it into a SELECT ... ORDER BY
... LIMIT 1 operation, which can then be done with an indexscan),
I really don't see how an index can help for COUNT.

The real problem with COUNT is that any attempt to maintain such a value
on-the-fly creates a single-point bottleneck for all insertions and
deletions on the table. The perspective of most of the developers is
that that cost outweighs any possible savings from having an
instantaneous COUNT operation.

When you add in the issue that under MVCC there isn't a unique COUNT
that's the same for all transactions, it's just not worth thinking
about. (And do I need to point out that with WHERE conditions,
GROUP BY, or a variable COUNT argument, all hope of such optimization
disappears anyway? A global rowcount doesn't help in those cases.)

The MAX/MIN issue will probably be addressed someday, but since there
is a good workaround it's not very high on anyone's TODO queue. We have
many more-pressing problems.

regards, tom lane