PostgreSQL Performance - SELECT vs Stored function -

i'm trying create stored function on postgresql improve performance , store big queries, , have call function after in code.

for example, if have function :

create or replace function test(max integer)  returns table (id integer) $$  select user.id user limit max;  $$ language sql stable; 

i call function see duration of query :

explain analyze select test(10); 

and function far slower same raw sql query ! thought stored function compiled , optimized @ creation. , if try bigger query, performance terrible function.

i think i'm doing wrong !

thank you,

the planner has problem query cannot evaluate execution time of function. in case planner gets estimated execution cost of function, can defined in create function... or alter function.... however, if try query:

explain analyse select * test(10); 

you see execution time being far more realistic.

compare:

test=# explain analyse select test(1000);                                         query plan ------------------------------------------------------------------------------------------  result  (cost=0.00..5.25 rows=1000 width=0) (actual time=0.830..1.220 rows=1000 loops=1)  planning time: 0.038 ms  execution time: 1.250 ms (3 rows) 

versus:

test=# explain analyse select * test(1000);                                                    query plan ----------------------------------------------------------------------------------------------------------------  limit  (cost=0.00..37.42 rows=1000 width=4) (actual time=0.006..0.124 rows=1000 loops=1)    ->  seq scan on test_table  (cost=0.00..2560.28 rows=68428 width=4) (actual time=0.005..0.102 rows=1000 loops=1)  planning time: 0.130 ms  execution time: 0.144 ms (4 rows)   test=# explain analyse select * test_table limit 1000;                                                     query plan ------------------------------------------------------------------------------------------------------------------  limit  (cost=0.00..37.42 rows=1000 width=269) (actual time=0.009..0.118 rows=1000 loops=1)    ->  seq scan on test_table  (cost=0.00..2560.28 rows=68428 width=269) (actual time=0.008..0.097 rows=1000 loops=1)  planning time: 0.076 ms  execution time: 0.151 ms (4 rows) 

note similarity of 2 last plans. table functions (functions return set of rows or table in case) should called in from clause. under conditions can inlined.

read more: inlining of sql functions.