PostgreSQL Performance - SELECT vs Saved Function

Question

I am trying to create a stored function in PostgreSQL to improve performance and store large queries, and I just need to call the function after 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 a function like this to see the duration of the request:

EXPLAIN ANALYZE SELECT test(10);

And the function is much slower than the same original SQL query! I thought that the saved function would be compiled and optimized upon creation. And if I try with a big request, the performance will be terrible with the function.

I think I'm probably something wrong!

Thank,

Answer 1

, . , create function... alter function.... , :

explain analyse select * from test(10);

, .

:

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)

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)

. (, , ) FROM. .

: SQL.