Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id 122921337990
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Sun, 14 Nov 2010 11:55:14 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 42628-06
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Sun, 14 Nov 2010 15:55:09 +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 9E59E1336FA7
	for <pgsql-performance@postgresql.org>;
	Sun, 14 Nov 2010 11:55:06 -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 oAEFt599010598;
	Sun, 14 Nov 2010 10:55:05 -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: <AANLkTikeaQ98udD7hvmiHtUJQ9LYR0A9+yfsOucUnBc3@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>
	<26496.1289696046@sss.pgh.pa.us>
	<AANLkTikeaQ98udD7hvmiHtUJQ9LYR0A9+yfsOucUnBc3@mail.gmail.com>
Comments: In-reply-to Robert Haas <robertmhaas@gmail.com>
	message dated "Sat, 13 Nov 2010 21:54:58 -0500"
Date: Sun, 14 Nov 2010 10:55:05 -0500
Message-ID: <10597.1289750105@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/239
X-Sequence-Number: 41263

Robert Haas <robertmhaas@gmail.com> writes:
> Yeah, I'm not familiar with the logic in that area of the code, so I
> can't comment all that intelligently.  However, I feel like there's a
> class of things that could potentially be optimized if we know that
> the only snapshot they could affect is the one we're currently using.

Yeah, perhaps.  The other thing I noticed while looking at the code is
that CREATE INDEX's test to see whether there are broken HOT chains is
borderline brain-dead: if there are any recently-dead HOT-updated tuples
in the table, it assumes they represent broken HOT chains, whether they
really do or not.  In principle you could find the live member of the
chain and see whether or not it is really different from the dead member
in the columns used by the new index.  In Jon's example that would win
because his update didn't actually change the indexed column.  It's
unclear though that it would be useful often enough to be worth the
extra code and cycles.

			regards, tom lane