From: Abu Mushayeed <abumushayeed@yahoo.com>
To: pgsql-performance@postgresql.org
Subject: Slow update
Date: Wed, 31 Jan 2007 16:57:38 -0800 (PST)
Message-ID: <359151.78151.qm@web57112.mail.re3.yahoo.com> (raw)
Following is one of the update query and it's explain plan which takes about 6 mins to execute. I am trying to find a way to execute it faster. The functions used in the update statement are if then else test and then return one value or the other.
=====================================================
update mdc_upc
set total_curoh = ownedgoods(k.gmmid,k.divid, loc1_oh, loc2_oh,loc3_oh,loc120_oh, loc15_oh,chesh_curoh),
total_curoo =orderedgoods(k.gmmid,k.divid, loc1_oo, loc2_oo,loc3_oo,loc120_oo, loc15_oo,chesh_curoo),
total_oh = ownedgoods(k.gmmid,k.divid, 0, 0,loc3_oh,loc120_oh, loc15_oh,chesh_oh),
total_oo =orderedgoods(k.gmmid,k.divid, 0, 0,loc3_oo,loc120_oo, loc15_oo,chesh_oo)
from mdc_products p LEFT OUTER JOIN
kst k on p.dvm_d = k.dept
where p.keyp_products = mdc_upc.keyf_products;
---------------------------------------------------------------------------
Hash Join (cost=48602.07..137331.77 rows=695899 width=391)
Hash Cond: ("outer".keyf_products = "inner".keyp_products)
-> Seq Scan on mdc_upc (cost=0.00..59153.99 rows=695899 width=383)
-> Hash (cost=47274.60..47274.60 rows=530990 width=12)
-> Hash Left Join (cost=43.85..47274.60 rows=530990 width=12)
Hash Cond: ("outer".dvm_d = "inner".dept)
-> Seq Scan on mdc_products p (cost=0.00..39265.90 rows=530990 width=8)
-> Hash (cost=41.48..41.48 rows=948 width=12)
-> Seq Scan on kst k (cost=0.00..41.48 rows=948 width=12)
======================================================
I have seen that the updates are very slow on our system. What parameter should I test in order to find out why is it slow during update.
Thanks
Abu
---------------------------------
It's here! Your new message!
Get new email alerts with the free Yahoo! Toolbar.
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: abumushayeed@yahoo.com
Subject: Re: Slow update
In-Reply-To: <359151.78151.qm@web57112.mail.re3.yahoo.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