Re: inlining SQL functions

Hi,

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 ? Is there an interest in expanding the class of SQL functions that can be inlined ?

Thanks,
--
Alexey Klyukin http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc

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.

regards, tom lane

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

Jim Nasby <decibel(at)decibel(dot)org> writes:
> On Apr 2, 2010, at 12:12 PM, Tom Lane wrote:
>> 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.

> Since Alexey was working on this for us, I'll elaborate. The actual
> use case is below.

There are two reasons why that function doesn't get inlined: it's not
declared to return SETOF, and it's (implicitly) declared VOLATILE.
If you make it SETOF and STABLE, it'll get inlined. Both of those
things change the semantics, so it's hard to do inlining otherwise.

regards, tom lane

On Apr 25, 2010, at 2:13 PM, Tom Lane wrote:
> Jim Nasby <decibel(at)decibel(dot)org> writes:
>> On Apr 2, 2010, at 12:12 PM, Tom Lane wrote:
>>> 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.
>
>> Since Alexey was working on this for us, I'll elaborate. The actual
>> use case is below.
>
> There are two reasons why that function doesn't get inlined: it's not
> declared to return SETOF, and it's (implicitly) declared VOLATILE.
> If you make it SETOF and STABLE, it'll get inlined. Both of those
> things change the semantics, so it's hard to do inlining otherwise.

That didn't seem to help, but possibly because I'm on 8.3 (see below). However, I've got some other questions...

A lot of what I'd like to use this for is simplifying lookups, ie:

plpgsql: v_disabled := test.setting_b( 'Checks disabled' );
or
SELECT ..., test.setting_b( 'Checks disabled' )

Where setting_b is FUNCTION(text) RETURNS boolean AS SELECT b FROM test.setting($1)

I would hope that would eventually get turned into...

SELECT ..., ( SELECT b FROM tools.settings WHERE ... )

I understand that's not the case now, but does it have to be that way? Perhaps if a function is defined SETOF we can allow it in the SELECT clause with the same restrictions we place on a subquery in the select clause (namely that you get an error if it returns multiple records).

I know this could be viewed as simply being syntactic sugar, but consider the options from my actual code:

IF tools.checks__disabled() OR p_condition THEN

or...

IF (current_database() NOT LIKE '%_prod' AND coalesce((SELECT b FROM tools.settings WHERE lower(setting_name) = lower('Checks Disabled')), false) OR p_condition THEN

And tools.checks__disabled() is used in more than one place, so this is even more important than syntactic sugar.

decibel(at)workbook(dot)local=# create or replace FUNCTION test.setting(text) RETURNS SETOF test.settings
STABLE LANGUAGE sql
AS $_$
SELECT * FROM test.settings WHERE lower(setting_name) = lower($1)
$_$;
CREATE FUNCTION
decibel(at)workbook(dot)local=# explain analyze SELECT * FROM test.setting( 'Checks disabled' );
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Function Scan on setting (cost=0.00..260.00 rows=1000 width=77) (actual time=0.160..0.160 rows=1 loops=1)
Total runtime: 0.176 ms
(2 rows)

decibel(at)workbook(dot)local=# explain analyze SELECT * FROM test.setting( 'Checks disabled' );
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Function Scan on setting (cost=0.00..260.00 rows=1000 width=77) (actual time=0.130..0.130 rows=1 loops=1)
Total runtime: 0.143 ms
(2 rows)

--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net