Re: inlining SQL functions

On Apr 2, 2010, at 12:12 PM, Tom Lane wrote:
> Alexey Klyukin <alexk(at)commandprompt(dot)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(at)workbook(dot)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(at)workbook(dot)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)
Total runtime: 0.026 ms
(3 rows)

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

decibel(at)workbook(dot)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(at)workbook(dot)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)
Total runtime: 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 executed in plpgsql functions that get executed very, very frequently. Worst-case I could pull the code all the way into the plpgsql, 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 londiste bitches without it.';
CREATE UNIQUE INDEX settings__setting_name ON settings USING btree (lower(setting_name));
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net