Hi,
probably it's just a stupid idea, but what do you think of this:currently,
most if not all queries with aggregates (count(), sum()....) make seq scans
when there are no conditions at all. especially count() is a classic question
on any postgres list.
the reason is - at least that's what I got from this list - is the
multi-version system postgres is using. Tom Lane once said that resolving
this to use index scans would mean adding 8 bytes to every index tuples,
which is no way to go. but what if 1 bit could be added to index items,
indication that the heap tuples MAY be modified?
in any of my database applications updated tuples in the tables are seldom,
because of vacuum. I've a table containing 10 million of tuples, and a
count(1) on them takes several minutes. however, the table is quite static
and changes seldom, and only a few items change.
so an extra bit in the index could indicate: this is a candiate, but the heap
must be checked. in most of my applications this would mean that for 95% of
all tuples the index can be used for aggregates, only the remaining 5% of all
tuples that MIGHT be modified or deleted have to be checked via heap.
and even those 5% are often too much, in my case it's typically below 1%.
The statistic collector could even tell the optimizer if an index scan is the
way to go...
Would that work?
Best regards,Mario Weilguni