Re: temporary tables, indexes, and query plans

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Jon Nelson <jnelson+pgsql(at)jamponi(dot)net>, pgsql-performance(at)postgresql(dot)org
Subject: Re: temporary tables, indexes, and query plans
Date: 2010-11-14 00:54:06
Message-ID: 26496.1289696046@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Robert Haas 2010-11-14 02:54:58 Re: temporary tables, indexes, and query plans
Previous Message Robert Haas 2010-11-14 00:46:05 Re: temporary tables, indexes, and query plans