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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Merlin Moncure <mmoncure(at)gmail(dot)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