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
