PostgreSQL增量排序使用场景
在上一篇技术文章PostgreSQL17优化器改进(8)支持GiST和SP-GiST索引进行增量排序中介绍了PostgreSQL17对于增量排序的新功能，但是对于增量排序，哪些场景可能会用到增量排序？这确实是一个不错的写作点，因此就有了我下面的这边功能测试文章。
准备测试用例及数据
这里的测试是使用官网测试用例表，数据可以从源码包中获取。
CREATE TABLE tenk1 (
unique1 int4,
unique2 int4,
two int4,
four int4,
ten int4,
twenty int4,
hundred int4,
thousand int4,
twothousand int4,
fivethous int4,
tenthous int4,
odd int4,
even int4,
stringu1 name,
stringu2 name,
string4 name
);
导入测试数据
COPY tenk1 FROM '/data/tenk.data';
VACUUM ANALYZE tenk1;
在这里，为了能够使得测试用例使用到增量排序，只需要给four字段创建索引即可。
--创建索引
create index idx_tenk1_four on tenk1(four);
ORDER BY
常见的ORDER BY使用的场景有三个，除了ORDER BY单独使用外，也可以与limit或group by语法配合使用，在这些情况下，也是可以使用到增量排序的。
ORDER BY
测试的SQL语句
在该测试用例中，我们只指定ORDER BY子句。
explain select * from (select * from tenk1 order by four) t order by four, ten;
查看执行计划
在该执行计划中可见Incremental Sort节点，也就是使用到了增量排序。
testdb=# explain select * from (select * from tenk1 order by four) t order by four, ten;
QUERY PLAN
-------------------------------------------------------------------------
Incremental Sort (cost=1256.73..1823.85 rows=10000 width=244)
Sort Key: tenk1.four, tenk1.ten
Presorted Key: tenk1.four
-> Sort (cost=1109.39..1134.39 rows=10000 width=244)
Sort Key: tenk1.four
-> Seq Scan on tenk1 (cost=0.00..445.00 rows=10000 width=244)
(6 rows)
如果在测试过程中，执行计划中未使用Incremental Sort，可能需要设置work_mem的值；另外在测试完成后，记得需要reset该参数，避免对后续的测试影响。
set work_mem to '2MB';
reset work_mem;
ORDER BY+LIMIT
测试的SQL语句
在该测试用例中，我们使用ORDER BY子句，并添加limit子句配合使用。
explain select * from (select * from tenk1 order by four) t order by four, ten limit 1;
查看执行计划
在该执行计划中可见Incremental Sort节点，也就是使用到了增量排序。
testdb=# explain select * from (select * from tenk1 order by four) t order by four, ten limit 1;
QUERY PLAN
-------------------------------------------------------------------------------
Limit (cost=1128.14..1128.15 rows=1 width=244)
-> Incremental Sort (cost=1128.14..1309.47 rows=10000 width=244)
Sort Key: tenk1.four, tenk1.ten
Presorted Key: tenk1.four
-> Sort (cost=1109.39..1134.39 rows=10000 width=244)
Sort Key: tenk1.four
-> Seq Scan on tenk1 (cost=0.00..445.00 rows=10000 width=244)
(7 rows)
GROUP BY+ORDER BY
设置参数
--禁用enable_hashagg
set enable_hashagg =off;
测试的SQL语句
在该测试用例中，我们使用ORDER BY子句，并添加group by子句配合使用。
explain select four,ten,sum(two) from tenk1 group by four,ten order by four,ten,3;
查看执行计划
在该执行计划中可见Incremental Sort节点，也就是使用到了增量排序。
testdb=# explain select four,ten,sum(two) from tenk1 group by four,ten order by four,ten,3;
QUERY PLAN
------------------------------------------------------------------------------
Incremental Sort (cost=1111.91..1211.59 rows=40 width=16)
Sort Key: four, ten, (sum(two))
Presorted Key: four, ten
-> GroupAggregate (cost=1109.39..1209.79 rows=40 width=16)
Group Key: four, ten
-> Sort (cost=1109.39..1134.39 rows=10000 width=12)
Sort Key: four, ten
-> Seq Scan on tenk1 (cost=0.00..445.00 rows=10000 width=12)
(8 rows)
重置参数
测试完成后，需要reset参数为默认值。
reset enable_hashagg;
窗口函数
测试的SQL语句
在该测试用例中，我们使用窗口函数并且使用ORDER BY子句指定排序。
explain select * from (
select unique1,unique2,two,four,ten,thousand,
row_number() over(partition by four order by ten) as firt_rn,
row_number() over(partition by four order by ten desc) as last_rn
from tenk1) emp where firt_rn=1 or last_rn=1;
查看执行计划
在该执行计划中可见Incremental Sort节点，也就是使用到了增量排序。
testdb=# explain select * from (
testdb(# select unique1,unique2,two,four,ten,thousand,
testdb(# row_number() over(partition by four order by ten) as firt_rn,
testdb(# row_number() over(partition by four order by ten desc) as last_rn
testdb(# from tenk1) emp where firt_rn=1 or last_rn=1;
QUERY PLAN
------------------------------------------------------------------------------------------
Subquery Scan on emp (cost=1300.58..2323.85 rows=100 width=40)
Filter: ((emp.firt_rn = 1) OR (emp.last_rn = 1))
-> WindowAgg (cost=1300.58..2173.85 rows=10000 width=40)
-> Incremental Sort (cost=1300.50..1998.85 rows=10000 width=32)
Sort Key: tenk1.four, tenk1.ten
Presorted Key: tenk1.four
-> WindowAgg (cost=1109.41..1309.39 rows=10000 width=32)
-> Sort (cost=1109.39..1134.39 rows=10000 width=24)
Sort Key: tenk1.four, tenk1.ten DESC
-> Seq Scan on tenk1 (cost=0.00..445.00 rows=10000 width=24)
(10 rows)
DISTINCT
设置参数
--禁用enable_hashagg
set enable_hashagg =off;
SET enable_seqscan =off;
测试的SQL语句
在该测试用例中，我们只使用DISTINCT语法。DISTINCT是隐含需要排序的操作，可以不显示指定排序键。
EXPLAIN (costs off)
SELECT DISTINCT four, ten FROM tenk1;
查看执行计划
在该执行计划中可见Incremental Sort节点，也就是使用到了增量排序。
testdb=# EXPLAIN (costs off)
SELECT DISTINCT four, ten FROM tenk1;
QUERY PLAN
------------------------------------------------------
Unique
-> Incremental Sort
Sort Key: four, ten
Presorted Key: four
-> Index Scan using idx_tenk1_four on tenk1
(5 rows)
重置参数
测试完成后，需要reset参数为默认值。
reset enable_hashagg;
reset enable_seqscan;
UNION
准备测试用例表及数据
create table t_union (a int, b int, c int);
insert into t_union select mod(i,10),mod(i,10),i from generate_series(1,10000) s(i);
create index on t_union (a);
analyze t_union;
设置参数
set enable_hashagg =off;
测试的SQL语句
在该测试用例中，我们只使用DISTINCT语法。DISTINCT是隐含需要排序的操作，可以不显示指定排序键。
explain (costs off) select * from t union select * from t order by 1,3;
查看执行计划
在该执行计划中可见Incremental Sort节点，也就是使用到了增量排序。
testdb=# explain (costs off) select * from t union select * from t order by 1,3;
QUERY PLAN
---------------------------------------------------
Incremental Sort
Sort Key: t.a, t.c
Presorted Key: t.a
-> Unique
-> Merge Append
Sort Key: t.a, t.b, t.c
-> Sort
Sort Key: t.a, t.b, t.c
-> Seq Scan on t
-> Sort
Sort Key: t_1.a, t_1.b, t_1.c
-> Seq Scan on t t_1
(12 rows)
重置参数
测试完成后，需要reset参数为默认值。
reset enable_hashagg;
总结
在这里我们测试PostgreSQL增量排序的ORDER BY、窗口函数、DISTINCT、UNION等场景，在这里我们只是验证在这些场景下是可以支持增量排序的，但是对于测试使用的SQL的语法是否规范不做要求。在日常开发过程中系统中，处理大量的数据集时，尽量配合limit子句使用，可以大幅提升SQL的查询性能。后续我将会用另外一篇文章来介绍增量排序的原理及其最佳实践。
– / END / –
可以通过下面的方式联系我
如果这篇文章为你带来了灵感或启发，就请帮忙点赞、收藏、转发；如果文章中不严谨或者错漏之处，请及时评论指正。非常感谢！
扫一扫
420
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
