PostgreSQL空间索引(GiST)创建加速

术语介绍

GiST 索引 PostgreSQL数据库的一种通用索引 全称为Generalized Search Tree。

PostgreSQL 非常适合用来存贮管理空间数据 然而随着数据量的日益增长 性能问题也日益明显 本文介绍了如何加速亿级数据的空间索引创建。

硬件环境

阿里云 ECS。

CPU

内存

硬盘

操作系统

4 core *?2500 MHZ

8 GB

1T 高效云盘

最大吞吐 150MB/s

最大IOPS: 5000

随机IO延时 1-3 ms

x86_64 GNU/Linux

Centos 7

亿级数据测试

1、创建表并写入一亿点数据。

CREATE extension ganos_geometry cascade; 
CREATE TABLE test (id int, geom Geometry(Point, 4326), name text, code int);
INSERT INTO test SELECT i,
 ST_SetSRID(ST_MakePoint(random() * 180.0, random() * 90.0), 4326),
 text , 1
 FROM generate_series(1,10000 * 10000) AS i;

2、为几何数据创建空间索引

\timing on
CREATE index ON test using GiST(geom);

3、耗时统计

9254.94 S

使用Buffering模式加速

PostgreSQL 9.2 引入了 BUFFERING 创建索引功能 可减少创建索引时的频繁磁盘操作。

\timing on
CREATE index on test using GiST(geom) with (buffering on);

耗时统计

2975.41 S

开启 Buffering 模式后 创建索引耗时减少了 2/3 提速效果很明显。

并行创建GiST索引加速

如果你使用 PostgreSQL 创建过 BTree 索引 会发现BTree索引有并行创建的机制。

那 GiST 索引为什么不能并行创建加速呢

这是因为 BTree 索引的数据是可排序的 PostgreSQL 使用多个进程对数据进行归并排序 提高了 BTree 索引的创建效率。

然而空间 RTree 索引是不能直接排序的 例如下图 一个矩形中包含多个小矩形 如何给这些矩形排序呢

图1、 GiST RTree 索引

这里就要介绍一种几何空间降维的方法 即空间填充曲线。

使用空间填充曲线 按照特定规则 如Z-Order、Hilbert等 将多维空间降低成一维空间 在一维空间上进行排序 进而达到空间数据排序的目的。

图2、希尔伯特曲线示意图

使用 PolarDB/PostgreSQL 的 GiST Sort 功能并行创建空间索引

set max_parallel_maintenance_workers 4;
set maintenance_work_mem 1GB ;
set polar_enable_gist_sort true;
\timing on
CREATE index on test using GiST(geom) with (buffering on);

耗时统计:

214.09 S

没有看错 真的只用了 214 秒 相比第一组测试 加速了 43 倍。

说明

1、GiST Sort 功能已在 PolarDB/PostgreSQL 中上线 使用文档

https://help.aliyun.com/document_detail/212157.html

2、GiST Sort 功能只适合于 Point 类型数据。

在Line/Polygon类型的数据上使用 GiST Sort 创建索引 会导致查询性能降低。

本文转自网络，原文链接：https://developer.aliyun.com/article/783825
本站部分内容转载于网络，版权归原作者所有，转载之目的在于传播更多优秀技术内容，如有侵权请联系QQ/微信：153890879删除，谢谢！