Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 7D0A31337B98
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Sat, 13 Nov 2010 20:54:15 -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 65937-04
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Sun, 14 Nov 2010 00:54:08 +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 0C3B41336FA7
	for <pgsql-performance@postgresql.org>;
	Sat, 13 Nov 2010 20:54:07 -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 oAE0s63t026497;
	Sat, 13 Nov 2010 19:54:07 -0500 (EST)
To: Robert Haas <robertmhaas@gmail.com>
cc: Jon Nelson <jnelson+pgsql@jamponi.net>, pgsql-performance@postgresql.org
Subject: Re: temporary tables, indexes, and query plans 
In-reply-to: <AANLkTi=bDh2TWJj-p8MGY=8yKx82YUULGXj1fQH_CMod@mail.gmail.com> 
References: <AANLkTinyFRBAR52m7a43-aUuxpLzwirtp5FWwfOYP2vT@mail.gmail.com>
	<4CC864F8.3060801@vmsinfo.com>
	<AANLkTimLX___=XXUMc86tjaxu7ketDpYr2Yd=YJFw0B6@mail.gmail.com>
	<AANLkTikJg7gpP5LOqe5DBFk6iORUefyjx8oJ9aobqr2G@mail.gmail.com>
	<7C0800F63CCF4149AC0FC5EE2A041226D52361@sr002-2k3exc.ateb.com>
	<AANLkTikJp-geSNQyDwEWDvZfjM0195J7PJN5yuRxuJBt@mail.gmail.com>
	<26190.1288208593@sss.pgh.pa.us>
	<AANLkTing+x8XCwGfzkoWUtoqtBBk0gv9eOVPqQ=O36Dn@mail.gmail.com>
	<29004.1288215953@sss.pgh.pa.us>
	<AANLkTimDd_epkFchAy7+OLHcvyMR9utRbG_Ow5GFJ_n+@mail.gmail.com>
	<29671.1288218995@sss.pgh.pa.us>
	<AANLkTikmp9Xpf6AOLcKKLuv2_1ZE9X=O_5KXaKKnLk76@mail.gmail.com>
	<12105.1288275812@sss.pgh.pa.us>
	<AANLkTikS2_v7KGj3yafJ6pjTSLeOTkcaBhg14Am7X_OE@mail.gmail.com>
	<25639.1289662872@sss.pgh.pa.us>
	<AANLkTi=bDh2TWJj-p8MGY=8yKx82YUULGXj1fQH_CMod@mail.gmail.com>
Comments: In-reply-to Robert Haas <robertmhaas@gmail.com>
	message dated "Sat, 13 Nov 2010 19:46:05 -0500"
Date: Sat, 13 Nov 2010 19:54:06 -0500
Message-ID: <26496.1289696046@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
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/234
X-Sequence-Number: 41258

Robert Haas <robertmhaas@gmail.com> writes:
> On Sat, Nov 13, 2010 at 10:41 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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.

> Is the "in principle" here because there might be an open snapshot
> other than the one under which CREATE INDEX is running, like a cursor?

Well, the test is based on xmin alone, not cmin, so it can't really tell
the difference.  It's unclear that it'd be worth trying.

			regards, tom lane