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 11:42:00 -0500
Message-ID: <26734.1289666520@sss.pgh.pa.us> (raw)
In-Reply-To: <AANLkTi==fxL+FyPSopL4jDQWS6NAgS+ouh5woefHsPY1@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==fxL+FyPSopL4jDQWS6NAgS+ouh5woefHsPY1@mail.gmail.com>

Jon Nelson <jnelson+pgsql@jamponi.net> writes:
> What would the effect be of patching postgresql to allow indexes to
> see and follow the HOT chains during index creation?

It would break things.  We did a *lot* of thinking about this when
HOT was implemented; there are not simple improvements to be made.

The particular case you have here might be improvable because you
actually don't have any indexes at all during the UPDATE, and so
maybe there's no need for it to create HOT-update chains.  But that
would still fall over if you made an index, did the update, then
made more indexes.

> Is this "HOT update" optimization interaction with indexes documented
> anywhere? It doesn't appear to be common knowledge as there are now 20
> messages in this topic and this is the first mention of the HOT
> updates / index interaction.

The reason it wasn't mentioned before was that you kept on not showing
us what you did, and there was no reason for anyone to guess that you
were mixing updates and index creations in a single transaction.  We
have seen people run into this type of issue once or twice since 8.3
came out, but it's sufficiently uncommon that it doesn't spend time at
the front of anybody's mind.

			regards, tom lane



view thread (25+ messages)  latest in thread

Message-ID: <26734.1289666520@sss.pgh.pa.us>
Permalink:  ../26734.1289666520@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/26734.1289666520@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: <26734.1289666520@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