我正在尝试在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查询慢得多!我认为存储函数将在创建时进行编译和优化。如果我尝试使用更大的查询,函数的性能会很糟糕。
我想我可能做错了什么!
谢谢
计划器对您的查询有问题,因为它无法评估函数的执行时间。在这种情况下,计划器获得函数的估计执行成本,可以用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 函数的内联。