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

On Wed, May 8, 2013 at 11:12 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Merlin Moncure <mmoncure@gmail.com> writes:
>> 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;
>
> Something wrong with your measurement technique, because those expand
> to identical plan trees for me.

you're right! interesting.  I had left the function f() as 'immutable'
(which is technically incorrect) before running performance test:

postgres=# create or replace function f(text) returns date as $$
  select to_date($1, 'YYYY'); $$
language sql stable;
CREATE FUNCTION
Time: 1.000 ms
postgres=# explain analyze select f(v::text) from generate_series(1,100000) v;
                                                         QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series v  (cost=0.00..17.50 rows=1000
width=4) (actual time=12.949..110.804 rows=100000 loops=1)
 Total runtime: 167.938 ms
(2 rows)

Time: 169.017 ms
postgres=# create or replace function f(text) returns date as $$
  select to_date($1, 'YYYY'); $$
language sql immutable;
CREATE FUNCTION
Time: 2.000 ms
postgres=# explain analyze select f(v::text) from generate_series(1,100000) v;
                                                         QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series v  (cost=0.00..265.00 rows=1000
width=4) (actual time=15.362..499.792 rows=100000 loops=1)
 Total runtime: 562.465 ms
(2 rows)


odd that stable function is inlined but immutable isn't!

merlin