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: Jon Nelson <jnelson+pgsql(at)jamponi(dot)net>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: temporary tables, indexes, and query plans
Date: 2010-11-13 16:42:00
Message-ID: 26734.1289666520@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Jon Nelson <jnelson+pgsql(at)jamponi(dot)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

In response to

pgsql-performance by date

Next:From: Marti RaudseppDate: 2010-11-13 17:38:07
Subject: Re: Defaulting wal_sync_method to fdatasync on Linux for 9.1?
Previous:From: bricklenDate: 2010-11-13 16:15:06
Subject: Re: anti-join chosen even when slower than old plan

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