I'm running 2 tests now, one, where I'm doing the traditional indexing, in sequence. The server isn't doing anything
else,so I should get pretty accurate results.
Test 2 will win all the create index sessions in separate sessions in parallel (echo "create index ..."|psql ... & )
oncethe 'serial build' test is done.
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?
On Apr 9, 2011, at 13:10 , Tom Lane wrote:
> 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
andbuilding 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
