Re: [HACKERS] inlining SQL functions
On Apr 2, 2010, at 12:12 PM, Tom Lane wrote:
Alexey Klyukin al...@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
deci...@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)
deci...@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??):
deci...@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)
deci...@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 disabledf \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 j...@nasby.net
512.569.9461 (cell) http://jim.nasby.net
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] inlining SQL functions
Jim Nasby deci...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] inlining SQL functions
On Apr 25, 2010, at 2:13 PM, Tom Lane wrote:
Jim Nasby deci...@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.
deci...@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
deci...@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)
deci...@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 j...@nasby.net
512.569.9461 (cell) http://jim.nasby.net
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] inlining SQL functions
Alexey Klyukin al...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
