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

Dmitry Koterov wrote:
> But it's quite strange that SQL+STABLE function does not recalculate
the plan each time it is called.
> Because when I use a bunch of SQL+STABLE functions in e.g. a
sub-select of a complex query, I see in
> the plan of this complex queries that function calls are "expanded".
It looks like PostgreSQL uses SQL
> code defined in SQL+STABLE functions and merges (injects) it into the
main query instead of the
> function call.
>
> E.g.:
>
>
========================================================================
==
> CREATE TABLE a(i INTEGER, t VARCHAR(5));
> INSERT INTO a(i, t) SELECT s, '' FROM generate_series(1, 10000) s;
> INSERT INTO a(i, t) SELECT s, 't' FROM generate_series(10001, 10002)
s;
> CREATE INDEX "a_i_idx" ON "public"."a" USING btree ("i");
> CREATE INDEX "a_t_idx" ON "public"."a" USING btree ("t");
> ANALYZE a;
>
> CREATE OR REPLACE FUNCTION a_get_t(in_a a) RETURNS TEXT STABLE
LANGUAGE sql
> AS 'SELECT $1.t';
>
> explain analyze
> select * from a
> where a_get_t(a) = 't';
>
> QUERY PLAN
> Index Scan using a_t_idx on a  (cost=0.00..8.29 rows=2 width=5)
(actual time=0.041..0.043 rows=2
> loops=1)
>   Index Cond: ((t)::text = 't'::text)
>
========================================================================
==
>
> You may see that a_get_t() SQL code was merged into the main query
plan, so the result is found
> without a seqscan with t='t' filtering, but the index is used.
>
> That was a very simple example, I use much more complex SQL+STABLE
functions in my code and I am
> practically sure that this SQL extraction+injection is applied by
PostgreSQL in other cases too (e.g.
> sometimes planner initiates a hash join with tables which are referred
inside SQL+STABLE functions in
> sub-queries). If I replace STABLE with VOLATILE in that complex cases,
the effect disappears: no more
> SQL extraction performed.
>
> And more: assume we have a function f(x, y, z) VOLATILE with very
complex SQL inside and we call it
> like:
>
> select * from f(1, 2, 3);
>
> The query may took a long time (during not only the first call, but
during all others too). Then I
> just replace VOLATILE to STABLE for f(), and the same query:
>
> select * from f(1, 2, 3);
>
> suddenly becomes very fast (e.g. 1000 times faster or even more). It's
a very common case: I've
> performed many times. I thought that it was because of re-planning of
STABLE functions on each call
> according to real passed values...
>
> If STABLE functions has frozen plans too (independent to its real
passed arguments values), how could
> we explain so much difference in performance replacing VOLATILE to
STABLE?

Simple SQL functions can get "inlined" in queries by the planner,
that's what you see in your first example.

This is IMO unrelated to the problem you describe where a complicated
STABLE SQL function performs much better than the same function declared
VOLATILE.

To understand that difference, one would have to look at the function
definition and EXPLAIN output for both cases, possibly using
auto_analyze.

Yours,
Laurenz Albe