Re: Does it make sense to break a large query into separate functions?

On Wed, May 8, 2013 at 9:05 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Seref Arikan <serefarikan@kurumsalteknoloji.com> writes:
>> I've just noticed that some parts of the autogenerated queries can be
>> functions on their own. Would moving these parts to their own functions
>> help the query planner?
>
> It's difficult to answer that without a lot more detail than you've
> provided, but my offhand guess would be "no".  Usually it's better
> when the planner can expand functions inline, which would just be
> reversing the transformation you're thinking of.

In my experience virtually no useful functions are inlined by the
planner.   For example, with function:

create function f(text) returns date as $$
  select to_date($1, 'YYYY'); $$
language sql stable;  /* immutable doesn't help */

I see about 4x time difference between:
select to_date(v::text, 'YYYY') from generate_series(1,100000) v;
and
select f(v::text) from generate_series(1,100000) v;

I'm curious if more aggressive inlining is a future performance
optimization target for postgres or if there is some fundamental
restriction that prevents such functions from being inlined.  From an
abstraction point of view, I'd really like to be able to push more
code into functions, but often can't because of performance issues.

merlin