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 <ch...@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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
