Re: UPDATE runs slow in a transaction

Viktor Rosenfeld <rosenfel(at)informatik(dot)hu-berlin(dot)de> writes:
> Postgres is indeed selecting a bad plan. Turns out that the index I
> created to speed up the UPDATE isn't used inside a transaction block.

I was having a hard time believing that, but just noticed that there is
a case in which it could be expected to happen, in 8.3. The HOT patch
has to prevent use of an index in its creating transaction in some
cases. To quote from README.HOT:

: Practically, we prevent old transactions from using the new index by
: setting pg_index.indcheckxmin to TRUE. Queries are allowed to use such an
: index only after pg_index.xmin is below their TransactionXmin horizon,
: thereby ensuring that any incompatible rows in HOT chains are dead to them.
: (pg_index.xmin will be the XID of the CREATE INDEX transaction. The reason
: for using xmin rather than a normal column is that the regular vacuum
: freezing mechanism will take care of converting xmin to FrozenTransactionId
: before it can wrap around.)
:
: This means in particular that the transaction creating the index will be
: unable to use the index. We alleviate that problem somewhat by not setting
: indcheckxmin unless the table actually contains HOT chains with
: RECENTLY_DEAD members. (In 8.4 we may be able to improve the situation,
: at least for non-serializable transactions, because we expect to be able to
: advance TransactionXmin intratransaction.)

That "alleviation" could mask the behavior in simple test cases, if
you're testing in an otherwise-idle database. But in a real workload
it wouldn't be surprising that a new index would fail to be used
immediately, if it were built on a table that had been recently UPDATEd.

I think also that I tried to duplicate the problem in HEAD rather than
8.3, which means that the TransactionXmin advance code also helped to
keep me from seeing it.

regards, tom lane