PostGIS性能提升100倍的秘密

PostGIS: 巧用大数定律和采样功能, 性能可提升百倍

当你面对海量数据库, 想对全局做个统计时, 如何提升性能?

数据库专业理工男的第一反应是: 因为要扫描所有数据, 得用列存储、得上向量化执行、得开启并行等等, 其实都是直面问题的好办法, 不能说不好.

但是, 还有更好的.

大数定律告诉我们, 当我们需要对海量数据库进行全貌统计时, 其实采样得到的结果乘以系数即可大概估计全貌情况, 而且准确度不错. 性能有可能提升百倍.

这就要用到数据库的采样语法了. 来看看下面这篇文章怎么玩的.

https://www.crunchydata.com/blog/postgis-performance-data-sampling

当数据库用户面对一个包含海量有趣数据的巨大表时，诱惑之一是运行查询来统计所有记录。举个例子, 你有十亿条测量数据？它们的平均值是多少？！

找到答案的一种方法就是直接计算平均值：

SELECT avg(value) FROM mytable;  

对于十亿条记录来说，这可能需要一些时间！

幸运的是， “大数定律”（Law of Large Numbers） 在这里拯救我们，它指出，随着样本量的增长，样本的平均值会接近总体（population）的平均值。令人惊讶的是，样本甚至不需要特别大就能相当接近总体平均值。

这里有一个包含1000万个值的表，这些值是从正态分布中随机生成的。我们知道平均值是零。那么，一个包含1万个值的样本会告诉我们平均值是多少呢？

CREATE TABLE normal AS  
  SELECT random_normal(0,1) AS values  
    FROM generate_series(1,10000000);  

我们可以使用排序（sort）或使用 random() 函数来获取样本，但这两种技术都会首先扫描整个表，而这正是我们想要避免的。

相反，我们可以使用 PostgreSQL 的 TABLESAMPLE 功能，快速获取表中页面的样本并估算平均值。

SELECT avg(values)  
  FROM normal TABLESAMPLE SYSTEM (1);  

我得到一个答案 —— 0.0031，与总体平均值非常接近 —— 并且只用了43毫秒。

这可以用于空间数据（spatial）吗？对于合适的数据来说，可以。想象一下，你有一个表，其中包含了加拿大（3600万人口）的每个人的一个点（point），而你想知道有多少人居住在多伦多（或多伦多周围的这个红色圆圈内）。

SELECT count(*)  
  FROM census_people  
  JOIN yyz  
    ON ST_Intersects(yyz.geom, census_people.geom);  

（注：ST_Intersects 是 PostGIS 的一个函数，用于检查几何图形是否相交。）

答案是 5,010,266，返回结果需要 7.2 秒。如果我们取 10% 的样本呢？

SELECT count(*)  
  FROM census_people TABLESAMPLE SYSTEM (10)  
  JOIN yyz  
    ON ST_Intersects(yyz.geom, census_people.geom);  

样本量为 10%，返回的答案是 508,292（接近我们实际测量值的大约十分之一），耗时 2.2 秒。那么 1% 的样本呢？

SELECT count(*)  
  FROM census_people TABLESAMPLE SYSTEM (1)  
  JOIN yyz  
    ON ST_Intersects(yyz.geom, census_people.geom);  

样本量为 1%，返回的答案是 50,379（接近我们实际测量值的大约百分之一），耗时 0.2 秒。仍然是一个很好的估计！

这是黑魔法吗？不，TABLESAMPLE SYSTEM（系统表采样）模式通过随机读取页面来获得速度。在我们最后一个示例中，它随机选择了 1% 的页面。下面是这在多伦多看起来的样子。

请特别注意圆圈外的郊区数据是如何斑驳不均的。表中的数据不是随机分布到页面的，它们是按顺序来自人口普查数据，并按顺序加载到数据库中。因此，对于任何给定的数据库页面，该页面中的实际行往往彼此靠近。

这个示例之所以有效，是因为数据量很大，并且我们正在汇总的区域占总数据量的很大一部分 —— 加拿大人口的七分之一居住在该圆圈内。

如果我们要汇总一个较小的区域，结果就不会那么理想了。

TABLESAMPLE SYSTEM（系统表采样）是一个强大的工具，但你必须确保任何给定的页面都包含你正在采样的数据的随机选择。我们的随机正态示例完美地工作了，因为数据是完全随机的。时间序列数据（time series data）的样本对于采样时间窗口（sample time windows）来说效果不佳（数据很可能是按到达顺序存储的），但可能适用于采样某些其他值。

采样功能的更多用法和场景, 参考阅读这些文章

• 《DuckDB select 语法糖: sample 采样查询》
• 《DB吐槽大会,第77期 - PG 不支持索引随机采样》
• 《PostgreSQL 随机查询采样 - 既要真随机、又要高性能 - table sample方法》
• 《PostgreSQL 随机采样应用 - table sample, tsm_system_rows, tsm_system_time》
• 《PostgreSQL 任意列组合条件 行数估算 实践 - 采样估算》
• 《秒级任意维度分析1TB级大表 - 通过采样估值满足高效TOP N等统计分析需求》
• 《PostgreSQL Oracle 兼容性 之 - 数据采样与脱敏》
• 《PostgreSQL 等待事件 及 等待采样统计(pg_wait_sampling)》
• 《PostgreSQL 巧妙的数据采样方法》

随机结果查询例子, 参考这些文章:

• 《PostgreSQL 随机查询优化》
• 《随机记录并发查询与更新(转移、删除)的"无耻"优化方法》
• 《PostgreSQL 内容随机推荐系统开发实践 - 文章随机推荐》
• 《PostgreSQL 随机记录返回 - 300倍提速实践 (随机数组下标代替order by random())》
• 《PostgreSQL 一条简单、“有人情味”的抽随机奖SQL》
• 《PostgreSQL 随机、唯一、有取值范围的序列生成器》
• 《PostgreSQL 索引算子下推扩展 - 索引内offset - 索引内过滤 - include index - 随机偏移》

随机偏移, 解决高并发冲突性能问题

• 《[直播]为什么打车和宇宙大爆炸有关?》
• 《PostgreSQL 网约车打车派单 高峰区域集中打车冲突优化1 - 宇宙大爆炸理论与PostgreSQL实践》

在数据库领域其实还有很多地方能看到统计的应用, 例如:

• 优化器常用的统计信息柱状图、most common value freq等
• hll, 用于估计唯一值. 例如uv, 滑动窗口分析等
• bloom

更多可参考我的github: https://github.com/digoal/blog