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

help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jon Nelson <jnelson+pgsql@jamponi.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: temporary tables, indexes, and query plans
Date: Sat, 13 Nov 2010 10:41:12 -0500
Message-ID: <25639.1289662872@sss.pgh.pa.us> (raw)
In-Reply-To: <AANLkTikS2_v7KGj3yafJ6pjTSLeOTkcaBhg14Am7X_OE@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>

Jon Nelson <jnelson+pgsql@jamponi.net> writes:
> OK. This is a highly distilled example that shows the behavior.

> BEGIN;
> CREATE TEMPORARY TABLE foo AS SELECT x AS A, chr(x % 75 + 32) AS b,
> ''::text AS c from generate_series(1,500) AS x;
> UPDATE foo SET c = 'foo' WHERE b = 'A' ;
> CREATE INDEX foo_b_idx on foo (b);
> [ and the rest of the transaction can't use that index ]

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.

You could avoid this effect either by creating the index before you do
any updates on the table, or by not wrapping the entire process into a
single transaction.

			regards, tom lane



view thread (25+ messages)  latest in thread

Message-ID: <25639.1289662872@sss.pgh.pa.us>
Permalink:  ../25639.1289662872@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/25639.1289662872@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, jnelson+pgsql@jamponi.net
  Subject: Re: temporary tables, indexes, and query plans
  In-Reply-To: <25639.1289662872@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