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