Pavel, thank you very much for your explanation. Is it possible to define under what conditions that sql procs will outperform plpgsql ones, and vice-versa? -----Original Message----- From: Pavel Stehule [mailto:pavel.stehule@xxxxxxxxx] Sent: January 30, 2012 2:57 AM To: Carlo Stonebanks Cc: Merlin Moncure; pgsql-performance@xxxxxxxxxxxxxx Subject: Re: pl/pgsql functions outperforming sql ones? Hello 2012/1/30 Carlo Stonebanks <stonec.register@xxxxxxxxxxxx>: > Pavel, are you saying that the code of the stored function is actually being > added to the SQL query, instead of a call to it? For example, I have seen > this: > > SELECT myVar > FROM myTable > WHERE myVar > 0 AND myFunc(myVar) > > And seen the SQL body of myVar appended to the outer query: > > ... Filter: SELECT CASE WHERE myVar < 10 THEN true ELSE false END > > Is this what we are talking about? Two questions: yes - it is SQL function "inlining" > > 1) Is this also done when the function is called as a SELECT column; > e.g. would: > SELECT myFunc(myVar) AS result > - become: > SELECT ( > SELECT CASE WHERE myVar < 10 THEN true ELSE false END > ) AS result? > yes CREATE OR REPLACE FUNCTION public.fx(integer, integer) RETURNS integer LANGUAGE sql AS $function$ select coalesce($1, $2) $function$ postgres=# explain verbose select fx(random()::int, random()::int); QUERY PLAN -------------------------------------------------------------- Result (cost=0.00..0.02 rows=1 width=0) Output: COALESCE((random())::integer, (random())::integer) (2 rows) > 2) Does that not bypass the benefits of IMMUTABLE? > no - optimizator works with expanded query - usually is preferred style a writing SQL functions without flags, because optimizer can work with definition of SQL function and can set well flags. SQL function is not black box for optimizer like plpgsql does. And SQL optimizer chooses a inlining or some other optimizations. Sometimes explicit flags are necessary, but usually not for scalar SQL functions. postgres=# create or replace function public.fxs(int) postgres-# returns setof int as $$ postgres$# select * from generate_series(1,$1) postgres$# $$ language sql; CREATE FUNCTION postgres=# explain verbose select * from fxs(10); QUERY PLAN ------------------------------------------------------------------- Function Scan on public.fxs (cost=0.25..10.25 rows=1000 width=4) Output: fxs Function Call: fxs(10) (3 rows) postgres=# create or replace function public.fxs(int) returns setof int as $$ select * from generate_series(1,$1) $$ language sql IMMUTABLE; CREATE FUNCTION postgres=# explain verbose select * from fxs(10); QUERY PLAN ---------------------------------------------------------------------------- ------- Function Scan on pg_catalog.generate_series (cost=0.00..10.00 rows=1000 width=4) Output: generate_series.generate_series Function Call: generate_series(1, 10) --<<<< inlined query (3 rows) Regards Pavel Stehule > > > -----Original Message----- > From: pgsql-performance-owner@xxxxxxxxxxxxxx > [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] On Behalf Of Pavel Stehule > Sent: January 28, 2012 1:38 AM > To: Carlo Stonebanks > Cc: Merlin Moncure; pgsql-performance@xxxxxxxxxxxxxx > Subject: Re: pl/pgsql functions outperforming sql ones? > > 2012/1/27 Carlo Stonebanks <stonec.register@xxxxxxxxxxxx>: >> Yes, I did test it - i.e. I ran the functions on their own as I had > always >> noticed a minor difference between EXPLAIN ANALYZE results and direct > query >> calls. >> >> Interesting, so sql functions DON'T cache plans? Will plan-caching be of > any >> benefit to SQL that makes no reference to any tables? The SQL is emulating >> the straight non-set-oriented procedural logic of the original plpgsql. >> > > It is not necessary usually - simple SQL functions are merged to outer > query - there are e few cases where this optimization cannot be > processed and then there are performance lost. > > For example this optimization is not possible (sometimes) when some > parameter is volatile > > Regards > > Pavel Stehule > > -- > Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
