Re: Improving count(*)

Jim C. Nasby wrote:
> 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.

Right, but xmin/xmax is too detailed. We just need a single bit to say
all the rows in the heap page are visible to everyone. Seem my earlier
posting.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073