Re: Use of sequence rather than index scan for one text column on one instance of a database

Hi Tom-

Thanks for the response, but I'm not sure what to do with it.

Are you suggesting we might have some transaction (or residue) that's
hanging around and causing this problem?

We do have transactions that run on the order of a couple minutes at
times. In the past, under heavy db load they have piled up on top of
each other, but as far as I can tell they've finished.

Is there something more I can look at to try and diagnose this? As I
mentioned we do have a workaround of copying the column and building
an index on the new column ... is it time to take that step? And if
so, should we be monitoring for this sort of thing on an ongoing basis?

Are there different options we can pass to CREATE INDEX to build and
index that would be usable? Should we be stopping our application
while applying indices (we don't always) to ensure the db is quiescent
at the time?

Regards,
-Bill Kirtley

On Sep 24, 2009, at 12:26 PM, Tom Lane wrote:

> Bill Kirtley <bill(at)actbluetech(dot)com> 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