Re: record from plpgsql function performance

On Thursday, July 2, 2015, Alexander Shereshevsky <shereshevsky(at)gmail(dot)com>
wrote:

> Hello,
>
> I have some simple function. The returned data set is generated based on
> view (dynamic - can be changed on daily basis).
> So the function was defined this way:
>
> 1. returns setof some_view as ...
> 2. inside the function I'm generating dynamic SQL into v_sql variable.
> 3. return query execute v_sql
>
> Everything works fine if I'm running single function, like:
> select * from function (param1, param2)
> I'm getting the delimited fields in desired order.
>
> But if I want to run the function in inline mode, like:
> select function(param1, param2) from some_table;
> so the returned datatype is record.
>
> To return multiple fields, I'm trying to use:
> select (function(param1, param2)).* from some_table;
> But this operation has a huge performance impact, IMHO runtime multiplied
> by number of columns - the function is executed for each column separately.
> In my case normal inline run is about 2 seconds for 300-400 records, but
> with ().* it's increased to 90-120 seconds.
>
> Thank you in advance if you can suggest me the better way.
>
> BR,
> Alexander Shereshevsky
>

Use LATERAL.

If that is not an option you place the unexpanded function call in a CTE
(with) and the expand it within the main query.

With funccall as ( select func(arg) from tbl )
Select (func).* from funccall;

Because, yes the function is called once for each column due to the star
expansion. You have to keep the result as a composite type during function
execution and then expand the composite type.

David J.