Thread: complicated queries in pl/pgsql

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

 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

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

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