pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: complicated queries in pl/pgsql
4+ messages / 2 participants
[nested] [flat]

* complicated queries in pl/pgsql
@ 2003-01-14 10:54 Tomasz Myrta <jasiek@klaster.net>
  2003-01-13 18:44 ` Re: complicated queries in pl/pgsql Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Tomasz Myrta @ 2003-01-14 10:54 UTC (permalink / raw)
  To: pgsql-performance

Hi
Sometimes my pl/pgsql functions have pretty complicated queries inside - 
  most of their execution time takes postgresql query planning.
I was wondering if I should change these queries into views? Does it 
speed up function execution? Pl/pgsql saves execution plan for 
connection lifetime. What happens to view planning - is it performed 
during view creation, or rather each time view is quered?

Regards,
Tomasz Myrta




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

* Re: complicated queries in pl/pgsql
  2003-01-14 10:54 complicated queries in pl/pgsql Tomasz Myrta <jasiek@klaster.net>
@ 2003-01-13 18:44 ` Josh Berkus <josh@agliodbs.com>
  2003-01-15 19:12   ` Re: complicated queries in pl/pgsql Tomasz Myrta <jasiek@klaster.net>
  0 siblings, 1 reply; 4+ messages in thread

From: Josh Berkus @ 2003-01-13 18:44 UTC (permalink / raw)
  To: Tomasz Myrta <jasiek@klaster.net>; pgsql-performance

 Tomasz,

> What happens to view planning - is it performed 
> during view creation, or rather each time view is quered?

Each time the view is executed.   The only savings in running a view over a 
regular query is that the view will have taken care of some reference 
expansion and JOIN explication during the CREATE process, but not planning.  
Also, views can actually be slower if the view is complex enough that any 
query-time parameters cannot be "pushed down" into the view.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




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

* Re: complicated queries in pl/pgsql
  2003-01-14 10:54 complicated queries in pl/pgsql Tomasz Myrta <jasiek@klaster.net>
  2003-01-13 18:44 ` Re: complicated queries in pl/pgsql Josh Berkus <josh@agliodbs.com>
@ 2003-01-15 19:12   ` Tomasz Myrta <jasiek@klaster.net>
  2003-01-14 20:36     ` Re: complicated queries in pl/pgsql Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Tomasz Myrta @ 2003-01-15 19:12 UTC (permalink / raw)
  To: josh@agliodbs.com; +Cc: pgsql-performance

Josh Berkus wrote:

 >  Tomasz,
 >
 >
 > >What happens to view planning - is it performed
 > >during view creation, or rather each time view is quered?
 >
 >
 > Each time the view is executed.   The only savings in running a view 
over a
 > regular query is that the view will have taken care of some reference
 > expansion and JOIN explication during the CREATE process, but not 
planning.
 > Also, views can actually be slower if the view is complex enough that 
any
 > query-time parameters cannot be "pushed down" into the view.

Thanks a lot.
I'm asking, because I use some queries which are easy to change into 
views. Most of their execution time takes planning, they use 5-10 
explicit table joins with not too many rows in these tables and returns 
few values.

Now I know, that queries inside pl/pgsql functions are better in these 
situations:
- complex queries whith deep parameters
- execution several times during conection lifetime.

Can anyone add something?

Tomasz Myrta




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

* Re: complicated queries in pl/pgsql
  2003-01-14 10:54 complicated queries in pl/pgsql Tomasz Myrta <jasiek@klaster.net>
  2003-01-13 18:44 ` Re: complicated queries in pl/pgsql Josh Berkus <josh@agliodbs.com>
  2003-01-15 19:12   ` Re: complicated queries in pl/pgsql Tomasz Myrta <jasiek@klaster.net>
@ 2003-01-14 20:36     ` Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Josh Berkus @ 2003-01-14 20:36 UTC (permalink / raw)
  To: Tomasz Myrta <jasiek@klaster.net>; +Cc: pgsql-performance


Tomasz,

> Thanks a lot.
> I'm asking, because I use some queries which are easy to change into 
> views. Most of their execution time takes planning, they use 5-10 
> explicit table joins with not too many rows in these tables and returns 
> few values.

You might want to investigate the new "prepared query" functionality in 7.3.1.

I haven't used it yet, so I can't help much.


-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




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


end of thread, other threads:[~2003-01-15 19:12 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-14 10:54 complicated queries in pl/pgsql Tomasz Myrta <jasiek@klaster.net>
2003-01-13 18:44 ` Josh Berkus <josh@agliodbs.com>
2003-01-15 19:12   ` Tomasz Myrta <jasiek@klaster.net>
2003-01-14 20:36     ` Josh Berkus <josh@agliodbs.com>

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