Re: Very slow update / hash join

On Wed, May 4, 2016 at 3:22 PM, Kurt Roeckx <kurt(at)roeckx(dot)be> wrote:
> Hi,
>
> I have an update query that's been running for 48 hours now.
> Since it started it used about 2.5% CPU, and is writing to the
> disk at about 3 MB/s, and reading at about 2 MB/s. It's mostly
> waiting for the disks.

The easiest way to figure out what is going on is to identify the
process, and then trace it with something like:

strace -T -ttt -y -p <PID of process>

That should make it obvious which file it is waiting for IO on. Then
you can look up that relfilenode in pg_class to see what table/index
it is.

> The table itself is 53GB, and the table it updates from is only
> 3.3 GB. There are some index on some of the fields (like the id),
> but none of them are being updated. I tried removing those that
> did get updated but that had little effect. It does have foreign
> keys to other tables, and other tables references it, but none of
> the keys should get updated.

What version of PostgreSQL are you using? Have you tried dropping the
foreign keys?

Cheers,

Jeff