From: Patrick Hatcher <pathat@comcast.net>
To: John A Meinel <john@arbash-meinel.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Slow update statement
Date: Sun, 07 Aug 2005 19:00:51 -0700
Message-ID: <42F6BCD3.4070600@comcast.net> (raw)
In-Reply-To: <42F4BC61.20102@arbash-meinel.com>
References: <42F4B812.7070501@comcast.net>
<42F4BC61.20102@arbash-meinel.com>
Sorry went out of town for the weekend. The update did occur, but I
have no idea when it finished.
Here's the actual query and the explain Update:
cdm.bcp_ddw_ck_cus = 12.7 M
cdm.cdm_ddw_customer = 12.8M
explain
update cdm.cdm_ddw_customer
set indiv_fkey = b.indiv_fkey
from cdm.bcp_ddw_ck_cus b
where
cdm.cdm_ddw_customer.cus_nbr = b.cus_num;
Hash Join (cost=1246688.42..4127248.31 rows=12702676 width=200)
Hash Cond: ("outer".cus_num = "inner".cus_nbr)
-> Seq Scan on bcp_ddw_ck_cus b (cost=0.00..195690.76 rows=12702676
width=16)
-> Hash (cost=874854.34..874854.34 rows=12880834 width=192)
-> Seq Scan on cdm_ddw_customer (cost=0.00..874854.34
rows=12880834 width=192)
John A Meinel wrote:
>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
>>
>>
>>
>
>
>
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: pathat@comcast.net, john@arbash-meinel.com
Subject: Re: Slow update statement
In-Reply-To: <42F6BCD3.4070600@comcast.net>
* 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