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

From: Dmitry Koterov <dmitry(at)koterov(dot)ru>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Difference between speed of 2 functions: SQL+STABLE and PLPGSQL+EXECUTE
Date: 2012-04-26 20:41:31
Message-ID: CA+CZih6Bjs-S9n=Dirz9MCZdsqW4Vdx_4Od9eX4r-duWkVn5oQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alexander Farber 2012-04-26 20:46:57 Re: Which data type to use for UTF8 JSON and perl/PHP: varchar, text or bytea?
Previous Message Kenneth Tilton 2012-04-26 20:22:04 Re: database error xx000?