From: Tom Lane <tgl@sss.pgh.pa.us>
To: Chris Ruprecht <chris@ruprecht.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Multiple index builds on same table - in one sweep?
Date: Sat, 09 Apr 2011 13:10:04 -0400
Message-ID: <4325.1302369004@sss.pgh.pa.us> (raw)
In-Reply-To: <DD8709CB-474F-4002-B555-1ED974371364@ruprecht.org>
References: <DD8709CB-474F-4002-B555-1ED974371364@ruprecht.org>
Chris Ruprecht <chris@ruprecht.org> writes:
> I have a table that I need to rebuild indexes on from time to time (records get loaded before indexes get build).
> To build the indexes, I use 'create index ...', which reads the entire table and builds the index, one at a time.
> I'm wondering if there is a way to build these indexes in parallel while reading the table only once for all indexes and building them all at the same time. Is there an index build tool that I missed somehow, that can do this?
I don't know of any automated tool, but if you launch several CREATE
INDEX operations on the same table at approximately the same time (in
separate sessions), they should share the I/O required to read the
table. (The "synchronized scans" feature guarantees this in recent
PG releases, even if you're not very careful about starting them at
the same time.)
The downside of that is that you need N times the working memory and
you will have N times the subsidiary I/O for sort temp files and writes
to the finished indexes. Depending on the characteristics of your I/O
system it's not hard to imagine this being a net loss ... but it'd be
interesting to experiment.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, chris@ruprecht.org
Subject: Re: Multiple index builds on same table - in one sweep?
In-Reply-To: <4325.1302369004@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox