PostgreSQL:与sql相比，plpgsql语言的相同请求速度较慢

Question

我是PostgreSQL的新手，我面临着一个关于表函数性能的问题。我需要做的相当于MSSQL中的存储过程。经过一些研究，我发现表函数是可行的，所以我举了一个例子来使用plpgsql创建我的函数。

通过比较执行时间，使用函数比直接调用查询慢2倍(函数中的查询完全相同(。

经过一点挖掘，我发现在函数中使用SQL语言大大缩短了执行时间(与调用查询的时间完全相同(。读过这篇文章后，我明白plpgsql增加了一点开销，但差异太大，无法解释。

由于我没有使用任何plpgsql功能，所以这个解决方案对我来说很好，完全有意义。然而，我想了解为什么会有这样的差异。如果我比较执行计划，plpgsql版本会执行一些HashAggregate和顺序搜索，而SQL版本会执行GroupAggregate并进行一些预排序。。。我确实按照Laurenz Albe的建议使用了auto_explain，并在最后添加了两个执行计划。

为什么在执行计划上有这样的差异，同一个查询只有语言上的差异？此外，即使是SUM的结果(见下面的请求(也有显著的差异。我知道我使用的是浮点值，所以每次调用的结果可能会有点不同，但在这种情况下，查询和函数之间的差异大约为3，这是出乎意料的(~10001比~9998(。

下面的代码使用2个表和2个函数来重现问题。

请注意，我使用的是PostgreSQL 12。

感谢您的解释：(谢谢。

-- Step 1: Create database
-- Step 2: Create tables
-- table1
CREATE TABLE public.table1(area real, code text COLLATE pg_catalog."default");
-- table 2
CREATE TABLE public.table2(code text COLLATE pg_catalog."default" NOT NULL, surface real, CONSTRAINT table2_pkey PRIMARY KEY (code));
-- Step 3: create functions
-- plpgsql
CREATE OR REPLACE FUNCTION public.test_function()
RETURNS TABLE(code text, value real) 
LANGUAGE 'plpgsql'
COST 100
VOLATILE 
ROWS 1000
AS $BODY$
BEGIN
RETURN QUERY 

SELECT table2.code, (case when (sum(area) * surface) IS NULL then 0 else (sum(area) * surface) end) AS value
FROM table1 
INNER JOIN table2 on table1.code = table2.code 
GROUP BY table2.code, surface
;
END;
$BODY$;
-- sql
CREATE OR REPLACE FUNCTION public.test_function2()
RETURNS TABLE(code text, value real) 
LANGUAGE SQL
AS $BODY$
SELECT table2.code, (case when (sum(area) * surface) IS NULL then 0 else (sum(area) * surface) end) AS value
FROM table1 
INNER JOIN table2 on table1.code = table2.code 
GROUP BY table2.code, surface
$BODY$;
-- Step 4: insert some random data
-- table 2
INSERT INTO table2(code, surface) VALUES ('AAAAA', 1);
INSERT INTO table2(code, surface) VALUES ('BBBBB', 1);
INSERT INTO table2(code, surface) VALUES ('CCCCC', 1);
INSERT INTO table2(code, surface) VALUES ('DDDDD', 1);
INSERT INTO table2(code, surface) VALUES ('EEEEE', 1);
-- table1 (will take some time, this simulate my current query with 10 millions rows)
DO
$$
DECLARE random_code text;
DECLARE code_count int := (SELECT COUNT(*) FROM table2);
BEGIN
FOR i IN 1..10000000 LOOP
random_code := (SELECT code FROM table2 OFFSET floor(random() * code_count) LIMIT 1);       
INSERT INTO public.table1(area, code) VALUES (random() / 100, random_code);
END LOOP;
END
$$  
-- Step 5: compare
SELECT * FROM test_function()
SELECT * FROM test_function2() -- 2 times faster

test_function执行计划(plpgsql版本(

2021-04-14 11:52:10.335 GMT [5056] LOG:  duration: 3808.919 ms  plan:
Query Text: SELECT table2.code, (case when (sum(area) * surface) IS NULL then 0 else (sum(area) * surface) end) AS value
FROM table1 
INNER JOIN table2 on table1.code = table2.code 
GROUP BY table2.code, surface
HashAggregate  (cost=459899.03..459918.08 rows=1270 width=40) (actual time=3808.908..3808.913 rows=5 loops=1)
Group Key: table2.code
Buffers: shared hit=34 read=162130
->  Hash Join  (cost=38.58..349004.15 rows=14785984 width=40) (actual time=215.340..2595.247 rows=10000000 loops=1)
Hash Cond: (table1.code = table2.code)
Buffers: shared hit=34 read=162130
->  Seq Scan on table1  (cost=0.00..310022.84 rows=14785984 width=10) (actual time=215.294..1036.615 rows=10000000 loops=1)
Buffers: shared hit=33 read=162130
->  Hash  (cost=22.70..22.70 rows=1270 width=36) (actual time=0.019..0.020 rows=5 loops=1)
Buckets: 2048  Batches: 1  Memory Usage: 17kB
Buffers: shared hit=1
->  Seq Scan on table2  (cost=0.00..22.70 rows=1270 width=36) (actual time=0.013..0.014 rows=5 loops=1)
Buffers: shared hit=1
2021-04-14 11:52:10.335 GMT [5056] CONTEXT:  PL/pgSQL function test_function() line 3 at RETURN QUERY

test_function2(sql版本(的执行计划

2021-04-14 11:54:24.122 GMT [5056] LOG:  duration: 1513.001 ms  plan:
Query Text: 
SELECT table2.code, (case when (sum(area) * surface) IS NULL then 0 else (sum(area) * surface) end) AS value
FROM table1 
INNER JOIN table2 on table1.code = table2.code 
GROUP BY table2.code, surface

Finalize GroupAggregate  (cost=271918.31..272252.77 rows=1270 width=40) (actual time=1484.846..1512.998 rows=5 loops=1)
Group Key: table2.code
Buffers: shared hit=96 read=162098
->  Gather Merge  (cost=271918.31..272214.67 rows=2540 width=40) (actual time=1484.840..1512.990 rows=15 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=96 read=162098
->  Sort  (cost=270918.29..270921.46 rows=1270 width=40) (actual time=1435.897..1435.899 rows=5 loops=3)
Sort Key: table2.code
Sort Method: quicksort  Memory: 25kB
Worker 0:  Sort Method: quicksort  Memory: 25kB
Worker 1:  Sort Method: quicksort  Memory: 25kB
Buffers: shared hit=96 read=162098
->  Partial HashAggregate  (cost=270840.11..270852.81 rows=1270 width=40) (actual time=1435.857..1435.863 rows=5 loops=3)
Group Key: table2.code
Buffers: shared hit=74 read=162098
->  Hash Join  (cost=38.58..240035.98 rows=6160827 width=40) (actual time=204.916..1022.133 rows=3333333 loops=3)
Hash Cond: (table1.code = table2.code)
Buffers: shared hit=74 read=162098
->  Parallel Seq Scan on table1  (cost=0.00..223771.27 rows=6160827 width=10) (actual time=204.712..486.850 rows=3333333 loops=3)
Buffers: shared hit=65 read=162098
->  Hash  (cost=22.70..22.70 rows=1270 width=36) (actual time=0.155..0.156 rows=5 loops=3)
Buckets: 2048  Batches: 1  Memory Usage: 17kB
Buffers: shared hit=3
->  Seq Scan on table2  (cost=0.00..22.70 rows=1270 width=36) (actual time=0.142..0.143 rows=5 loops=3)
Buffers: shared hit=3
2021-04-14 11:54:24.122 GMT [5056] CONTEXT:  SQL function "test_function2" statement 1

Answer 1

首先，一般讨论如何在这种情况下获得执行计划

为了弄清真相，激活auto_explain并跟踪postgresql.conf:中的功能执行

shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = 0
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_nested_statements = on
track_functions = 'pl'

然后重新启动数据库。不要在繁忙的生产数据库上这样做，因为它会记录大量日志并增加相当大的开销！

使用重置数据库统计信息

SELECT pg_stat_reset();

现在，函数中所有SQL语句的执行计划都会被记录下来，PostgreSQL会跟踪函数的执行时间。

当从SQL函数和PL/pgSQL函数调用时，查看语句的执行计划和执行时间，看看是否可以发现差异。然后比较pg_stat_user_functions中的执行时间以比较函数执行时间。

查看执行计划后，对当前案例进行解释

从PL/pgSQL运行的查询没有并行化。由于实现中的限制，使用RETURN QUERY运行的查询从来都不是。