利用postgresql进行缓冲区内对象检测过程中遇到的sql查询效率问题解决方案记录
来源:互联网 发布:淘宝有什么好的鞋店 编辑:程序博客网 时间:2024/06/11 11:48
首先说一下问题,在利用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)部分都进行单独处理,这次就走了索引,时间大概在几百毫秒。效率提高了几百倍。
- 利用postgresql进行缓冲区内对象检测过程中遇到的sql查询效率问题解决方案记录
- hadoop搭建过程中遇到问题解决方案
- Ios上线过程遇到的问题解决方案:
- 记录学习《0Day安全》路上遇到的问题解决方案 利用Ret2Libc挑战DEP
- ubuntu16.04与win10双系统的安装过程遇到的问题解决方案记录
- java中使用sql的存储过程,提高查询效率
- ubuntu使用中遇到的问题解决方案
- 结合ACCESS数据库开发过程中遇到的问题解决方案(转载)
- 检测mysql中sql语句的效率
- 向postgresql中利用sql批量跟新记录
- 不同数据库中查询前几条记录的用法(SQL Server/MYSQL /Oracle/Postgresql)
- SQL Server 2008 安装过程中出现问题解决方案
- 车道检测过程中遇到的问题
- sql server 存储过程中输入参数需要用in而失效的问题解决方案
- 利用DMV 查询效率低的sql语句
- Bochs安装和启动中遇到的问题解决方案
- cocos2d-x平台审核中遇到的openssl问题解决方案
- SQL查询的效率
- layer火狐弹出层显示不完全问题
- 职工有职工号,姓名,年龄.输入n个职工的信息,找出3个年龄最小的职工打印出来
- 使用RTL-SDR和Matlab Simulink玩转软件无线电(五)
- of_iomap
- VMware 10.0.6最小化安装centos7
- 利用postgresql进行缓冲区内对象检测过程中遇到的sql查询效率问题解决方案记录
- systemctl命令使用方法和心得体会
- React.js 官方资料摘记:状态提升
- Mac抓包工具Charles乱码解决办法
- 24点解法
- solr环境搭建&基本使用
- js加密用户名(朱***牛)
- java访问修饰符&final使用
- Linux内存管理