如何在PostgreSQL的GEOMETRY字段上创建空间索引？

Question

我正在使用PostgreSQL和PostGIS在表中处理地理坐标。如何在GEOMETRY(POINT)类型字段上创建空间索引，以提高基于距离的ST_DWithin查询的性能？

我正在使用迁移创建索引。

- Aayush Taneja

---

对于可能对内置的PostgreSQL GIST感兴趣而不是外部的PostGIS扩展的谷歌员工，请参考以下链接：https://stackoverflow.com/questions/28292198/how-to-port-simple-spatial-index-using-sqlite-r-trees-to-postgres - Ciro Santilli OurBigBook.com

Answer 1

对于几何数据，建议使用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

- Jim Jones

---

有没有一种方法可以在不执行原始SQL命令的情况下创建此索引？ 有任何Sequelize函数吗？ - Aayush Taneja

我对sequelize不是很熟悉，但我相信可以执行DDL语句，例如创建索引。你不能直接在数据库中运行这个语句吗？它非常简短，只需要执行一次。 - Jim Jones