PostgreSQL17优化器改进(8)支持GiST和SP-GiST索引进行增量排序

原创墨竹 All China Database Union 2024-08-20

595

PostgreSQL17优化器改进(8)支持GiST和SP-GiST索引进行增量排序

增量排序最早是在PostgreSQL 13中引入的，但到目前为止，它只用于B-tree索引。现在，在PostgreSQL17中实现了对GiST和SP-GiST索引的增量排序的支持，适用于sql语句中含有一个LIMIT子句和ORDER BY子句，对于ORDER BY子句的排序列，其中第一列具有GiST和SP-GiST索引，而其他列没有，对于该查询语句是特别有用的。下面我们开始测试并观察两个版本数据库中执行计划的变化。

功能测试

准备测试用例

在PostgreSQL16.3和PostgreSQL17.0Beta 1版本数据库导入demo测试数据，demo的数据库可以从下面的地址自行下载。

https://postgrespro.com/community/demodb

在这里我们先根据机场坐标创建一个GiST索引:

CREATE INDEX ON airports_data USING gist (coordinates);

模拟场景：从离给定点最近的机场(例如，坐标为10和50)找到10个航班，并按出发日期排序

--airports是基于airports_data表创建的视图
EXPLAIN (costs off)
SELECT f.*
FROM flights f
         JOIN airports a ON (f.departure_airport=a.airport_code)
ORDER BY point(10,50) <-> a.coordinates, f.scheduled_departure
LIMIT 10;

PostgreSQL16.3的执行计划

在PostgreSQL16.3的客户端中执行sql语句

demo=# EXPLAIN (costs off)
demo-# SELECT f.*
demo-# FROM flights f
demo-#          JOIN airports a ON (f.departure_airport=a.airport_code)
demo-# ORDER BY point(10,50) <-> a.coordinates, f.scheduled_departure
demo-# LIMIT 10;
                                    QUERY PLAN                                    
----------------------------------------------------------------------------------
 Limit
   ->  Sort
         Sort Key: (('(10,50)'::point <-> ml.coordinates)), f.scheduled_departure
         ->  Hash Join
               Hash Cond: (f.departure_airport = ml.airport_code)
               ->  Seq Scan on flights f
               ->  Hash
                     ->  Seq Scan on airports_data ml
(8 rows)

Time: 1.070 ms

从PostgreSQL16.3的执行计划，可以看到对airports_data表是顺序扫描。

PostgreSQL17.0Beta 1的执行计划

在PostgreSQL17.0Beta 1的客户端中执行sql语句

demo=# EXPLAIN (costs off)
demo-# SELECT f.*
demo-# FROM flights f
demo-#          JOIN airports a ON (f.departure_airport=a.airport_code)
demo-# ORDER BY point(10,50) <-> a.coordinates, f.scheduled_departure
demo-# LIMIT 10;
                                      QUERY PLAN                                      
--------------------------------------------------------------------------------------
 Limit
   ->  Incremental Sort
         Sort Key: (('(10,50)'::point <-> ml.coordinates)), f.scheduled_departure
         Presorted Key: (('(10,50)'::point <-> ml.coordinates))
         ->  Nested Loop
               Join Filter: (ml.airport_code = f.departure_airport)
               ->  Index Scan using airports_data_coordinates_idx on airports_data ml
                     Order By: (coordinates <-> '(10,50)'::point)
               ->  Materialize
                     ->  Seq Scan on flights f
(10 rows)

Time: 0.789 ms

从PostgreSQL17.0Beta 1的执行计划，可以看到对airports_data表使用的是GiST索引扫描，同时也有Incremental Sort节点出现。另外sql执行时间为0.789ms。

总结

通过PostgreSQL17.0对于增量排序的优化，目前可以适用于B-tree、GiST和SP-GiST索引。对于GiST和SP-GiST索引的使用场景**包含有一个LIMIT子句和ORDER BY子句，对于ORDER BY子句的排序列，其中第一列具有GiST和SP-GiST索引，而其他列没有的情况特别有用。**我们也可以通过两个版本的sql执行耗时比对，在使用GiST索引后，sql的查询性能有较大幅度的提升。