node.js - 如何在 PostgreSQL GEOMETRY 字段上创建空间索引?
问题描述
我正在使用 PostgreSQL 和 PostGIS 来处理表中的地理坐标。如何在GEOMETRY(POINT)
类型字段上创建空间索引以提高基于距离的ST_DWithin
查询的性能?
我正在使用迁移来创建索引。
解决方案
对于几何,建议使用gist
索引,例如
CREATE INDEX idx_any_label ON mytable USING gist (geom_column);
数据样本(50k 随机点):
CREATE TABLE t (geom geometry(point,4326));
INSERT INTO t
SELECT ('SRID=4326;POINT('||floor(random() * 50)||' ' ||floor(random() * 50) ||')')
FROM generate_series(1,50000);
没有索引的查询计划:
EXPLAIN ANALYSE
SELECT * FROM t
WHERE ST_DWithin('SRID=4326;POINT(1 1)',geom,1);
Seq Scan on t (cost=0.00..1252068.48 rows=5 width=32) (actual time=122.091..144.137 rows=98 loops=1)
Filter: st_dwithin('0101000020E6100000000000000000F03F000000000000F03F'::geometry, geom, '1'::double precision)
Rows Removed by Filter: 49902
Planning Time: 0.083 ms
JIT:
Functions: 2
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 0.387 ms, Inlining 83.228 ms, Optimization 30.947 ms, Emission 7.626 ms, Total 122.187 ms
Execution Time: 186.107 ms
带有 gist 索引的查询计划
CREATE INDEX idx_t_geom ON t USING gist (geom);
EXPLAIN ANALYSE
SELECT * FROM t
WHERE ST_DWithin('SRID=4326;POINT(1 1)',geom,1);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on t (cost=4.98..2119.16 rows=5 width=32) (actual time=0.086..0.367 rows=98 loops=1)
Filter: st_dwithin('0101000020E6100000000000000000F03F000000000000F03F'::geometry, geom, '1'::double precision)
Rows Removed by Filter: 83
Heap Blocks: exact=139
-> Bitmap Index Scan on idx_t_geom (cost=0.00..4.98 rows=77 width=0) (actual time=0.063..0.064 rows=181 loops=1)
Index Cond: (geom && st_expand('0101000020E6100000000000000000F03F000000000000F03F'::geometry, '1'::double precision))
Planning Time: 0.291 ms
Execution Time: 2.237 ms
演示:db<>fiddle
推荐阅读
- android - 如何在Delphi android中连接后(在监听模式下)使用蓝牙接收数据
- database - 将数据放在 Kafka 或数据库中?
- wordpress - 在wordpress中编辑帖子时如何设置指定的字体系列?
- php - `content_for_layout` 如何在 shopify 中加载内容?
- c# - 文件流重命名文件
- javascript - React Native - FlatList 对 renderItems 中的每个项目进行获取请求
- mongodb - 如何在 MongoDB 中应用密集排名?
- python - 如何根据另一个数据框中的变量从数据框中选择列
- python - 尝试在熊猫数据框中追加时获得“真”值而不是实际值
- cross-browser - browser.tabs.executescript() 在 Firefox 中不起作用