Skip site navigation (1) Skip section navigation (2)

Re: Reducing relation locking overhead

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Jochem van Dieten <jochemd(at)gmail(dot)com>
Cc: Gregory Maxwell <gmaxwell(at)gmail(dot)com>, Greg Stark <gsstark(at)mit(dot)edu>, Simon Riggs <simon(at)2ndquadrant(dot)com>, Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Reducing relation locking overhead
Date: 2005-12-02 23:32:57
Message-ID: 8855.1133566377@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
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

Responses

pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group