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
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?..
