pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: complicated queries in pl/pgsql
4+ messages / 2 participants
[nested] [flat]
* Re: complicated queries in pl/pgsql
@ 2003-01-13 18:44 Josh Berkus <josh@agliodbs.com>
parent: 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
* complicated queries in pl/pgsql
@ 2003-01-14 10:54 Tomasz Myrta <jasiek@klaster.net>
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 20:36 Josh Berkus <josh@agliodbs.com>
parent: Tomasz Myrta <jasiek@klaster.net>
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
* Re: complicated queries in pl/pgsql
@ 2003-01-15 19:12 Tomasz Myrta <jasiek@klaster.net>
parent: 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
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