Обсуждение: (some) Indexes ignored after long-running UPDATE and REINDEX at the same time (8.3.6)

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;

This took about 12 hours, during this time our nightly REINDEX script
ran (successfully). After that however, queries completely ignored some
indexes, including newly created ones. I ran "vacuum analyze" on the
table several times, I set "enable_seqscan=false" and it still used
sequential scans for queries like

SELECT * FROM eintrag WHERE h_id = 1234;

while I had a freshly created (just before the query and with an ANALYZE
afterwards) index on the table:

     "e_h" btree (h_id)

I also had an older index

     "e_opt_rejects" btree (h_id, a_id)

which was explicitly reindexed by the above mentioned nightly script and
would have been used normally.

# EXPLAIN SELECT * FROM eintrag WHERE h_id>5227;
                              QUERY PLAN
---------------------------------------------------------------------
  Seq Scan on eintrag  (cost=0.00..1579036.38 rows=7135194 width=463)
    Filter: (h_id > 5227)
(2 rows)

# EXPLAIN SELECT * FROM eintrag WHERE h_id=5227;
                             QUERY PLAN
------------------------------------------------------------------
  Seq Scan on eintrag  (cost=0.00..1579036.38 rows=1642 width=463)
    Filter: (h_id = 5227)
(2 rows)

# EXPLAIN SELECT * FROM eintrag WHERE mtime = 'yesterday';
                                  QUERY PLAN
-----------------------------------------------------------------------------
  Index Scan using e_mtime on eintrag  (cost=0.00..511.80 rows=477
width=528)
    Index Cond: (mtime = '2009-03-07 00:00:00'::timestamp without time zone)
(2 rows)

(the above were with enable_seqscan=true obviously, with it set to false
the cost was artifically high, 1000.... as usually, but I didn't save
the output). Other queries, e.g. using an index over "mtime" still
worked fine. Autovacuum was enabled on the table.

The odd thing is, the problem completely disappeared after I restarted
Postgres. I'm sorry that I cannot provide more details, but I have
absolutely no idea what could have happened here and which system tables
I could have checked for inconsistencies etc. My guess is that the index
"e_opt_rejects" was somehow corrupted during the REINDEX and the some of
the information kept in memory and used by the planner was wrong (the
row estimates for the SELECTs above were quite accurate though).

Regards,
  Marinos

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!

Gregory Stark <stark@enterprisedb.com> writes:
> Marinos Yannikos <mjy@pobox.com> writes:
>> 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):

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

This would only be possible if it were 8.3 and the reindex script used
REINDEX CONCURRENTLY.  Neither fact is in evidence at this point.

            regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Gregory Stark <stark@enterprisedb.com> writes:
>> Marinos Yannikos <mjy@pobox.com> writes:
>>> 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):
>
>> I think you were bitten by a gotcha with newly created indexes and "heap-only"
>> updates.
>
> This would only be possible if it were 8.3 and the reindex script used
> REINDEX CONCURRENTLY.  Neither fact is in evidence at this point.

Well it does say "8.3.6" in the subject.

We don't actually support REINDEX CONCURRENTLY... But if my other post is
right then it would still happen (unnecessarily) if the index was originally
built with REINDEX CONCURRENTLY and then reindexed.

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