PostgreSQL PostGIS point join polygon (by ST_xxxx) - pglz_decompress 性能优化

本文涉及的产品
云原生数据库 PolarDB MySQL 版,通用型 2核4GB 50GB
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
简介: postgresql 数据库

背景
在空间数据中,通常会有轨迹、点、面的数据,假设有两张表,一张为面的表,一张为点的表,使用包含 ST_xxxx(c.geom, p.geom) 来进行JOIN(例如以面为单位,聚合统计点的数量)。

pic

本文介绍了空间JOIN的性能分析,瓶颈分析,优化方法。

原文
http://blog.cleverelephant.ca/2018/09/postgis-external-storage.html

例子
测试数据:

Setup

First download some polygons and some points.

Admin 0 - Countries

Populated Places

Load the shapes into your database.

shp2pgsql -s 4326 -D -I ne_10m_admin_0_countries.shp countries | psql performance

shp2pgsql -s 4326 -D -I ne_10m_populated_places.shp places | psql performance
包含大量POINT的空间对象

SELECT count(*)
FROM countries
WHERE ST_NPoints(geom) > (8192 / 16);
1、使用默认的压缩格式时,这个空间JOIN查询,耗时25秒。

SELECT count(*), c.name
FROM countries c
JOIN places p
ON ST_Intersects(c.geom, p.geom)
GROUP BY c.name;
使用PERF或oprofile跟踪其耗时的代码

《PostgreSQL 代码性能诊断之 - OProfile & Systemtap》

《PostgreSQL 源码性能诊断(perf profiling)指南 - 珍藏级》

发现问题是解压缩的pglz_decompress 接口造成的。

《TOAST,The Oversized-Attribute Storage Technique - 暨存储格式main, extended, external, plain介绍》

2、将空间字段改成非压缩格式,耗时降到4秒。

-- Change the storage type
ALTER TABLE countries
ALTER COLUMN geom
SET STORAGE EXTERNAL;

-- Force the column to rewrite
UPDATE countries
SET geom = ST_SetSRID(geom, 4326);

vacuum full countries;

-- Re-run the query
SELECT count(*), c.name
FROM countries c
JOIN places p
ON ST_Intersects(c.geom, p.geom)
GROUP BY c.name;
小结
1、代码层面的性能瓶颈分析方法,perf.

《PostgreSQL 源码性能诊断(perf profiling)指南 - 珍藏级》

2、PostGIS空间相关计算函数

http://postgis.net/docs/manual-dev/reference.html

3、数据库表级存储格式包括4种:

对于定长的字段类型,存储格式如下:

PLAIN
prevents either compression or out-of-line storage; furthermore it disables use of single-byte headers for varlena types. This is the only possible strategy for columns of non-TOAST-able data types.
对于变长的字段类型,除了可以使用PLAIN格式,还可以使用如下存储格式:

EXTENDED
allows both compression and out-of-line storage.
This is the default for most TOAST-able data types.
Compression will be attempted first, then out-of-line storage if the row is still too big.

EXTERNAL
allows out-of-line storage but not compression.
Use of EXTERNAL will make substring operations on wide text and bytea columns faster (at the penalty of increased storage space) because these operations are optimized to fetch only the required parts of the out-of-line value when it is not compressed.

MAIN
allows compression but not out-of-line storage.
(Actually, out-of-line storage will still be performed for such columns, but only as a last resort when there is no other way to make the row small enough to fit on a page.)
4、本文发现的瓶颈为变长字段,压缩后,解压缩的pglz_decompress 接口,所以将字段的存储格式改为非压缩格式,即提升了大量的性能。

参考
http://blog.cleverelephant.ca/2018/09/postgis-external-storage.html

http://postgis.net/docs/manual-dev/reference.html

《TOAST,The Oversized-Attribute Storage Technique - 暨存储格式main, extended, external, plain介绍》

《PostgreSQL 源码性能诊断(perf profiling)指南 - 珍藏级》

