The following bug has been logged online:
Bug reference: 4276
Logged by: x
Email address: noreply@postgresql.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