Skip site navigation (1) Skip section navigation (2)

Slow update

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 (view raw or flat)
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.

Responses

pgsql-performance by date

Next:From: Chad WagnerDate: 2007-02-01 01:29:55
Subject: Re: Very slow queries
Previous:From: Mischa SandbergDate: 2007-01-31 17:25:12
Subject: Re: Tuning

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group