Re: [PERFORM] Views and functions returning sets of records
On 22/mar/08, at 17:55, Andreas Kretschmer wrote: Tom Lane <[EMAIL PROTECTED]> schrieb: Giorgio Valoti <[EMAIL PROTECTED]> writes: maybe it’s a naive question but I was wondering if there is any difference, from a performance point of view, between a view and a function performing the same task, Yes. Usually the view will win. *smile* :-( I was thinking about using using functions as the main way to interact with the database from an external application. The (supposed) rationale was to simplify the application code: you only have to worry about in and out function parameters. Are there any way to pass some hints to the planner? For example, could the IMMUTABLE/STABLE/VOLATILE modifiers be of some help? Thank you -- Giorgio Valoti - Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Views and functions returning sets of records
Giorgio Valoti <[EMAIL PROTECTED]> writes: > Are there any way to pass some hints to the planner? For example, > could the IMMUTABLE/STABLE/VOLATILE modifiers be of some help? Those don't really do anything for set-returning functions at the moment. As of 8.3 there is a ROWS attribute for SRFs that can help with one of the worst problems, namely that the planner has no idea how many rows a SRF might return. It's simplistic (just an integer constant estimate) but better than no control at all. As of CVS HEAD (8.4 to be) there's a capability in the planner to "inline" SRFs that are single SELECTs in SQL language, which should pretty much eliminate the performance differential against a comparable view. Unfortunately 8.4 release is at least a year away, but just so you know. (I suppose if you were desperate enough to run a privately modified copy, that patch should drop into 8.3 easily enough.) IIRC the restrictions for this to happen are * single SELECT * function declared to return set * function NOT declared strict or volatile * function NOT declared SECURITY DEFINER or given any local parameter settings The latter restrictions are needed so that inlining doesn't change the semantics. regards, tom lane - Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
