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@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073