Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 15F171337B58 for ; Sat, 13 Nov 2010 11:41:20 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 61661-01 for ; Sat, 13 Nov 2010 15:41:13 +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 BA8251337990 for ; Sat, 13 Nov 2010 11:41:12 -0400 (AST) 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 oADFfCvq025640; Sat, 13 Nov 2010 10:41:12 -0500 (EST) To: Jon Nelson cc: pgsql-performance@postgresql.org Subject: Re: temporary tables, indexes, and query plans In-reply-to: References: <4CC864F8.3060801@vmsinfo.com> <7C0800F63CCF4149AC0FC5EE2A041226D52361@sr002-2k3exc.ateb.com> <26190.1288208593@sss.pgh.pa.us> <29004.1288215953@sss.pgh.pa.us> <29671.1288218995@sss.pgh.pa.us> <12105.1288275812@sss.pgh.pa.us> Comments: In-reply-to Jon Nelson message dated "Fri, 12 Nov 2010 21:31:55 -0600" Date: Sat, 13 Nov 2010 10:41:12 -0500 Message-ID: <25639.1289662872@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: 201011/224 X-Sequence-Number: 41248 Jon Nelson writes: > OK. This is a highly distilled example that shows the behavior. > BEGIN; > CREATE TEMPORARY TABLE foo AS SELECT x AS A, chr(x % 75 + 32) AS b, > ''::text AS c from generate_series(1,500) AS x; > UPDATE foo SET c = 'foo' WHERE b = 'A' ; > CREATE INDEX foo_b_idx on foo (b); > [ and the rest of the transaction can't use that index ] OK, this is an artifact of the "HOT update" optimization. Before creating the index, you did updates on the table that would have been executed differently if the index had existed. When the index does get created, its entries for those updates are incomplete, so the index can't be used in transactions that could in principle see the unmodified rows. You could avoid this effect either by creating the index before you do any updates on the table, or by not wrapping the entire process into a single transaction. regards, tom lane