From: Tomas Vondra <tv@fuzzy.cz>
To: Abu Mushayeed <abumushayeed@yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow update
Date: Thu, 01 Feb 2007 02:32:20 +0100
Message-ID: <45C14324.1020907@fuzzy.cz> (raw)
In-Reply-To: <359151.78151.qm@web57112.mail.re3.yahoo.com>
References: <359151.78151.qm@web57112.mail.re3.yahoo.com>
> 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
Obviously the update is slow because of sequential scans of the table,
with about 1GB od data to read - this may seem as 'not too much of data'
but there may be a lot of seeks, knocking the performance down. You can
use iowait / dstat to see how much time is spent waiting for the data.
I have to admin I don't fully uderstand that query as I've never used
the UPDATE ... FROM query, but it seems to me you could set up some
indexes to speed things up. I'd definitely start with indexes on the
columns used in join conditions, namely
CREATE INDEX mdc_products_keyp_idx ON mdc_products(keyp_products);
CREATE INDEX mdc_upc_keyf_idx ON mdc_upc(keyf_products);
CREATE INDEX mdc_products_dvm_idx ON mdc_products(dvm_d);
CREATE INDEX kst_dept_idx ON kst(dept);
but this is just a guess as I really know NOTHING about those tables
(structure, size, statistical features, etc.) Btw. don't forget to
analyze the tables.
Another 'uncertainty' is related to the functions used in your query,
namely ownedgoods() / orderedgoods(). If these procedures do something
nontrivial (searches in tables, etc.) it might have severe impact on the
query - but the parser / optimizer knows nothing about these procedures
so it can't optimize them.
If this won't help, we'll need some more information - for example what
does the 'our system' mean - how much memory doest it have? What are the
important settings in your postgresql.conf? Especially what is the value
of effective_cache_size, work_mem, and some others (for example number
of checkpoint segments as it seems like a write-intensive query).
And last but not least info of the structure and size of the tables
(columns, indexes, number of rows, number of occupied blocks, etc).
Tomas
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: tv@fuzzy.cz, abumushayeed@yahoo.com
Subject: Re: Slow update
In-Reply-To: <45C14324.1020907@fuzzy.cz>
* 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