To: Jeff Frost <jeff@frostconsultingllc.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Index usage problem on 8.3.3 
In-reply-to: <490A56C1.8020804@frostconsultingllc.com> 
References: <490A4EBE.9030804@frostconsultingllc.com>
	<13272.1225413116@sss.pgh.pa.us>
	<490A56C1.8020804@frostconsultingllc.com>
Comments: In-reply-to Jeff Frost <jeff@frostconsultingllc.com>
	message dated "Thu, 30 Oct 2008 17:52:17 -0700"
Date: Thu, 30 Oct 2008 21:14:17 -0400
Message-ID: <14519.1225415657@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Jeff Frost <jeff@frostconsultingllc.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