Re: inlining SQL functions - Mailing list pgsql-hackers

On Apr 2, 2010, at 12:12 PM, Tom Lane wrote:
> Alexey Klyukin <alexk@commandprompt.com> writes:
>> Is there a reason why only a table free SQL functions are allowed to
>> be inlined ?  I wonder why a simple SQL function containing only a
>> SELECT * FROM table can't be expanded inline ?
>
> If you're thinking of just replacing the call with a sub-SELECT
> construct, that's no good in general because it would change the
> semantics.  We can and do inline such things when the function
> returns SETOF and is in the FROM list, but a regular scalar subselect
> acts a bit differently than scalar SQL functions historically have.
>
> Keep in mind also that there's not going to be a lot of benefit from
> inlining other cases, since a subselect that's not in FROM is not
> very optimizable.

Since Alexey was working on this for us, I'll elaborate. The actual use case is below. I was hoping that SELECT * FROM

decibel@workbook.local=# explain analyze  SELECT * FROM test.setting( 'Checks disabled' );
              QUERY PLAN                                                
-------------------------------------------------------------------------------------------------------Function Scan on
setting (cost=0.00..0.26 rows=1 width=77) (actual time=0.136..0.136 rows=1 loops=1)Total runtime: 0.151 ms 
(2 rows)

decibel@workbook.local=# explain analyze  SELECT * FROM test.settings WHERE lower(setting_name) = lower('Checks
disabled');                                          QUERY PLAN                                              
---------------------------------------------------------------------------------------------------Seq Scan on settings
(cost=0.00..1.06 rows=1 width=77) (actual time=0.009..0.010 rows=1 loops=1)  Filter: (lower(setting_name) = 'checks
disabled'::text)Totalruntime: 0.026 ms 
(3 rows)

Same issue when prepared, too (and why is this *slower* with a prepared statement??):

decibel@workbook.local=# explain analyze EXECUTE function;                                             QUERY PLAN
                                        
-------------------------------------------------------------------------------------------------------Function Scan on
setting (cost=0.00..0.26 rows=1 width=77) (actual time=0.190..0.190 rows=1 loops=1)Total runtime: 0.212 ms 
(2 rows)

decibel@workbook.local=# explain analyze EXECUTE statement;                                           QUERY PLAN
                                     
---------------------------------------------------------------------------------------------------Seq Scan on settings
(cost=0.00..1.06 rows=1 width=77) (actual time=0.013..0.015 rows=1 loops=1)  Filter: (lower(setting_name) = 'checks
disabled'::text)Totalruntime: 0.047 ms 
(3 rows)


See below for dump. I had hoped that since this was a SQL SRF in a FROM clause that it would basically be treated as a
macro.BTW, the real use case is that this function is called from within some other SQL functions that are then
executedin plpgsql functions that get executed very, very frequently. Worst-case I could pull the code all the way into
theplpgsql, but that's obviously very ugly. 

SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
CREATE SCHEMA test;
ALTER SCHEMA test OWNER TO decibel;
SET search_path = test, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
CREATE TABLE settings (  setting_name text NOT NULL,  b boolean,  f double precision,  i integer,  t text
);
ALTER TABLE test.settings OWNER TO cnuadmin;
COMMENT ON TABLE settings IS 'This is a seed table.';
CREATE FUNCTION setting(text) RETURNS settings  LANGUAGE sql  AS $_$
SELECT * FROM test.settings WHERE lower(setting_name) = lower($1)
$_$;
ALTER FUNCTION test.setting(text) OWNER TO cnuadmin;
COPY settings (setting_name, b, f, i, t) FROM stdin;
Asserts disabled    f    \N    \N    \N
Checks disabled    f    \N    \N    \N
Minimum assert level    \N    \N    0    \N
State Contract Numbering: Maximum Contracts Per Run    \N    \N    2000    \N
\.
ALTER TABLE ONLY settings  ADD CONSTRAINT settings__pk_setting_name PRIMARY KEY (setting_name);
COMMENT ON CONSTRAINT settings__pk_setting_name ON settings IS 'This PK is superfluous given the unique index, but
londistebitches without it.'; 
CREATE UNIQUE INDEX settings__setting_name ON settings USING btree (lower(setting_name));
--
Jim C. Nasby, Database Architect                   jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net