Search Postgresql Archives

Difference between speed of 2 functions: SQL+STABLE and PLPGSQL+EXECUTE

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux