Message-ID: <4DA2B0A4.6050605@2ndquadrant.com>
Date: Mon, 11 Apr 2011 03:41:24 -0400
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: Scott Marlowe <scott.marlowe@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Multiple index builds on same table - in one sweep?
References: <DD8709CB-474F-4002-B555-1ED974371364@ruprecht.org>
	<4325.1302369004@sss.pgh.pa.us>
	<DBEFE6AF-E8CE-4508-A8C3-A0E5E0987EFE@ruprecht.org>
	<4DA2679D.5000207@2ndQuadrant.com>
	<BANLkTinTWXOKd6QunwBag1AJypYTVjYxRw@mail.gmail.com>
In-Reply-To: <BANLkTinTWXOKd6QunwBag1AJypYTVjYxRw@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Scott Marlowe wrote:
> FYI, in 8.3.13 I get this for all but one index:
>
> ERROR:  deadlock detected
> DETAIL:  Process 24488 waits for ShareLock on virtual transaction
> 64/825033; blocked by process 27505.
> Process 27505 waits for ShareUpdateExclusiveLock on relation 297369165
> of database 278059474; blocked by process 24488.
>   

Is that trying to build them by hand?  The upthread request here is 
actually already on the TODO list at 
http://wiki.postgresql.org/wiki/Todo and it talks a bit about what works 
and what doesn't right now:

Allow multiple indexes to be created concurrently, ideally via a single 
heap scan
-pg_restore allows parallel index builds, but it is done via 
subprocesses, and there is no SQL interface for this.

This whole idea was all the rage on these lists circa early 2008, but 
parallel restore seems to have satisfied enough of the demand in this 
general area that it doesn't come up quite as much now.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books