From: | Alexander Shereshevsky <shereshevsky(at)gmail(dot)com> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: record from plpgsql function performance |
Date: | 2015-07-02 15:09:38 |
Message-ID: | CAJMMYvpGHPhCQTN6fbM8d4tdtwuyiWBMXV0jE2dG0CWtkitd1w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Thanks, David.
Works perfect.
Best Regards,
Alexander Shereshevsky
+972-52-7460635
On Thu, Jul 2, 2015 at 5:47 PM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> 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.
>
From | Date | Subject | |
---|---|---|---|
Next Message | Vick Khera | 2015-07-02 15:50:00 | Re: Running PostgreSQL with ZFS ZIL |
Previous Message | Melvin Davidson | 2015-07-02 14:58:34 | Re: very slow queries and ineffective vacuum |