利用postgresql进行缓冲区内对象检测过程中遇到的sql查询效率问题解决方案记录

首先说一下问题，在利用ST_DWithin（）函数进行缓冲区内对象进行检测时，出现效率极低的情况，比如，有几万条数据，每条数据都要在一个几十万条记录的表中检测存在于其缓冲区内的对象，而在执行的时候，每个对象的缓冲区检测是一条sq语句，但是刚开始的时候每条语句都要花费近一分多钟的时间，这样算下来几万条语句就要花费数万分钟，效率太极低，这个问题困扰了好几天，刚开始也试过其它函数，试过建立索引，都没有太明显的提高。幸好最终还是得以解决，最终的问题在于sql语句使用不当，导致建立的索引没有起到作用。

首先需要通过修改postgresql的配置文件postgresql.conf对数据库的相关内存参数进行调整，具体参数和调整方法参考点击打开链接

其次，需要对geometry字段建立空间索引：

建立空间索引的语句：CREATE INDEX geom_idx_name ON poly USING gist(geom)；

删除索引的语句：DROP INDEX “geom_idx_name”；

然后就是写出能够正确使用索引的sql语句，尽量将涉及到索引字段的部分sql语句简化，在涉及到索引字段的部分尽量不要使用sql语句嵌套，否则就会导致所建立的索引不能够被使用，而进行全盘扫描，要知道全盘扫描和使用索引在数据量比较大的时候效率是千差万别的。例如：

最开始的查询语句：select objectid from poly where ST_DWithin(ST_Transform(geom,3857), ST_Transform((select geom from poly where objectid=57),3857),50)=true，执行这条语句所耗时间在一分钟以上，没有走索引。

后来改进为：explain select objectid from poly where ST_DWithin(geom,’0102000020110F00000A000000D5F3E7D734D46C4177B409BBE0C04F41B512F20DC4D36C4145BEB34EC3C04F417150340635D36C41E41ED13AB6C04F41121FAB79E2D26C41E41ED13AB6C04F415CCEB3348AD26C41E41ED13AB6C04F41DD1C9DAA2ED26C41AB53754E6EC04F4104FC9FA0FDD16C41300A8C4E19C04F41D3EAC22CA6D16C410178C97E65BF4F41A9AA576A6FD16C41382D221CCFBE4F414F593DC033D16C41B9FEB7F30ABE4F41′,50)=true

即将ST_Transform(geom,3857)部分与(select geom from poly where objectid=57)部分都进行单独处理，这次就走了索引，时间大概在几百毫秒。效率提高了几百倍。

转载自：https://blog.csdn.net/xianyucishi/article/details/73481061