By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,903 Members | 1,081 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,903 IT Pros & Developers. It's quick & easy.

update slows down in pl/pgsql function

P: n/a
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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Oops, I named the var name the same as the column name. Changing it to
something else solved the problem.

Thanks,
Jenny
On Tue, 2003-12-16 at 15:54, Stephan Szabo wrote:
On Tue, 16 Dec 2003, Jenny Zhang wrote:
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;


Umm, is that exactly the condition you're using? Isn't that going to
update the entire table?

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.