postgresql , 空间数据 , r-tree , 聚集存储
单身汉的房间可能是这样的,凌乱得很。
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicGcq5yMwAzXjlGcfFDMfVDM5AzNxAjMvwVOwcTMwIzLcJXZ0NXYt9CX3Fmcvw1ZvxmYvwFbh92ZpR2Lc12bj5iY1hGdpd2Lc9CX6MHc0RHaiojIsJye.jpg)
可能会有人表示不服,不是应该这样的吗?
实际上大多数情况下数据库存储也可能是这样的,数据凌乱。索引的目的是在凌乱的数据中找到目标数据,但是从凌乱的数据中按索引顺序扫描一批数据,会有什么后果呢:
没错,io放大,我在以前的文章中有详细介绍过其原理。
<a href="https://github.com/digoal/blog/blob/master/201404/20140426_01.md">《索引顺序扫描引发的堆扫描io放大背后的统计学原理与解决办法 - postgresql index scan enlarge heap page scans when index and column correlation small.》</a>
对于乱序存放的数据,如果按索引顺序来调整存储,聚集后,可以减少io放大。
对于空间数据,应该如何存放呢?
postgresql 插件有一个插件pageinspect,可以解析block查看索引、堆表的内容。
但是一直没有支持gist, sp-gist的内窥,不过马上就会支持了,看这个patch。
<a href="https://www.postgresql.org/message-id/flat/accae316-5e4d-8963-0c3d-277ef13c396c%40postgrespro.ru#[email protected]">https://www.postgresql.org/message-id/flat/accae316-5e4d-8963-0c3d-277ef13c396c%40postgrespro.ru#[email protected]</a>
1、打补丁
2、加载pageinspect插件
3、几个函数接口的讲解
3.1 查看gist索引概貌,比如层级,占用多少个page,多少叶子节点,多少tuple等。
gist_stat(indexname) - show some statistics about gist tree
3.2 列出gist索引的每个层级的概貌,直到某个层级的节点。
gist_tree(indexname,maxlevel) - show gist tree up to maxlevel
3.3 打印gist索引的详细内容,这里包含了每一个索引的区间,例如国家、省、市。。。box边界。
gist_print(indexname) - prints objects stored in gist tree, works only if objects in index have textual representation (type_out functions should be implemented for given object type).
it's known to work with r-tree gist based index (contrib/rtree_gist).
note, in example below, objects are of type box.
我们可以把gist索引的内容,按层级绘制出图像来。
例如一级长这样,就是大box。
下一级(叶子节点),长这样,就是大box下的一个个小box。
如果我们按照gist索引的层级排序,聚集存储,那么在基于空间扫描数据的时候,扫描的块就更少。
假设落在杭州地区有100万条数据,按照单身汉的乱序存储,杭州的数据可能散落分布在很多个page中,我们扫描时也许扫描的是全量数据,而且是离散扫,性能损耗比较大。如果按照gist聚集存储,那么会有极大的性能提升。
1、创建测试表
2、写入1000万测试记录,随机点位
3、创建空间索引
4、内窥空间索引
4.1、末端(叶子)节点的box如下。
5、空间聚集前,也就是从单身汉的凌络卧室空间扫描落在某一个box内的数据。
返回101518条记录,扫描了50914个heap数据块。
6、按gist空间聚集,整理数据。
postgresql提供了一个聚集语法:
使用gist索引对数据进行聚集:
验证聚集后的块扫描性能。
返回101518条记录,扫描了691个heap数据块。
使用空间聚集,按某个空间条件查询并返回101518记录,对比聚集前后,扫描的heap数据块数目从50914降到了691。
<a href="http://www.sai.msu.su/~megera/wiki/gevel">http://www.sai.msu.su/~megera/wiki/gevel</a>
<a href="http://www.sai.msu.su/~megera/wiki/rtree_index">http://www.sai.msu.su/~megera/wiki/rtree_index</a>