Marinos Yannikos <m...@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! -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