《PostgreSQL 代码性能诊断之 - OProfile & Systemtap》
转自阿里云德哥

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
2月前
|
关系型数据库 数据库 PostgreSQL
深入理解 PostgreSQL 的 JOIN 连接
深入理解 PostgreSQL 的 JOIN 连接
131 4
|
2月前
|
关系型数据库 PostgreSQL Docker
PostgreSQL - 01 PostgreSQL + PostGIS + Docker 空间计算!判断坐标点是否在某个区域中 POINT MULTIPOLYGON ST_Contains
PostgreSQL - 01 PostgreSQL + PostGIS + Docker 空间计算!判断坐标点是否在某个区域中 POINT MULTIPOLYGON ST_Contains
36 0
|
7月前
|
关系型数据库 数据库 PostgreSQL
Docker【应用 03】给Docker部署的PostgreSQL数据库安装PostGIS插件(安装流程及问题说明)
Docker【应用 03】给Docker部署的PostgreSQL数据库安装PostGIS插件(安装流程及问题说明)
414 0
|
7月前
|
SQL 关系型数据库 PostgreSQL
PostgreSQL【部署 01】离线安装PostgreSQL+PostGIS踩坑及问题解决经验分享(含安装文件PostgreSQL+PostGIS及多个依赖+测试SQL)
PostgreSQL【部署 01】离线安装PostgreSQL+PostGIS踩坑及问题解决经验分享(含安装文件PostgreSQL+PostGIS及多个依赖+测试SQL)
785 0
|
关系型数据库 测试技术 分布式数据库
PolarDB | PostgreSQL 高并发队列处理业务的数据库性能优化实践
在电商业务中可能涉及这样的场景, 由于有上下游关系的存在, 1、用户下单后, 上下游厂商会在自己系统中生成一笔订单记录并反馈给对方, 2、在收到反馈订单后, 本地会先缓存反馈的订单记录队列, 3、然后后台再从缓存取出订单并进行处理. 如果是高并发的处理, 因为大家都按一个顺序获取, 容易产生热点, 可能遇到取出队列遇到锁冲突瓶颈、IO扫描浪费、CPU计算浪费的瓶颈. 以及在清除已处理订单后, 索引版本未及时清理导致的回表版本判断带来的IO浪费和CPU运算浪费瓶颈等. 本文将给出“队列处理业务的数据库性能优化”优化方法和demo演示. 性能提升10到20倍.
841 4
|
关系型数据库 PostgreSQL
PostgreSQL 性能优化: 等待事件
等待事件是 PostgreSQL 的重要优化工具。当您能查明会话为什么在等待资源以及会话在做什么时,您就能更好地减少瓶颈。您可以使用本节中的信息来查找可能的原因和纠正措施。
277 0
|
关系型数据库 PostgreSQL
PostgreSQL 性能优化: EXPLAIN 使用教程
PostgreSQL为每个收到的查询产生一个查询计划。选择正确的计划来匹配查询结构和数据的属性对于好的性能来说绝对是最关键的,因此系统包含了一个复杂的规划器来尝试选择好的计划。你可以使用EXPLAIN命令察看规划器为任何查询生成的查询计划。
236 0
|
关系型数据库 PostgreSQL 索引
PostgreSQL 性能优化: 执行计划
PostgreSQL为每个收到的查询产生一个查询计划。查询计划的结构是一个计划结点的树。最底层的结点是扫描结点:它们从表中返回未经处理的行。不同的表访问模式有不同的扫描结点类型:顺序扫描、索引扫描、位图索引扫描。也还有不是表的行来源,例如VALUES子句和FROM中返回集合的函数,它们有自己的结点类型。如果查询需要连接、聚集、排序、或者在未经处理的行上的其它操作,那么就会在扫描结点之上有其它额外的结点来执行这些操作。并且,做这些操作通常都有多种方法,因此在这些位置也有可能出现不同的结点类型。
125 0
|
缓存 运维 网络协议
PostgreSQL 性能优化和体系化运维(一)|学习笔记
快速学习 PostgreSQL 性能优化和体系化运维(一)
410 0
|
SQL 关系型数据库 Unix