PostgreSQL Performance - SELECT vs Stored function

Question

I'm trying to create a stored function on PostgreSQL to improve performance and to store big queries, and just have to call the function after in my code.

For example, if I have a function :

CREATE OR REPLACE FUNCTION test(max integer) 
RETURNS TABLE (id integer) AS $$ 
SELECT User.id
FROM User
LIMIT max; 
$$ LANGUAGE sql STABLE;

I call the function like that to see the duration of the query :

EXPLAIN ANALYZE SELECT test(10);

And the function is far slower than the same raw SQL query ! I thought a stored function would be compiled and optimized at creation. And if I try with a bigger query, performance are terrible with a function.

I think I'm probably doing something wrong !

Thank you,

Answer 1

The planner has a problem with your query as it cannot evaluate execution time of the function. In this case the planner gets the estimated execution cost of the function, which can be defined in create function... or alter function.... However, if you try this query:

explain analyse select * from test(10);

you will see the 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 * from 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 * from 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 the similarity of the two last plans. Table functions (functions that return set of rows or table like in this case) should be called in FROM clause. Under certain conditions they can be inlined.

Read more: Inlining of SQL functions.