Hello,
It has been brought to my attention that SQL functions always use generic
plans.
Take this function for example:
create or replace function test_plpgsql(p1 oid) returns text as $$
BEGIN
RETURN (SELECT relname FROM pg_class WHERE oid = p1 OR p1 IS NULL LIMIT 1);
END;
$$ language plpgsql;
As expected, the PlanCache takes care of generating parameter specific plans,
and correctly prunes the redundant OR depending on wether we call the function
with a NULL value or not:
ro=# select test_plpgsql(NULL);
LOG: duration: 0.030 ms plan:
Query Text: (SELECT relname FROM pg_class WHERE oid = p1 OR p1 IS NULL LIMIT
1)
Result (cost=0.04..0.05 rows=1 width=64)
InitPlan 1 (returns $0)
-> Limit (cost=0.00..0.04 rows=1 width=64)
-> Seq Scan on pg_class (cost=0.00..18.12 rows=412 width=64)
LOG: duration: 0.662 ms plan:
Query Text: select test_plpgsql(NULL);
Result (cost=0.00..0.26 rows=1 width=32)
ro=# select test_plpgsql(1);
LOG: duration: 0.075 ms plan:
Query Text: (SELECT relname FROM pg_class WHERE oid = p1 OR p1 IS NULL LIMIT
1)
Result (cost=8.29..8.30 rows=1 width=64)
InitPlan 1 (returns $0)
-> Limit (cost=0.27..8.29 rows=1 width=64)
-> Index Scan using pg_class_oid_index on pg_class
(cost=0.27..8.29 rows=1 width=64)
Index Cond: (oid = '1'::oid)
LOG: duration: 0.675 ms plan:
Query Text: select test_plpgsql(1);
Result (cost=0.00..0.26 rows=1 width=32)
But writing the same function in SQL:
create or replace function test_sql(p1 oid) returns text as $$
SELECT relname FROM pg_class WHERE oid = p1 OR p1 IS NULL LIMIT 1
$$ language sql;
we end up with a generic plan:
ro=# select test_sql(1);
LOG: duration: 0.287 ms plan:
Query Text: SELECT relname FROM pg_class WHERE oid = p1 OR p1 IS NULL LIMIT 1
Query Parameters: $1 = '1'
Limit (cost=0.00..6.39 rows=1 width=32)
-> Seq Scan on pg_class (cost=0.00..19.16 rows=3 width=32)
Filter: ((oid = $1) OR ($1 IS NULL))
This is due to the fact that SQL functions are planned once for the whole
query using a specific SQLFunctionCache instead of using the whole PlanCache
machinery.
The following comment can be found in functions.c, about the SQLFunctionCache:
* Note that currently this has only the lifespan of the calling query.
* Someday we should rewrite this code to use plancache.c to save parse/plan
* results for longer than that.
I would be interested in working on this, primarily to avoid this problem of
having generic query plans for SQL functions but maybe having a longer lived
cache as well would be nice to have.
Is there any reason not too, or pitfalls we would like to avoid ?
Best regards,
--
Ronan Dunklau