Re: (some) Indexes ignored after long-running UPDATE and REINDEX at the same time (8.3.6)

Marinos Yannikos <mjy@pobox.com> writes:

> Hi,
>
> I had a strange problem this morning - I started a long-running UPDATE on a
> heavily indexed table with about 8m rows last night to test a trigger-based
> queue (PgQ):
>
> UPDATE eintrag SET mtime=mtime;

I think you were bitten by a gotcha with newly created indexes and "heap-only"
updates.

If a table has any "heap-only" updates then a newly created index cannot be
used by any queries which come along which need to be able to see older
versions of those records. Once your older transactions had all finished then
the index would have suddenly started being used.

This is not very common in practice because usually index builds take a while
and once they're done any transactions which were started earlier have long
since expired. But if you were running any long-lived transactions at the same
time they could prevent any other transaction from being able to use the index
until they commit (and you start a new transaction to run the query in).

Normally I would not recommend running nightly REINDEXes, though in this case
because you had done a massive UPDATE against the table it was probably
helpful.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's 24x7 Postgres support!