From: | Abu Mushayeed <abumushayeed(at)yahoo(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Slow update |
Date: | 2007-02-01 00:57:38 |
Message-ID: | 359151.78151.qm@web57112.mail.re3.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Chad Wagner | 2007-02-01 01:29:55 | Re: Very slow queries |
Previous Message | Mischa Sandberg | 2007-01-31 17:25:12 | Re: Tuning |