Re: Very slow update / hash join

On Fri, May 06, 2016 at 10:25:34AM -0700, Jeff Janes wrote:
>
> OK, so it sounds like what is happening is that your update cannot do
> a "Heap-Only Tuple" (HOT) update, because there is not enough room in
> each data page for the new copy of rows being updated. So it is
> forced to put the new copy on a different page, which means it has to
> update all the indexes so they know where to find the new version.

That makes total sense now.

> If this not a one-time event, then one thing you could do is lower the
> table's fillfactor, so that the table is more loosely packed and
> future updates are more likely to be able to do HOT updates. If the
> rows being updated are randomly scattered, it wouldn' take much
> lowering to make this happen (maybe 90). But if the rows being
> updated in a single transaction are co-located with each other, then
> you might have to lower it to below 50 before it would solve the
> problem, which might be a solution worse than the problem. When you
> change the parameter, it won't take full effect until the table has
> been completely rewritten, either due to natural churn, or running a
> VACUUM FULL or CLUSTER.

I will probably want to run this a few times. The data being
updated comes from an external tool and once I add new things or
fix bug in it I would like to update the old rows. It's normally an
insert/select only table.

But there are only about 20M of the 133M current rows (about 15%)
that I'm really interested in. So I guess something like an 85%
fillfactor might actually help.

> If this giant update does not have to occur atomically in order for
> your application to behave correctly, then I would probably break it
> up into a series of smaller transactions. Then you could even run
> them in parallel, which would be a big help if you have a RAID (which
> can efficiently work on multiple random IO read requests in parallel)
> but not help so much if you have a single disk.

I don't care about it being atomic or not. I actually tried to do
it in smaller batches before and I ended up calculating that it
would take 2 weeks to do the update.

Kurt