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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-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
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2003-12-16 23:54:34 | Re: update slows down in pl/pgsql function |
Previous Message | Jenny Zhang | 2003-12-16 23:37:21 | Re: passing array as argument and returning an array in |
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2003-12-16 23:54:34 | Re: update slows down in pl/pgsql function |
Previous Message | Neil Conway | 2003-12-16 22:51:18 | Re: Why is VACUUM ANALYZE <table> so slow? |