From: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
---|---|
To: | Kurt Roeckx <kurt(at)roeckx(dot)be> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Very slow update / hash join |
Date: | 2016-05-06 04:32:28 |
Message-ID: | CAMkU=1x=tz10a1KDW=LUQUKX+k6LFU8Nn4JhrCB7g+tkasOtNw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Arjen Nienhuis | 2016-05-06 08:23:46 | Re: xml-file as foreign table? |
Previous Message | rob stone | 2016-05-06 02:29:13 | Re: Debian and Postgres |