Re: Block B-Tree concept

On Wed, Sep 27, 2006 at 05:38:38AM -0400, Bruce Momjian wrote:
> Heikki Linnakangas wrote:
> > Jim C. Nasby wrote:
> > > Couldn't vacuum just eliminate tuples marked dead? Heck, don't we do
> > > that anyway right now?
> >
> > You mean _index_ tuples marked dead? Sure, no problem there.
> >
> > > Granted, you'd want to periodically ensure that you scan the entire
> > > index, but that shouldn't be horribly hard to set up.
> >
> > Well, it seems to be. A vacuum can't evaluate index expressions because
> > it's not in a real transaction.
> >
> > The DBA could set up a cron job to do "SELECT * FROM foo WHERE bar > 0"
> > etc. with enable_seqscan=false? That would work, but we can't depend on
> > an additional administrative task like. And we might as well just
> > disable the optimization that's causing us problems.
>
> Why can't the C code just do a full index scan that touches the heap,
> sets those expired bits, and then do a vacuum? My point is that the
> bits can be set outside the normal vacuum process, so you don't have to
> be doing heap lookups from the index inside vacuum.
>
> Assuming the heap is mostly in index order, the full index scan
> shouldn't take much longer than a heap scan, and if the heap doesn't
> match index order, a block index shouldn't be used anyway.

Well, my thought was to have a backend process that would periodically
scan a small section of the index, so that you wouldn't have a
long-running transaction. That could then be followed by a vacuum of
that same section of the index, which would nuke the dead tuple entries.

Though, maybe we wouldn't even need the vacuum step since 8.2 will now
reclaim tuples marked as dead?
--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)