PostGIS性能优化：空间索引与查询调优实战

随着数据量增长，PostGIS查询性能往往成为系统瓶颈。本文从索引、查询、数据导入三个维度讲解优化策略。

一、空间索引优化

-- 创建GIST索引
CREATE INDEX idx_geom ON cities USING GIST(geom);

-- 对大表使用BRIN索引（按物理顺序组织的数据）
CREATE INDEX idx_geom_brin ON cities USING BRIN(geom);

-- 查看索引大小
SELECT pg_size_pretty(pg_relation_size("idx_geom"));

二、查询计划分析

EXPLAIN ANALYZE
SELECT name, ST_Distance(geom, ST_MakePoint(116.4, 39.9)::geography)
FROM cities
WHERE ST_DWithin(geom, ST_MakePoint(116.4, 39.9)::geography, 50000);

三、常见优化技巧

• 使用ST_DWithin替代ST_Distance < threshold，前者可以利用索引
• 用&&运算符做边界框预过滤
• geography类型做距离计算时比geometry+投影转换更高效
• 对频繁查询的列建复合索引

四、批量导入优化

-- 关闭索引后导入，导入完成再重建
DROP INDEX idx_geom;
-- 批量COPY导入
COPY cities(name, geom) FROM "/tmp/data.csv" WITH (FORMAT csv);
-- 重建索引
CREATE INDEX idx_geom ON cities USING GIST(geom);
VACUUM ANALYZE cities;

PostGIS性能优化需要结合具体场景，掌握分析工具和优化手段才能对症下药。