Message-ID: <4DA2679D.5000207@2ndQuadrant.com>
Date: Sun, 10 Apr 2011 22:29:49 -0400
From: Greg Smith <greg@2ndQuadrant.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.1.16) Gecko/20110307 Icedove/3.0.11
MIME-Version: 1.0
To: 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>
In-Reply-To: <DBEFE6AF-E8CE-4508-A8C3-A0E5E0987EFE@ruprecht.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 04/09/2011 01:23 PM, Chris Ruprecht wrote:
> Maybe, in a future release, somebody will develop something that can create indexes as inactive and have a build tool build and activate them at the same time. Food for thought?
>    

Well, the most common case where this sort of thing happens is when 
people are using pg_restore to load a dump of an entire database.  In 
that case, you can use "-j" to run more than one loader job in parallel, 
which can easily end up doing a bunch of index builds at once, 
particularly at the end.  That already works about as well as it can 
because of the synchronized scan feature Tom mentioned.

I doubt you'll ever get much traction arguing for something other than 
continuing to accelerate that path; correspondingly, making your own 
index builds look as much like it as possible is a good practice.  Fire 
up as many builds as you can stand in parallel and see how many you can 
take given the indexes+data involved.  It's not clear to me how a create 
as inactive strategy could improve on that.

There are some types of index build operations that bottleneck on CPU 
operations, and executing several of those in parallel can be a win.  At 
some point you run out of physical I/O, or the additional memory you're 
using starts taking away too much from caching.  Once you're at that 
point, it's better to build the indexes on another pass, even if it 
requires re-scanning the table data to do it.  The tipping point varies 
based on both system and workload, it's very hard to predict or automate.

-- 
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