Jeff Frost <jeff@xxxxxxxxxxxxxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
