From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jeff Frost <jeff@frostconsultingllc.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Index usage problem on 8.3.3
Date: Thu, 30 Oct 2008 21:14:17 -0400
Message-ID: <14519.1225415657@sss.pgh.pa.us> (raw)
In-Reply-To: <490A56C1.8020804@frostconsultingllc.com>
References: <490A4EBE.9030804@frostconsultingllc.com>
<13272.1225413116@sss.pgh.pa.us>
<490A56C1.8020804@frostconsultingllc.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, jeff@frostconsultingllc.com
Subject: Re: Index usage problem on 8.3.3
In-Reply-To: <14519.1225415657@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox