pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Jon Nelson <jnelson+pgsql@jamponi.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: temporary tables, indexes, and query plans
Date: Sat, 13 Nov 2010 19:54:06 -0500
Message-ID: <26496.1289696046@sss.pgh.pa.us> (raw)
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>

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



view thread (25+ messages)  latest in thread

Message-ID: <26496.1289696046@sss.pgh.pa.us>
Permalink:  ../26496.1289696046@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/26496.1289696046@sss.pgh.pa.us

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, robertmhaas@gmail.com, jnelson+pgsql@jamponi.net
  Subject: Re: temporary tables, indexes, and query plans
  In-Reply-To: <26496.1289696046@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox