>On Tue, 28 Feb 2006 01:04:00 -0500, Tom Lane wrote
>"Jim C. Nasby" <jnasby ( at ) pervasive ( dot ) com> writes:
>> On Mon, Feb 27, 2006 at 03:05:41PM -0500, Tom Lane wrote:
>> Moreover, you haven't pointed to any strong reason to adopt this
>>> methodology. It'd only be a win when vacuuming pretty small numbers
>>> of tuples, which is not the design center for VACUUM, and isn't likely
>>> to be the case in practice either if you're using autovacuum. If you're
>>> removing say 1% of the tuples, you are likely to be hitting every index
>>> page to do it, meaning that the scan approach will be significantly
>>> *more* efficient than retail lookups.
>> The use case is any large table that sees updates in 'hot spots'.
>> Anything that's based on current time is a likely candidate, since often
>> most activity only concerns the past few days of data.
>I'm unmoved by that argument too. If the updates are clustered then
>another effect kicks in: the existing btbulkdelete approach is able to
>collapse all the deletions on a given index page into one WAL record.
>With retail deletes it'd be difficult if not impossible to do that,
>resulting in a significant increase in WAL traffic during a vacuum.
>(We know it's significant because we saw a good improvement when we
>fixed btbulkdelete to work that way, instead of issuing a separate
>WAL record per deleted index entry as it once did.)
Excuse me for cutting in.
I think it is possible to build a bitmap of _index_ pages dinamicly while scanning table pages.
If we touched more than 1/3(for example) of index pages (for certain index)
then we stop looking tuples in that index and fall back to full index scan for VACUUMing it.
If not, then we can VACUUM only touched pages of this index.
Excuse my English.
pgsql-hackers by date
|Next:||From: Jim C. Nasby||Date: 2006-02-28 16:45:15|
|Subject: Re: temporary indexes|
|Previous:||From: Bruce Momjian||Date: 2006-02-28 16:40:27|
|Subject: Re: [HACKERS] how solve diff of API counstruct_md_array between|