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

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