pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
update slows down in pl/pgsql function
3+ messages / 2 participants
[nested] [flat]

* update slows down in pl/pgsql function
@ 2003-12-16 23:52 Jenny Zhang <jenny@osdl.org>
  2003-12-16 23:54 ` Re: update slows down in pl/pgsql function Stephan Szabo <sszabo@megazone.bigpanda.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Jenny Zhang @ 2003-12-16 23:52 UTC (permalink / raw)
  To: pgsql-performance; pgsql-general@postgresql.org

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





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: update slows down in pl/pgsql function
  2003-12-16 23:52 update slows down in pl/pgsql function Jenny Zhang <jenny@osdl.org>
@ 2003-12-16 23:54 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
  2003-12-17 00:07   ` Re: update slows down in pl/pgsql function Jenny Zhang <jenny@osdl.org>
  0 siblings, 1 reply; 3+ messages in thread

From: Stephan Szabo @ 2003-12-16 23:54 UTC (permalink / raw)
  To: Jenny Zhang <jenny@osdl.org>; +Cc: pgsql-performance; pgsql-general@postgresql.org

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?



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: update slows down in pl/pgsql function
  2003-12-16 23:52 update slows down in pl/pgsql function Jenny Zhang <jenny@osdl.org>
  2003-12-16 23:54 ` Re: update slows down in pl/pgsql function Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2003-12-17 00:07   ` Jenny Zhang <jenny@osdl.org>
  0 siblings, 0 replies; 3+ messages in thread

From: Jenny Zhang @ 2003-12-17 00:07 UTC (permalink / raw)
  To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: pgsql-performance; general-pgsql <pgsql-general@postgresql.org>

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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2003-12-17 00:07 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-12-16 23:52 update slows down in pl/pgsql function Jenny Zhang <jenny@osdl.org>
2003-12-16 23:54 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-12-17 00:07   ` Jenny Zhang <jenny@osdl.org>

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