Re: Improving count(*)

On Fri, Nov 18, 2005 at 02:56:52PM -0500, Gregory Maxwell wrote:
> However, some great ideas have been proposed here which would not only
> help in that case but would otherwise be quite useful.
>
> *Inclusion of a 'MVCC inflight' bit in indexes which would allow
> skipping MVCC checks in clumps of an index scan which have no pending
> changes. This would further close the performance gap between PG and
> non-MVCC databases for some workloads.
> *Introduction of high performance table sampling, which would be
> useful in many applications (including counting where there is a where
> clause) as well as for testing and adhoc queries.
> and
> *a estimate_count() that provides the planner estimate, which would
> return right away and provide what is really needed most of the time
> people try to count(*) on a large table.

What about Greg Stark's idea of combining Simon's idea of storing
per-heap-block xmin/xmax with using that information in an index scan?
ISTM that's the best of everything that's been presented: it allows for
faster index scans without adding a lot of visibility overhead to the
index heap, and it also allows VACUUM to hit only pages that need
vacuuming. Presumably this could also be used as the on-disk backing for
the FSM, or it could potentially replace the FSM.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461