From: John A Meinel <john@arbash-meinel.com>
To: Patrick Hatcher <pathat@comcast.net>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Slow update statement
Date: Sat, 06 Aug 2005 08:34:25 -0500
Message-ID: <42F4BC61.20102@arbash-meinel.com> (raw)
In-Reply-To: <42F4B812.7070501@comcast.net>
References: <42F4B812.7070501@comcast.net>
Patrick Hatcher wrote:
> [Reposted from General section with updated information]
> Pg 7.4.5
>
> I'm running an update statement on about 12 million records using the
> following query:
>
> Update table_A
> set F1 = b.new_data
> from table_B b
> where b.keyfield = table_A.keyfield
>
> both keyfields are indexed, all other keys in table_A were dropped, yet
> this job has been running over 15 hours. Is
> this normal?
Can you do an EXPLAIN UPDATE so that we can have an idea what the
planner is trying to do?
My personal concern is if it doing something like pulling in all rows
from b, and then one by one updating table_A, but as it is going, it
can't retire any dead rows, because you are still in a transaction. So
you are getting a lot of old rows, which it has to pull in to realize it
was old.
How many rows are in table_B?
I can see that possibly doing it in smaller chunks might be faster, as
would inserting into another table. But I would do more of a test and
see what happens.
John
=:->
>
> I stopped the process the first time after 3 hours of running due to
> excessive log rotation and reset the conf file to these settings:
>
>
> wal_buffers = 64 # min 4, 8KB each
>
> # - Checkpoints -
>
> checkpoint_segments = 128 # in logfile segments, min 1, 16MB each
> checkpoint_timeout = 1800 # range 30-3600, in seconds
> #checkpoint_warning = 30 # 0 is off, in seconds
> #commit_delay = 0 # range 0-100000, in microseconds
> #commit_siblings = 5 # range 1-1000
>
>
> Would it just be quicker to run a JOIN statement to a temp file and then
> reinsert?
> TIA Patrick
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
Attachments:
signature.asc (application/pgp-signature, 252B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: john@arbash-meinel.com, pathat@comcast.net
Subject: Re: Slow update statement
In-Reply-To: <42F4BC61.20102@arbash-meinel.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox