Re: temporary tables, indexes, and query plans - Mailing list pgsql-performance

From Tom Lane
Subject Re: temporary tables, indexes, and query plans
Date
Msg-id 26734.1289666520@sss.pgh.pa.us
Whole thread Raw
In response to Re: temporary tables, indexes, and query plans  (Jon Nelson <jnelson+pgsql@jamponi.net>)
List pgsql-performance
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

pgsql-performance by date:

Previous
From: Jon Nelson
Date:
Subject: Re: temporary tables, indexes, and query plans
Next
From: Marti Raudsepp
Date:
Subject: Re: Defaulting wal_sync_method to fdatasync on Linux for 9.1?