Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 9989C1337B61 for ; Sat, 9 Apr 2011 14:10:13 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 92942-01 for ; Sat, 9 Apr 2011 17:10:05 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 90065133616B for ; Sat, 9 Apr 2011 14:10:05 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id p39HA4jO004326; Sat, 9 Apr 2011 13:10:04 -0400 (EDT) To: Chris Ruprecht cc: pgsql-performance@postgresql.org Subject: Re: Multiple index builds on same table - in one sweep? In-reply-to: References: Comments: In-reply-to Chris Ruprecht message dated "Sat, 09 Apr 2011 12:28:21 -0400" Date: Sat, 09 Apr 2011 13:10:04 -0400 Message-ID: <4325.1302369004@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201104/95 X-Sequence-Number: 43157 Chris Ruprecht 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