Re: Index vacuum improvements

From: Simon Riggs <simon(at)2ndquadrant(dot)com>
To: Heikki Linnakangas <hlinnaka(at)iki(dot)fi>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Index vacuum improvements
Date: 2006-03-29 21:16:28
Message-ID: 1143666988.32384.331.camel@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Wed, 2006-03-29 at 21:48 +0300, Heikki Linnakangas wrote:

> As we know, index vacuum could be sped up significantly if it didn't
> have
> to lock every page in left to right direction because of the integrity
> issue
> described in nbtree/README. We could then scan the index in physical
> order, and AFAICS combine the btbulkdelete and btvacuumcleanup logic
> to
> just one scan.

First off, we need some good timings that show this effect. I believe
it, but we need some publicly discussable performance test cases to show
the effect and then show how much we've improved upon it, repeatably.

Initially, I'd suggest just trying to improve this situation by
pre-scanning the physical index files into OS filesystem cache (only) -
i.e. dont lock the files at all. That way, all I/O is sequential into
memory and then after that all random I/O will be logical. But it would
*all* need to fit in cache.

We might be able to improve the index FSM allocation algorithm so that
we improve the locality of logically adjacent blocks. That way a larger
than memory index would be able to be read with a limited cache. We
could then replace the full pre-read with just a limited sequential scan
ahead.

Maybe effective_cache_size could be a real parameter after all?

The existing FSM allocation scheme provides this for certain kinds of
tables, but not others.

Best Regards, Simon Riggs

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Jonah H. Harris 2006-03-29 21:20:07 Re: control pg_hba.conf via SQL
Previous Message BERTHOULE Emmanuel 2006-03-29 21:12:57 control pg_hba.conf via SQL