开发者社区> pg小助手> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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

简介: 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》
转自阿里云德哥

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
前端 fetchMetadata: sill fetchPackageMetaData error for detec卡住解决办法
前端 fetchMetadata: sill fetchPackageMetaData error for detec卡住解决办法
184 0
用kotlin来实现dsl风格的编程
用kotlin来实现dsl风格的编程
62 0
when is backend date format set in Javascript DateFormat
Created by Wang, Jerry, last modified on Oct 27, 2015
41 0
Linux必知词汇:兼容分时系统(Compatible Time-Sharing System,CTSS)
Linux必知词汇:兼容分时系统(Compatible Time-Sharing System,CTSS)
392 0
The frequent used operation in Linux system
The frequently used operation in Linux system    2017-04-08 12:48:09    1. mount the hard disk:    #: fdisk -l     %% use this operation to check how many and what disk it found in the computer.
613 0
JavaScript decodeURIComponent Uncaught URIError: URI malformed
使用 JavaScript 的 decodeURIComponent 方法解码使用 Java 的 URLEncoder.encode 方法编码的字符串时,产生错误: Uncaught URIError: URI malformed 产生错误的原因: 1、是使用 Java 的 URLEncoder.encode 方法编码的字符串时使用的方法姿势不对。
2235 0
href="javascript:xxx(this);"和onclick="javascript:xxx(this);"的区别
原文: href="javascript:xxx(this);"和onclick="javascript:xxx(this);"的区别 href="javascript:xxx(this);"和onclick="javascript:xxx(this);...
792 0
+关注
21
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载