作者:Neshoir
SuperMap iDesktop属性查询优化
GIS矢量几何数据在各行业领域的应用中,避免不了空间查询和属性查询等操作。既然是查询,那就少不了查询优化话题,高效率的查询可以给业务系统带来很好的体验。小编就iDesktop在PostGIS数据源上的属性查询优化之路做一个全面的介绍。将在724w条地类图斑数据里对qsdwmc字段做属性查询,将查询性能优化在100ms之内。
一、背景介绍
- 一份国土图斑数据,约724w条记录,smid字段有索引,smgeometry有空间索引。
- iDesktop属性查询,实际是UGO组件接口在PG数据库上做sql查询。
- 如何将权属单位名称qsdwmc字段的属性查询优化到100ms?
- 属性查询,包含等值查询、前置糊查询、后模糊查询、前后模糊查询。
二、qsdwmc字段无索引
-
等值查询,无索引全表顺序扫描,等值查询耗时2.4s,故不必再做其他查询。
三、iDesktop创建qsdwmc字段索引
iDesktop创建字段索引,功能界面如下图,通过pgsql查看该索引,可以看出iDesktop对属性字段创建的是B树索引。
-
等值查询,效率比较给力,耗时81ms。
-
模糊查询,就有些差强人意了,不管前模糊查询还是后模糊查询,耗时约2.5s+,通过查询计划可以看出原因是B树索引没有起作用,其原因是B树索引创建时默认的opclass为等值查询。
-
模糊查询,我们在创建B树索引时加上指定的操作类验证下。B树索引支持text_pattern_ops、varchar_pattern_ops和bpchar_pattern_ops操作类,分别对应text、varchar和char类型的字段。由于qsdwmc字段是text类型,那就加上text_pattern_ops操作类再创建一个B树索引。
-- qsdwmc字段创建B树索引时加上指定的text_pattern_ops操作类 CREATE INDEX dltb700w_fieldindex_tpo ON public.dltb700w USING btree (qsdwmc text_pattern_ops);
可以看出后模糊查询的效率很给力,耗时54ms。但前模糊查询的耗时依然2.6s,不能满足要求。
为解决前模糊查询效率,下面介绍一种真正支持模糊查询的GIN索引,PG官方的插件pg_trgm模块提供了GiST 和GIN 索引操作符类,GIN索引适用于LIKE查询。
四、qsdwmc字段创建GIN索引
-
创建插件
CREATE EXTENSION pg_trgm;
-
创建GIN索引
CREATE INDEX dltb700w_fieldindex_gin ON public.dltb700w USING gin(qsdwmc gin_trgm_ops);
-
模糊查询,效率快到飞起。如果返回记录数少,那么耗时应该就完全低于100ms。
-- 前模糊查询耗时117ms、后模糊查询46ms、前后模糊查询109ms。 -- 返回记录数过多,也会增加耗时。
-
等值查询,耗时46ms。
五、小结
至此,等值查询优化提升了50倍,模糊查询优化提升25倍,属性查询效率能这么快,得益于构建的qsdwmc字段索引,当前索引情况如下。
注意:创建索引过程会比较耗时,同时会占用一部分表空间。但这有能大大提升业务系统查询效率,所以无论属性字段还是空间对象字段都必须创建索引,以空间换取时间。超图产品兼容PG的三方索引插件。