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

On Wed, Aug 11, 2010 at 4:47 PM, Brian Ceccarelli <bceccarelli@net32.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