Re: Very slow update / hash join

On Fri, May 06, 2016 at 11:38:27AM +0200, Kurt Roeckx wrote:
> 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.

I dropped all the index except for the primary key. It was still
as slow when it started, but then I forced the primary key into
the filesystem cache and it seems to be much happier now, average
reading at about 10 MB/s, writing at 30 MB/s.

Kurt