Re: Automatic free space map filling

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Csaba Nagy <nagy(at)ecircle-ag(dot)com>
Cc: Christopher Browne <cbbrowne(at)acm(dot)org>, postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Automatic free space map filling
Date: 2006-03-02 17:56:27
Message-ID: 200603021756.k22HuR604228@candle.pha.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Csaba Nagy wrote:
> > What bothers me about the TODO item is that if we have to sequentially
> > scan indexes, are we really gaining much by not having to sequentially
> > scan the heap? If the heap is large enough to gain from a bitmap, the
> > index is going to be large too. Is disabling per-index cleanout for
> > expression indexes the answer?
>
> I guess you're saying that full index scan should only be done when the
> index is a functional one, and use index lookup for safe indexes ? That
> would be a huge win for most of my vacuum-problematic tables, as I don't
> have any functional indexes. But I guess full index scan would still be
> faster if the percentage of pages changed is more than some threshold.
> On the other hand it would allow very frequent vacuuming even for huge
> tables so that situation should not occur. Autovacuum thresholds could
> be lowered drastically in that case...

Right. Another idea would be to remove the heap space held by expired
rows, but to keep the tid slot in place because it is pointed to by an
index. The index entry could be recycled by a later vacuum index scan,
or if an index lookup finds such an entry. Because of multiple indexes,
I don't think the tid slot can be removed except by sequential index
scans of all indexes.

There is also the concern that updating the single-page bitmap will
cause contention by multiple sessions modifing a table.

I am thinking as long as we have to sequential-scan every index, we
aren't going to improve vacuum performance dramatically.

If the bitmap adds contention, and it is only a marginal improvement, it
might not be a win.

The bitmap can be a win, but I think we have to think more boldly to
ensure it is a win.

--
Bruce Momjian http://candle.pha.pa.us
SRA OSS, Inc. http://www.sraoss.com

+ If your life is a hard drive, Christ can be your backup. +

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Mark Dilger 2006-03-02 18:06:26 Re: [SQL] Interval subtracting
Previous Message Hannu Krosing 2006-03-02 17:50:11 Re: [SQL] Interval subtracting