Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 734DF798D53 for ; Tue, 16 Mar 2010 22:14:22 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 68215-02 for ; Wed, 17 Mar 2010 01:14:11 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from qw-out-2122.google.com (qw-out-2122.google.com [74.125.92.26]) by mail.postgresql.org (Postfix) with ESMTP id 07347798F71 for ; Tue, 16 Mar 2010 22:05:02 -0300 (ADT) Received: by qw-out-2122.google.com with SMTP id 8so123609qwh.61 for ; Tue, 16 Mar 2010 18:05:01 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:from:date:message-id :subject:to:content-type; bh=2YAzuQl72SVsSAWZLlu06b9dqLqYsEymWjm5e4/lwFI=; b=Wi+E3nn1mLIfXyGEEg03FE5o+dlna3SeOd2YxB/EVtogXq/vR3FLY5V3P2T5N2E6Rq qA7kx+JqtFRQJhWuh4jt64tLKGomP6c4M7xOgaTf5uoNSPZrE1jP0dwUWpTG93LNGjlD O6zGFPMbrEp0eQZIfM1idqhy+CkQwzME8M+aU= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:from:date:message-id:subject:to:content-type; b=G9NQ+5Vgry//QQUgS2xmQoIVfdWnc/yH+mpo8Ildn+RI+LV/nhZnn/Z9IBwz7c1Dz8 /PpsghvFr3tuJPouA2YrkMSa6SMxY0a1POVfDyhxuB1aO6xv7utGnrDvcwUKT9GUndmu EY24htV5aKKLV4Fq/FjOTIGdQdSTDAZKWDHOQ= MIME-Version: 1.0 Received: by 10.229.97.147 with SMTP id l19mr82307qcn.24.1268787901216; Tue, 16 Mar 2010 18:05:01 -0700 (PDT) From: Rob Wultsch Date: Tue, 16 Mar 2010 18:04:41 -0700 Message-ID: <2c5ef4e31003161804l5e32ceb9v6b7019118f30004d@mail.gmail.com> Subject: Building multiple indexes concurrently To: pgsql-performance@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5 tests=BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201003/187 X-Sequence-Number: 38012 Lets say I have a large table bigTable to which I would like to add two btree indexes. Is there a more efficient way to create indexes than: CREATE INDEX idx_foo on bigTable (foo); CREATE INDEX idx_baz on bigTable (baz); Or CREATE INDEX CONCURRENTLY idx_foo on bigTable (foo); CREATE INDEX CONCURRENTLY idx_baz on bigTable (baz); Are there any particular performance optimizations that would be in play in such a scenario? At a minimum I assume that if both of the commands were started at about the same time they would each scan the table in the same direction and whichever creation was slower would benefit from most of the table data it needed being prepopulated in shared buffers. Is this the case? -- Rob Wultsch wultsch@gmail.com