PostgreSQL 性能 - SELECT 与存储函数

Question

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

例如，如果我有一个函数:

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

我调用这样的函数来查看查询的持续时间:

EXPLAIN ANALYZE SELECT test(10);

而且该函数比相同的原始 SQL 查询慢得多!我认为存储函数会在创建时被编译和优化。如果我尝试使用更大的查询，函数的性能会很糟糕。

我想我可能做错了什么!

谢谢，

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 子句中调用表函数(在本例中返回行集或表的函数)。在某些情况下，它们可以内联。

阅读更多:Inlining of SQL functions .