Jochem van Dieten <jochemd(at)gmail(dot)com> writes:
> How about the following sceanrio for building a new index:
> - create an empty index
> - flag it as incomplete
> - commit it so it becomes visible to new transactions
> - new transactions will update the index when inserting / updating
> - the planner will not use it for queries because it is flagged as incomplete
> - wait until the the index is visible to all running transactions
> - start a new seqscan and insert all records in the index
> - commit
> - remove the incomplete flag
> Wouldn't this overcome the lock upgrade problem?

Doesn't really solve the problem for REINDEX, though.  Presumably, the
reason that you are REINDEXing is that you would like to defragment the
existing index.  Well, that requires collecting all the index entries
and sorting them.  The above method is not going to produce a nicely
sorted index; whatever entries get made on-the-fly during the first
stage are going to determine the index shape.

This same problem applies to the build-lock-catchup paradigm, although
less severely since you can hope that the entries to be added on at the
end are only a small part of the total and will fit in the excess space
that you leave in the index leaf pages.  If you spend too long catching
up, though (as in the multiple-pass ideas that various people were
advocating), you'll end up with an index messy enough that it's
questionable why you bothered.

			regards, tom lane

In response to

• Re: Reducing relation locking overhead at 2005-12-02 22:41:15 from Jochem van Dieten

Responses

• Re: Reducing relation locking overhead at 2005-12-03 21:45:14 from Jochem van Dieten

pgsql-hackers by date

Next:	From: Tom Lane	Date: 2005-12-02 23:38:01
	Subject: Re: Numeric 508 datatype
Previous:	From: Marc G. Fournier	Date: 2005-12-02 23:19:31
	Subject: Re: Spam 508