Обсуждение: inlining SQL functions

Поиск
Список
Период
Сортировка

inlining SQL functions

От
Alexey Klyukin
Дата:
Hi,

Is there a reason why only a table free SQL functions are allowed to be inlined ?  I wonder why a simple SQL function
containingonly a SELECT * FROM table can't be expanded inline ? Is there an interest in expanding the class of SQL
functionsthat can be inlined ?   

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



Re: inlining SQL functions

От
Tom Lane
Дата:
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.
        regards, tom lane


Re: inlining SQL functions

От
Jim Nasby
Дата:
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




Re: inlining SQL functions

От
Tom Lane
Дата:
Jim Nasby <decibel@decibel.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


Re: inlining SQL functions

От
Jim Nasby
Дата:
On Apr 25, 2010, at 2:13 PM, Tom Lane wrote:
> Jim Nasby <decibel@decibel.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
allowit in the SELECT clause with the same restrictions we place on a subquery in the select clause (namely that you
getan 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('ChecksDisabled')), 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@workbook.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@workbook.local=# explain analyze  SELECT * FROM test.setting( 'Checks disabled' );
                 QUERY PLAN                                                  
------------------------------------------------------------------------------------------------------------Function
Scanon 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@workbook.local=# explain analyze  SELECT * FROM test.setting( 'Checks disabled' );
                 QUERY PLAN                                                  
------------------------------------------------------------------------------------------------------------Function
Scanon 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@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net