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

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