Dmitry Koterov wrote:
> 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().
You can get EXPLAIN plans if you use the auto_explain contrib module
with auto_explain.log_nested_statements enabled.
As you suspect, the two functions work differently.
The SQL function will plan a parameterized statement (with $1 etc. in
it)
and execute that statement whenever it is called, while the PL/pgSQL
function will execute an SQL statement with all the constant literals
in it that gets planned and executed when you call the function.
The SQL function will create a statement that cannot benefit from
optimizations that work only for certain constant values (although
there will be improvements in 9.2 for that). On the down side,
the PL/pgSQL function will have to plan the query every time it is
executed, which does not come for free.
To illustrate that, an example:
I create a table "test" as follows:
CREATE TABLE test(id integer PRIMARY KEY, val text NOT NULL);
CREATE INDEX test_val_ind ON test(val);
Then I fill it with 1000 rows, 11 of which have val='test'
and ANALYZE the table.
CREATE OR REPLACE FUNCTION first(text, integer) RETURNS integer
STABLE STRICT LANGUAGE sql AS
'SELECT id FROM test WHERE val=$1 LIMIT $2';
CREATE OR REPLACE FUNCTION second(IN v text, IN l integer, OUT r
integer)
STABLE STRICT LANGUAGE plpgsql AS
$$BEGIN
EXECUTE 'SELECT id FROM test WHERE val=' || quote_literal(v)
|| ' LIMIT ' || CAST(l AS integer) INTO r;
END$$;
Then I execute "SELECT first('test', 1)" and "SELECT SECOND('test', 1)".
The respective plans are:
For the SQL function:
Query Text: SELECT id FROM test WHERE val=$1 LIMIT $2
Limit (cost=0.00..1.75 rows=50 width=4)
-> Seq Scan on test (cost=0.00..17.50 rows=500 width=4)
Filter: (val = $1)
For the PL/pgSQL function:
Query Text: SELECT id FROM test WHERE val='test' LIMIT 1
Limit (cost=0.00..0.95 rows=1 width=4)
-> Index Scan using test_val_ind on test (cost=0.00..10.46 rows=11
width=4)
Index Cond: (val = 'test'::text)
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
