Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group