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

Re: Index usage problem on 8.3.3

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Jeff Frost <jeff(at)frostconsultingllc(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Index usage problem on 8.3.3
Date: 2008-10-31 01:14:17
Message-ID: 14519.1225415657@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Jeff Frost <jeff(at)frostconsultingllc(dot)com> writes:
> Tom Lane wrote:
>> This may be a HOT side-effect ... is pg_index.indcheckxmin set for
>> the index?
>> 
> Yep, sure enough, the 'act' table's indexes have it set and jefftest and
> jefftest2's indexes do not.

Okay.  What that means is that the indexes were created on data that had
already been inserted and updated to some extent, resulting in
HOT-update chains that turned out to be illegal for the new indexes.
The way we deal with this is to mark the indexes as not usable by any
query that can still see the dead HOT-updated tuples.

Your best bet for dodging the problem is probably to break the operation
into two transactions, if that's possible.  INSERT and UPDATE in the
first xact, create the indexes at the start of the second.  (Hmm ...
I'm not sure if that's sufficient if there are other concurrent
transactions; but it's certainly necessary.)  Another possibility is
to create the indexes just after data load, before you start updating
the columns they're on.

			regards, tom lane

In response to

Responses

pgsql-performance by date

Next:From: Jeff FrostDate: 2008-10-31 01:22:19
Subject: Re: Index usage problem on 8.3.3
Previous:From: Jeff FrostDate: 2008-10-31 00:52:17
Subject: Re: Index usage problem on 8.3.3

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