Re: BUG #5611: SQL Function STABLE promoting to VOLATILE

On Wed, Aug 11, 2010 at 4:47 PM, Brian Ceccarelli <bceccarelli(at)net32(dot)com> wrote:
> Please show me an example where an inline query gets a performance boost.

Sure.

rhaas=# create table example as select a from
generate_series(1,100000) a; SELECT 100000
rhaas=# alter table example add primary key (a);
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index
"example_pkey" for table "example"
ALTER TABLE
rhaas=# create function f() returns setof int as $$select a from
example$$ language sql stable;
rhaas=# explain analyze select * from f() where f = 1;
QUERY
PLAN
----------------------------------------------------------------------------------------------------------------------
Index Scan using example_pkey on example (cost=0.00..8.28 rows=1
width=4) (actual time=0.102..0.103 rows=1 loops=1)
Index Cond: (a = 1)
Total runtime: 0.149 ms
(3 rows)
rhaas=# alter function f() volatile;
ALTER FUNCTION
rhaas=# explain analyze select * from f() where f = 1;
QUERY PLAN
---------------------------------------------------------------------------------------------------
Function Scan on f (cost=0.25..12.75 rows=5 width=4) (actual
time=34.585..51.972 rows=1 loops=1)
Filter: (f = 1)
Total runtime: 63.277 ms
(3 rows)

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company