Re: Very slow update / hash join

On Wed, May 4, 2016 at 3:22 PM, Kurt Roeckx <kurt@roeckx.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