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

update slows down in pl/pgsql function

From: Jenny Zhang <jenny(at)osdl(dot)org>
To: perf-pgsql <pgsql-performance(at)postgresql(dot)org>,pgsql-general(at)postgresql(dot)org
Subject: update slows down in pl/pgsql function
Date: 2003-12-16 23:52:41
Message-ID: 1071618760.10925.17.camel@ibm-a.pdx.osdl.net (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-performance
I have stored procedure written in pl/pgsql which takes about 13 seconds
to finish.  I was able to identify that the slowness is caused by one
update SQL:

UPDATE shopping_cart SET sc_sub_total=sc_subtotal, sc_date=now()    
WHERE sc_id=sc_id;

If I comment this sql out, the stored procedure returns within 1 second.

What puzzles me is that if I execute the same update SQL in psql
interface, it returns very fast.  The following is the explain analyze
output for that SQL.  

#>explain analyze UPDATE shopping_cart SET sc_sub_total=1, sc_date=now()
where sc_id=260706;
                                                            QUERY
PLAN                                                                                 
----------------------------------------------------------------------------------------------------------------------------------
 Index Scan using shopping_cart_pkey on shopping_cart  (cost=0.00..5.01
rows=1 width=144) (actual time=0.22..0.37 rows=1 loops=1)
   Index Cond: (sc_id = 260706::numeric)
 Total runtime: 1.87 msec
(3 rows)

Is it true that using pl/pgsql increases the overhead that much?

TIA,
Jenny
-- 
Jenny Zhang
Open Source Development Lab
12725 SW Millikan Way, Suite 400
Beaverton, OR 97005
(503)626-2455 ext 31



Responses

pgsql-performance by date

Next:From: Stephan SzaboDate: 2003-12-16 23:54:34
Subject: Re: update slows down in pl/pgsql function
Previous:From: Neil ConwayDate: 2003-12-16 22:51:18
Subject: Re: Why is VACUUM ANALYZE <table> so slow?

pgsql-general by date

Next:From: Stephan SzaboDate: 2003-12-16 23:54:34
Subject: Re: update slows down in pl/pgsql function
Previous:From: Jenny ZhangDate: 2003-12-16 23:37:21
Subject: Re: passing array as argument and returning an array in

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