pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedView vs function
4+ messages / 4 participants
[nested] [flat]
* View vs function
@ 2005-03-21 03:39 Keith Worthington <KeithW@NarrowPathInc.com>
2005-03-21 04:27 ` Re: View vs function Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 4+ messages in thread
From: Keith Worthington @ 2005-03-21 03:39 UTC (permalink / raw)
To: pgsql-performance
Hi All,
I have been reading about set returning functions. What I would like to
know is is there a performance advantage in using SRFs versus querying a
view. Assuming the underlying SQL is the same for the view vs the
function except for the WHERE clause which of these would you expect to
be faster? Or does the planner realize all this...
SELECT * FROM view_big_query WHERE column1 = 1234;
SELECT * FROM func_bug_query(1234);
--
Kind Regards,
Keith
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: View vs function
2005-03-21 03:39 View vs function Keith Worthington <KeithW@NarrowPathInc.com>
@ 2005-03-21 04:27 ` Bruno Wolff III <bruno@wolff.to>
2005-03-21 05:13 ` Re: View vs function Neil Conway <neilc@samurai.com>
0 siblings, 1 reply; 4+ messages in thread
From: Bruno Wolff III @ 2005-03-21 04:27 UTC (permalink / raw)
To: Keith Worthington <KeithW@NarrowPathInc.com>; +Cc: pgsql-performance
On Sun, Mar 20, 2005 at 22:39:57 -0500,
Keith Worthington <KeithW@NarrowPathInc.com> wrote:
> Hi All,
>
> I have been reading about set returning functions. What I would like to
> know is is there a performance advantage in using SRFs versus querying a
> view. Assuming the underlying SQL is the same for the view vs the
> function except for the WHERE clause which of these would you expect to
> be faster? Or does the planner realize all this...
In general you are going to be better off with a view, since the planner
knows what the view is doing and there may be some optimizations it
can make. Functions are just black boxes to the planner.
>
> SELECT * FROM view_big_query WHERE column1 = 1234;
>
> SELECT * FROM func_bug_query(1234);
>
> --
> Kind Regards,
> Keith
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: View vs function
2005-03-21 03:39 View vs function Keith Worthington <KeithW@NarrowPathInc.com>
2005-03-21 04:27 ` Re: View vs function Bruno Wolff III <bruno@wolff.to>
@ 2005-03-21 05:13 ` Neil Conway <neilc@samurai.com>
2005-03-21 06:40 ` Re: View vs function Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Neil Conway @ 2005-03-21 05:13 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: Keith Worthington <KeithW@NarrowPathInc.com>; pgsql-performance
Bruno Wolff III wrote:
> Functions are just black boxes to the planner.
... unless the function is a SQL function that is trivial enough for the
planner to inline it into the plan of the invoking query. Currently, we
won't inline set-returning SQL functions that are used in the query's
rangetable, though. This would be worth doing, I think -- I'm not sure
how much work it would be, though.
-Neil
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: View vs function
2005-03-21 03:39 View vs function Keith Worthington <KeithW@NarrowPathInc.com>
2005-03-21 04:27 ` Re: View vs function Bruno Wolff III <bruno@wolff.to>
2005-03-21 05:13 ` Re: View vs function Neil Conway <neilc@samurai.com>
@ 2005-03-21 06:40 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2005-03-21 06:40 UTC (permalink / raw)
To: Neil Conway <neilc@samurai.com>; +Cc: Bruno Wolff III <bruno@wolff.to>; Keith Worthington <KeithW@NarrowPathInc.com>; pgsql-performance
Neil Conway <neilc@samurai.com> writes:
> Bruno Wolff III wrote:
>> Functions are just black boxes to the planner.
> ... unless the function is a SQL function that is trivial enough for the
> planner to inline it into the plan of the invoking query. Currently, we
> won't inline set-returning SQL functions that are used in the query's
> rangetable, though. This would be worth doing, I think -- I'm not sure
> how much work it would be, though.
Yeah, I've been thinking the same. It seems like it shouldn't be unduly
difficult --- not harder than inlining scalar-valued SQL functions, just
different validity conditions.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-03-21 06:40 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-21 03:39 View vs function Keith Worthington <KeithW@NarrowPathInc.com>
2005-03-21 04:27 ` Bruno Wolff III <bruno@wolff.to>
2005-03-21 05:13 ` Neil Conway <neilc@samurai.com>
2005-03-21 06:40 ` Tom Lane <tgl@sss.pgh.pa.us>
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