Re: simple functions, huge overhead, no cache

Craig Ringer <craig@postnewspapers.com.au> writes:
> Your problem boils down to the fact that PL/PgSQL function calls are
> expensive. PL/PgSQL is good for complex work, but bad for wrapping up
> simple expressions because setting up / tearing down the function call
> context is so expensive.

Yes.  What's actually the most expensive part is invoking the main
executor to process SQL expressions --- that's approximately the same
cost as starting up/shutting down an entire SQL query.  plpgsql has some
optimizations to avoid some of that cost when evaluating a "simple"
expression, but both of the examples here defeat that optimization:
one because it's an EXISTS() subquery, and the other because of the
entirely-unnecessary use of a sub-SELECT.

To illustrate the point I did a bit of experimentation using CVS HEAD.
This is an assert-enabled build so it will have more overhead than a
production build ... you might care to repeat the tests on your own
installation.

Base case (to measure the overhead of the test query):

regression=# explain analyze verbose select (x::float8) from generate_series(1,100000) x;
                                                               QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------
 Function Scan on pg_catalog.generate_series x  (cost=0.00..12.50 rows=1000 width=4) (actual time=252.595..980.359
rows=100000loops=1) 
   Output: (x)::double precision
 Total runtime: 1495.410 ms
(3 rows)

Add the built-in cos() function to that:

regression=# explain analyze verbose select cos(x::float8) from generate_series(1,100000) x;
                                                               QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------
 Function Scan on pg_catalog.generate_series x  (cost=0.00..15.00 rows=1000 width=4) (actual time=242.764..1140.608
rows=100000loops=1) 
   Output: cos((x)::double precision)
 Total runtime: 1652.968 ms
(3 rows)

So cos() added about 150 ms to the test, or 1.5 microsec per execution.
Now let's wrap the cos() call in a plpgsql function:

regression=# create function plpgsql_cosine(float8) returns float8 as '
begin return cos($1); end' language plpgsql;
CREATE FUNCTION
regression=# explain analyze verbose select plpgsql_cosine(x::float8) from generate_series(1,100000) x;
                                                                QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------
 Function Scan on pg_catalog.generate_series x  (cost=0.00..262.50 rows=1000 width=4) (actual time=254.050..5719.287
rows=100000loops=1) 
   Output: plpgsql_cosine((x)::double precision)
 Total runtime: 6296.207 ms
(3 rows)

The wrapper added about 4650 ms.  If we're incautious enough to make
the RETURN's expression look not-simple:

regression=# create function plpgsql_cosine2(float8) returns float8 as '
begin return (select cos($1)); end' language plpgsql;
CREATE FUNCTION
regression=# explain analyze verbose select plpgsql_cosine2(x::float8) from generate_series(1,100000) x;
                                                                QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------
 Function Scan on pg_catalog.generate_series x  (cost=0.00..262.50 rows=1000 width=4) (actual time=244.735..15557.516
rows=100000loops=1) 
   Output: plpgsql_cosine2((x)::double precision)
 Total runtime: 16092.261 ms
(3 rows)

... the wrapper's overhead balloons to 14440 ms.

> For such simple expressions, you should use 'SQL' functions. These can
> often be inlined to allow the query planner to avoid call overheads
> entirely, and are WAY cheaper even if they can't be inlined. They're
> less flexible, but much faster.

I think the performance benefit of SQL functions is real only if they
can be inlined.  Continuing the example:

regression=# create function sql_cosine(float8) returns float8 as '
regression'# select cos($1)' language sql;
CREATE FUNCTION
regression=# explain analyze verbose select sql_cosine(x::float8) from generate_series(1,100000) x;
                                                               QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------
 Function Scan on pg_catalog.generate_series x  (cost=0.00..15.00 rows=1000 width=4) (actual time=243.677..1140.872
rows=100000loops=1) 
   Output: cos((x)::double precision)
 Total runtime: 1653.346 ms
(3 rows)

The runtime is indistinguishable from the bare cos() call, which is not
surprising since you can see from the EXPLAIN output that what was
getting executed was just the bare cos() call --- the SQL function was
inlined.  If I do something to prevent inlining, it's in the same
ballpark as plpgsql:

regression=# create function sql_cosine2(float8) returns float8 as '
select cos($1) limit 1' language sql;
CREATE FUNCTION
regression=# explain analyze verbose select sql_cosine2(x::float8) from generate_series(1,100000) x;
                                                                QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------
 Function Scan on pg_catalog.generate_series x  (cost=0.00..262.50 rows=1000 width=4) (actual time=242.401..8647.405
rows=100000loops=1) 
   Output: sql_cosine2((x)::double precision)
 Total runtime: 9166.177 ms
(3 rows)

The SQL-function executor is fairly stupid and hasn't had nearly the
level of optimization effort put into it that plpgsql has over the
years.  This test case doesn't really show the effects of that, but
for example plpgsql can remember plans across queries whereas a SQL
function will not.  So if you're focused on performance, use SQL
functions when they can be inlined (and use EXPLAIN to *verify*
that they're getting inlined) ... but otherwise you're probably as
well or better off with plpgsql.

Oh, and one more thing: there is no "function cache".

            regards, tom lane