Re: Indices for select count(*)?

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> One way to conceptually tackle this count(*) issue would be to create a new
> index type for it. The index type would (logically) just need to implement
> insert and delete operations and keep a running count with a big lock around
> it. Users could then choose to trade off concurrent performance against the
> speed of count() by creating or dropping that index. Implementing that type
> of index might not even be that hard but convincing the planer and executor
> to use it without too many hardcoded cases seems more challenging.

It's not that easy --- in the MVCC world there simply isn't a unique
count that is the right answer for every observer. But the idea of
packaging a count(*) mechanism as an index type seems like it might be
a good one. I don't think the planner objection need be taken too
seriously: we already have a good big wart in there for recognizing
MIN/MAX indexability, and this sort of transformation would fit pretty
naturally with what's already done in planagg.c.

regards, tom lane