Re: Multiple index builds on same table - in one sweep? - Mailing list pgsql-performance

From Greg Smith
Subject Re: Multiple index builds on same table - in one sweep?
Date
Msg-id 4DA2679D.5000207@2ndQuadrant.com
Whole thread Raw
In response to Re: Multiple index builds on same table - in one sweep?  (Chris Ruprecht <chris@ruprecht.org>)
Responses Re: Multiple index builds on same table - in one sweep?  (Scott Marlowe <scott.marlowe@gmail.com>)
List pgsql-performance
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
buildand 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


pgsql-performance by date:

Previous
From: Tomas Vondra
Date:
Subject: Re: optimizer parameters
Next
From: Greg Smith
Date:
Subject: Re: optimizer parameters