pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
View 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