Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 2CEAF9FB456 for ; Wed, 31 Jan 2007 20:57:43 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 60158-08 for ; Wed, 31 Jan 2007 20:57:41 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from web57112.mail.re3.yahoo.com (web57112.mail.re3.yahoo.com [216.252.111.125]) by postgresql.org (Postfix) with SMTP id C4D649FB408 for ; Wed, 31 Jan 2007 20:57:39 -0400 (AST) Received: (qmail 79238 invoked by uid 60001); 1 Feb 2007 00:57:38 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=Uju9TdxjpYRnenTLNlwJKnWFFiUURKWGYQAA7l6S3diavW319qu4Zo6PhCL6RqNzKT2iKPXpblBxucUzuQSYa01M7Tho/FK48Aupo483gLSEfsqAMJAKEHUrd4aXg5R/uIgFpEdYSCr9SnAeMr3qtQxsToQR38xtD3WhyF6iZZA=; X-YMail-OSG: V2CjKD0VM1mkUEThOUnmk7htt6Kzl7awu.DnJIDDpC9eIkI1_4Dd9BB_r3YGKa18MPSn1mGX2TdkFoAnPbtT_zWXqjG1J6fCY1hlKSpZmqXX1sv_011lY7UBrGxFy_0- Received: from [64.237.4.140] by web57112.mail.re3.yahoo.com via HTTP; Wed, 31 Jan 2007 16:57:38 PST Date: Wed, 31 Jan 2007 16:57:38 -0800 (PST) From: Abu Mushayeed Subject: Slow update To: pgsql-performance@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-2108087663-1170291458=:78151" Content-Transfer-Encoding: 8bit Message-ID: <359151.78151.qm@web57112.mail.re3.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.401 tagged_above=0 required=5 tests=AWL, BAYES_00, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS, HTML_30_40, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200701/451 X-Sequence-Number: 23052 --0-2108087663-1170291458=:78151 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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. --0-2108087663-1170291458=:78151 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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. --0-2108087663-1170291458=:78151--