Re: Very slow update / hash join

On Thu, May 05, 2016 at 09:32:28PM -0700, Jeff Janes wrote:
> 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.

Thanks for the hint, that I didn't think about it.

So it's busy reading all the index files including the primary
key, and only writing to the table I'm updating.

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

I'm using 9.5.2.

So I think the foreign keys are unrelated now. They all obviously
point to the primary key that's not changing, and it's reading all
the index on the table itself, not those on the other tables.

It's kind of annoying that I would need to drop the indexes that
aren't modified just to run an update query.

Kurt