Skip site navigation (1) Skip section navigation (2)

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Bill Kirtley <bill(at)actbluetech(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Use of sequence rather than index scan for one text column on one instance of a database
Date: 2009-09-24 16:26:08
Message-ID: 24790.1253809568@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: keshav upadhyayaDate: 2009-09-24 17:32:54
Subject: Regarding Sequential Scans count increase each time we press refresh .
Previous:From: Dave DutcherDate: 2009-09-24 16:08:15
Subject: Re: High CPU load on Postgres Server during Peak times!!!!

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group