Bill Kirtley <bill@xxxxxxxxxxxxxxx> writes: > select xmin,* from pg_index where indexrelid = > 'index_users_on_email'::regclass; > xmin | indexrelid | indrelid | indnatts | indisunique | > indisprimary | indisclustered | indisvalid | indcheckxmin | indisready > | indkey | indclass | indoption | indexprs | indpred > ----------+------------+----------+----------+------------- > +--------------+----------------+------------+-------------- > +------------+--------+----------+-----------+----------+--------- > 12651453 | 24483560 | 17516 | 1 | t | > f | f | t | t | t > | 6 | 10042 | 0 | | > (1 row) Okay, the basic cause of the issue is now clear: the index has indcheckxmin true, which means it's not usable until local TransactionXmin exceeds the tuple's xmin (12651453 here). This is all a pretty unsurprising consequence of the HOT optimizations added in 8.3. The question is why that state persisted long enough to be a problem. Perhaps you have long-running background transactions? TransactionXmin is normally the oldest XID that was running when your own transaction started, so basically the index isn't usable until all transactions that were running while it was built complete. I had been thinking that this only happened for concurrent index builds, but actually regular builds can be subject to it as well. We've seen some complaints about this behavior before. I wonder if there's a way to work a bit harder to avoid the indcheckxmin labeling --- right now the code is pretty conservative about setting that bit if there's any chance at all of an invalid HOT chain. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
