Open liujiusheng opened 5 years ago
更新索引： VACUUM public."jibennongtianshuju" 在未建立索引时count函数统计140万永久基本农田数据大概需要12s，建立索引后查询都走了缓存，hit值很高，查询效率明显提高，只需要约140ms。explain下可以明显看到查询走了索引。
VACUUM public."jibennongtianshuju"
可以理解为建立常规的平衡二叉树，建立的索引会比较精确，几乎精确到了任意一条数据，所以索引的体积会比较大
CREATE INDEX [indexname] ON [tablename] USING GIST ( [geometryfield] );
建立不平衡树结构，包括四叉树等。实际生成过程中索引没有生成成功 CREATE INDEX [indexname] ON [tablename] USING SPGIST ( [geometryfield] );
CREATE INDEX [indexname] ON [tablename] USING SPGIST ( [geometryfield] );
适用于一个字段的内容本来为组合式内容，但是要做查询的场景，它的作用机理与mapbox的编码差不多，都是对数据进行拆分，然后对每一个关键字进行单独编码建立索引
空间数据里面使用比较合适，它的作用机理由数据的组织方式决定，即前后n条数据如果存储在硬盘的相邻区域就把它做为一个块，如此反复生成很多个小块，加快查询的效率。实际上地理数据生产的时候也是按区域进行数字化的，空间关联度很高所以很适合用这种方式的索引。
CREATE INDEX [indexname] ON [tablename] USING BRIN ( [geometryfield] );
索引比较粗略，只精确到块级，不会到具体的数据，所以索引体积比较小，实际使用中发现索引的查询速度也没有GIST快。猜测在数据量没有到极其巨大的时候它的索引优势并不能体现出来。
ST_Collect合并多个要素时不会检查自相交，ST_Union会做自相交检查，ST_Collect运行效率是ST_Union的近百倍
使用node.js写的查询比数据库管理工具速度要快很多，排查发现是我个人电脑上有这个问题，只要是基于jdbc的查询都要比别人慢4倍，与java和数据库工具都没有关系。
子查询会导致索引不被使用
查询语句前面加上explain analyze可以显示出查询计划和实际查询需要的时间，便于做sql优化，如果查询中启用了索引的也可以看到有使用index的标识
explain analyze
判断空间数据是否为正确的2d数据 st_isvalid(geom)='f'的原因，可通过ST_IsValidReason函数查看： select ST_IsValidReason(geom) from public."xianzhuangyongdi" where st_isvalid(geom)='f'; ST_IsValidDetail函数也可以辅助于调试
ST_Normalize、ST_MakeValid函数有助于解决此类问题
ST_Normalize会将一个面的自相交处理一遍，使之不自相交 ST_MakeValid更倾向于把一个自相交的面切分成两个面，似乎无法直接再写入表的本字段，需要进一步测试明确
postgresql数据库默认会使用缓存，并且这个缓存功能是无法关闭的，最低只能调到128KB。此时缓存命中率会非常低，对测试结果基本没有影响。
也可以使用pg_dropcache pg数据库插件实现缓存清除，未尝试过
也可以使用以下命令强制清除磁盘上的缓存（未实验过）
service postgresql stop sync echo 3 > /proc/sys/vm/drop_caches service postgresql start
查询空间数据的坐标系： select st_srid(geom) from "DS_YJJBNT";
select st_srid(geom) from "DS_YJJBNT";
到底地理坐标数据导入pg后st_area计算面积单位默认是米还是投影坐标计算出的面积默认是米？
在postgis中计算时geometry指投影坐标系，geography指使用地理坐标系
ST_Covers的效率比ST_CoveredBy高，ST_Contains(A,B) = ST_Covers(A, B) = ST_Within(B,A)，
https://postgis.net/docs/reference.html#PostGIS_Types
各种索引
更新索引：
VACUUM public."jibennongtianshuju"在未建立索引时count函数统计140万永久基本农田数据大概需要12s，建立索引后查询都走了缓存，hit值很高，查询效率明显提高，只需要约140ms。explain下可以明显看到查询走了索引。GiST 索引
可以理解为建立常规的平衡二叉树，建立的索引会比较精确，几乎精确到了任意一条数据，所以索引的体积会比较大
CREATE INDEX [indexname] ON [tablename] USING GIST ( [geometryfield] );SP-GiST索引
建立不平衡树结构，包括四叉树等。实际生成过程中索引没有生成成功
CREATE INDEX [indexname] ON [tablename] USING SPGIST ( [geometryfield] );GIN 索引
适用于一个字段的内容本来为组合式内容，但是要做查询的场景，它的作用机理与mapbox的编码差不多，都是对数据进行拆分，然后对每一个关键字进行单独编码建立索引
BRIN 索引
空间数据里面使用比较合适，它的作用机理由数据的组织方式决定，即前后n条数据如果存储在硬盘的相邻区域就把它做为一个块，如此反复生成很多个小块，加快查询的效率。实际上地理数据生产的时候也是按区域进行数字化的，空间关联度很高所以很适合用这种方式的索引。
CREATE INDEX [indexname] ON [tablename] USING BRIN ( [geometryfield] );索引比较粗略，只精确到块级，不会到具体的数据，所以索引体积比较小，实际使用中发现索引的查询速度也没有GIST快。猜测在数据量没有到极其巨大的时候它的索引优势并不能体现出来。
ST_Collect合并多个要素时不会检查自相交，ST_Union会做自相交检查，ST_Collect运行效率是ST_Union的近百倍
使用node.js写的查询比数据库管理工具速度要快很多，排查发现是我个人电脑上有这个问题，只要是基于jdbc的查询都要比别人慢4倍，与java和数据库工具都没有关系。
子查询会导致索引不被使用
查询语句前面加上
explain analyze可以显示出查询计划和实际查询需要的时间，便于做sql优化，如果查询中启用了索引的也可以看到有使用index的标识st_isvalid(geom)
判断空间数据是否为正确的2d数据 st_isvalid(geom)='f'的原因，可通过ST_IsValidReason函数查看： select ST_IsValidReason(geom) from public."xianzhuangyongdi" where st_isvalid(geom)='f'; ST_IsValidDetail函数也可以辅助于调试
ST_Normalize、ST_MakeValid函数有助于解决此类问题
ST_Normalize会将一个面的自相交处理一遍，使之不自相交 ST_MakeValid更倾向于把一个自相交的面切分成两个面，似乎无法直接再写入表的本字段，需要进一步测试明确
ST_Boundary ST_Envelope区别
数据库查询缓存
postgresql数据库默认会使用缓存，并且这个缓存功能是无法关闭的，最低只能调到128KB。此时缓存命中率会非常低，对测试结果基本没有影响。
也可以使用pg_dropcache pg数据库插件实现缓存清除，未尝试过
也可以使用以下命令强制清除磁盘上的缓存（未实验过）
查询空间数据的坐标系：
select st_srid(geom) from "DS_YJJBNT";到底地理坐标数据导入pg后st_area计算面积单位默认是米还是投影坐标计算出的面积默认是米？
在postgis中计算时geometry指投影坐标系，geography指使用地理坐标系
ST_Covers的效率比ST_CoveredBy高，ST_Contains(A,B) = ST_Covers(A, B) = ST_Within(B,A)，
参考：
https://postgis.net/docs/reference.html#PostGIS_Types