BUG #4276: Feature request aggregate indexes

The following bug has been logged online:

Bug reference: 4276
Logged by: x
Email address: noreply(at)postgresql(dot)org
PostgreSQL version: n/a
Operating system: n/a
Description: Feature request aggregate indexes
Details:

# CREATE INDEX count_star ON fscrbank (COUNT(*));
ERROR: cannot use aggregate function in index expression

Don't know how the architechture would handle it but this should be
possible.

Initialy the values would be calculated on the existing values.

Inserts, Deletes and updates would be passed through a trigger that appends,
reverses, reverses then appends respectively.

So in this example count finds 12 rows and value is 12.
Insert a new record and COUNT(*) increases value to 13.
Delete two records and COUNT(*) decreases value to 11.
Update a row and COUNT(*) decreases by 1 and increases by 1 leaving 11.

Similarly sum(field).
Initial sum(*) of [3,5,2,1]=11
Insert sum(*) [3], sum becomes 14
Delete sum(*) [5], sum becomes 5
Update sum(*) [2] becomes [7], sum becomes 3 becomes 10

On Tue, 2008-07-01 at 13:27 +0000, x wrote:
> The following bug has been logged online:
>
> Bug reference: 4276
> Logged by: x
> Email address: noreply(at)postgresql(dot)org
> PostgreSQL version: n/a
> Operating system: n/a
> Description: Feature request aggregate indexes
> Details:
>
> # CREATE INDEX count_star ON fscrbank (COUNT(*));
> ERROR: cannot use aggregate function in index expression
>
> Don't know how the architechture would handle it but this should be
> possible.

The developers agree this is a good idea. It is already on the TODO,
described as "materialized views", though I like your name for it also,
since that is the main use case.
http://www.postgresql.org/docs/faqs.TODO.html

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support