Re: Multiple index builds on same table - in one sweep?

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 ... & ) once the '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 build and activate them at the same time. Food for thought?

On Apr 9, 2011, at 13:10 , Tom Lane wrote:

> Chris Ruprecht <chris(at)ruprecht(dot)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