Re: Improving count(*)

Zeugswetter Andreas DCP SD schrieb:
>>>The instant someone touches a block it would no longer be marked as
>>>frozen (vacuum or analyze or other is not required) and count(*)
>
> would
>
>>>visit the tuples in the block making the correct decision at that
>
> time.
>
>>Hmm, so the idea would be that if a block no longer contained any
>
> tuples hidden from any active transaction,
>
>>you could store the count and skip reading that page.
>
>
> I like the approach of informix and maxdb, that can tell the count(*)
> instantly without looking at index leaf or data pages.
>
> Imho we could do that with a central storage of count(*) even with mvcc.
> The idea is a base value for count(*) and corrective values per open
> xid.
> To tell the count you add all corrective values whose xid is visible in
> snapshot.
> Each backend is responsibe for compacting xid counters below min open
> xid.
> Periodically (e.g. at checkpoint time) you compact (aggregate committed
> xid counters
> into the base value) and persist the count.
>
> Since that costs, I guess I would make it optional and combine it with
> materialized
> views that are automatically used at runtime, and can at the same time
> answer other
> aggregates or aggregates for groups.
> create materialized view xx_agg enable query rewrite as select count(*),
> sum (col1) from xx
> [group by col2];
>

I wonder how many times you really need a count(*) w/o where clause.
If I understand you correctly you are trying to optimize just this
one case?

Regards
Tino