Hello.
For example, I have 2 functions like these:
CREATE OR REPLACE FUNCTION first(a INTEGER, b INTEGER, ...) RETURNS ... AS
$body$
...any SQL which uses $1, $2 etc. arguments, plus LIMIT $3...
$body$
LANGUAGE 'sql'
*STABLE*
and
CREATE OR REPLACE FUNCTION second(a INTEGER, b INTEGER, ...) RETURNS ... AS
$body$
DECLARE
res ...;
BEGIN
EXECUTE '...the same SELECT, ' ||
'but ' || quote_literal(a) || ' args are embedded, plus ' ||
'LIMIT ' || quote_literal($3)
INTO res;
RETURN res;
END;
$body$
LANGUAGE 'plpgsql'
*STABLE*
And then I call
EXPLAIN ANALYZE SELECT * FROM first(...);
EXPLAIN ANALYZE SELECT * FROM second(...);
Should these two queries be executed by the same time usage (i.e. does
PostgreSQL generate same plans for inner queries)?
I always thought that the answer is YES: if a function is STABLE and with
language=SQL, its SQL code is embedded into outer context after all
arguments are expanded into their values (so the plan is built after
argument expansion). But some days ago I detected a case when second()
works about 100 times faster than first(), and the cause is seems that the
planner does not see all of expanded arguments in first() (if I replace
arguments to constants in first(), especially in LIMIT clause, it begins to
work the same speed as second() does). Unfortunately EXPLAIN ANALYZE does
not go into functions and shows only overall time, so I have no real
information about what plan is actually used in first().
Could you please comment this case a bit?..
