Re: Slow count(*) again...

12.10.10 21:58, Tom Lane написав(ла):
>
> I'm less than convinced that that approach will result in a significant
> win.  It's certainly not going to do anything to convert COUNT(*) into
> an O(1) operation, which frankly is what the complainants are expecting.
> There's basically no hope of solving the "PR problem" without somehow
> turning COUNT(*) into a materialized-view reference.  We've discussed
> that in the past, and know how to do it in principle, but the complexity
> and distributed overhead are daunting.
>
>
I've though about "aggregate" indexes, something like
create index index_name on table_name(count(*) group by column1, column2);
OR
create index index_name on table_name(count(*));
for table-wide count

To make it usable one would need:
1) Allow third aggregate function SMERGE that can merge one aggregate
state to another
2) The index should be regular index (e.g. btree) on column1, column2
that for each pair has page list to which it's data may belong (in
past/current running transactions), and aggregate state for each page
that were frozen previously
When index is used, it can use precalculated values for "pages with all
tuples vacuumed" (I suspect this is information from visibility map) and
should do regular calculation for all non-frozen pages with visibility
checks and everything what's needed.
When vacuum processes the page, it should (in sync or async way)
calculate aggregate values for the page.

IMHO Such an indexes would make materialized views/triggers/high level
caches unneeded in most cases.

Best regards, Vitalii Tymchyshyn