Re: pg_xlog becomes extremely large during CREATE INDEX

On Sat, May 15, 2004 at 12:23:18AM -0400, Tom Lane wrote:

> What I see happening on closer analysis is that btree CREATE INDEX can
> hold "exclusive context lock" on some shared buffers for significant
> periods of time. It tries to write all the levels of the btree in
> parallel, so it is spitting out level-zero pages at a great rate,
> level-one pages at a lesser rate, etc. For a large index there could
> be many btree levels, and pages in the higher levels will be held locked
> in the shared buffer arena for considerable periods.

I'm not sure how btree from-scratch-building work, but could it be made
so that it first build level 0 completely, scanning the heap; then build
level 1 scanning the level 0, and so on?

I also wonder why index creation would write XLog information; it won't
be used to recreate the tree if the machine goes down while the index is
created. If you plan on not using the shared bufmgr and issuing a sync
before comit, you could get rid of logging during build ...

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Investigación es lo que hago cuando no sé lo que estoy haciendo"
(Wernher von Braun)