PostgreSQL性能-SELECT与存储函数

问题描述

我试图在PostgreSQL上创建一个存储函数以提高性能并存储大查询，而只需要在我的代码中调用该函数即可。

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);

该函数比同一个原始SQL查询要慢得多！我认为存储函数将在创建时进行编译和优化。而且，如果我尝试使用更大的查询，则函数会带来糟糕的性能。

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.

我认为我可能做错了！

谢谢

推荐答案

计划者的查询存在问题，因为它无法评估执行时间功能。在这种情况下，计划者将获得该函数的估计执行成本，可以在 create function ... 或 alter function ... 。但是，如果您尝试执行以下查询：

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.

比较：

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 子句中调用。在某些情况下，可以内联它们。

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.

了解更多：。

这篇关于PostgreSQL性能-SELECT与存储函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！