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

Search Discussions

  • Tom Lane at Apr 2, 2010 at 5:13 pm

    Alexey Klyukin 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
  • Jim Nasby at Apr 25, 2010 at 7:00 pm

    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)
    Total runtime: 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)
    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@nasby.net
    512.569.9461 (cell) http://jim.nasby.net
  • Tom Lane at Apr 25, 2010 at 7:14 pm

    Jim Nasby 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
  • Jim Nasby at Apr 26, 2010 at 1:03 am

    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 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@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 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@workbook.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@nasby.net
    512.569.9461 (cell) http://jim.nasby.net

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedApr 2, '10 at 5:02p
activeApr 26, '10 at 1:03a
posts5
users3
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase