PostGIS空间索引优化：索引失效和GiST索引排查

wowwwai GIS研习社 · 工具流程与项目排障

PostGIS空间索引优化：索引失效和GiST索引排查

在地块落区、管线压占、POI 进网格、影像瓦片范围筛选这类项目里，PostGIS空间索引通常是空间查询性能的第一道门槛。问题是，很多表明明已经建了索引，SQL 还是慢，执行计划里甚至看不到索引扫描。这时就要把问题拆成两个方向：索引是否真的可用，以及查询写法是否触发了 PostGIS空间索引失效。

本文按 Dr.GIS 的排查习惯，围绕这类优化讲清楚三件事：GiST 索引到底在优化什么，怎么用执行计划判断索引有没有参与查询，以及哪些 SQL 写法会让索引看起来“建了但没用”。

问题背景：为什么空间索引建了还是慢

空间索引不是“建完以后所有空间 SQL 都自动变快”的开关。它只在查询条件、数据分布、统计信息和空间函数写法都匹配时，才会被优化器选择使用。实际项目中，索引没有被使用，常见于批量导入后忘记更新统计信息、在 geom 字段外层套函数、两张大表直接全量空间连接，或者查询范围本身覆盖了大部分数据。

还有一种情况容易被误判：执行计划出现顺序扫描，不一定就是索引坏了。如果表很小，或者查询范围覆盖全市绝大多数要素，PostgreSQL 可能认为顺序扫描比索引扫描更便宜。这是优化器基于成本做出的选择。

所以，真正的排查顺序应该是：先确认空间列和索引定义，再用 EXPLAIN 看计划，再判断候选集是否过大，最后才考虑重建索引、改写 SQL 或调整数据加工方式。

核心原理：PostGIS GiST索引先过滤外包矩形

PostGIS GiST索引常用于 geometry 和 geography 类型的空间字段。GiST 可以理解为一种通用索引框架，PostGIS 用它存储几何对象的外包矩形，也就是 bounding box。空间查询时，数据库先用外包矩形快速筛出“可能相关”的候选对象，再由 ST_Intersects、ST_Contains、ST_DWithin 等函数做精确判断。

这就解释了两个常见现象。第一，空间索引能减少扫描对象，但不能消除所有几何计算。第二，复杂大面、大范围查询和低选择性条件，即使走了索引，也可能仍然慢，因为进入精确判断的候选对象太多。

判断空间索引是否有效，不要只看“有没有索引”。更关键的是看执行计划里是否出现空间索引条件，以及索引过滤后还剩多少候选行。

在多数空间叠加查询中，比较理想的过程是：属性条件先缩小业务范围，GiST 索引用外包矩形做粗过滤，空间函数再做精确拓扑判断。如果一开始就让两张百万级空间表互相做全量空间连接，索引也很难救场。

第一步：检查 GiST 索引是否真实存在

排查索引优化问题时，不要凭经验说“应该建过”。直接查询系统目录，确认目标表、目标字段和索引类型。

SELECT
  schemaname,
  tablename,
  indexname,
  indexdef
FROM pg_indexes
WHERE schemaname = 'public'
  AND tablename IN ('parcels', 'roads', 'poi')
ORDER BY tablename, indexname;

如果看到类似 USING gist (geom)，说明 geom 字段上存在 GiST 空间索引。如果只有主键索引、普通 B-tree 索引，或者索引建在另一个几何字段上，那么空间函数并不会自动使用你以为存在的索引。

同时检查空间字段的 SRID 和几何类型，避免后续把坐标系混乱误判成索引问题。

SELECT
  f_table_schema,
  f_table_name,
  f_geometry_column,
  type,
  srid
FROM geometry_columns
WHERE f_table_schema = 'public'
  AND f_table_name IN ('parcels', 'roads', 'poi')
ORDER BY f_table_name;

如果确实没有索引，可以创建 GiST 空间索引。在业务库里操作前，要评估数据量、锁表影响和维护窗口。

CREATE INDEX parcels_geom_gist
ON public.parcels
USING GIST (geom);

CREATE INDEX roads_geom_gist
ON public.roads
USING GIST (geom);

ANALYZE public.parcels;
ANALYZE public.roads;

大表刚导入后，只建索引还不够。执行 ANALYZE 可以更新统计信息，让优化器知道表里大概有多少行、数据分布怎样，从而更准确地选择执行计划。

第二步：用执行计划判断PostGIS空间索引失效

这类问题不能靠感觉判断，要看执行计划。先用一个范围查询做最小复现，不要一开始就拿复杂业务 SQL 排查。

EXPLAIN (ANALYZE, BUFFERS)
WITH q AS (
  SELECT ST_MakeEnvelope(120.10, 30.10, 120.35, 30.32, 4326) AS geom
)
SELECT
  p.id,
  p.name
FROM public.parcels AS p
JOIN q
  ON p.geom && q.geom
 AND ST_Intersects(p.geom, q.geom);

看计划时，重点找几类信息：是否出现 Index Scan、Bitmap Index Scan 或索引名称；是否有 Index Cond 指向 geom && 这类边界框条件；实际读取行数是否远高于预期；Buffers 是否显示大量数据页读取。

如果计划只有大范围 Seq Scan，先不要马上重建索引。继续判断：表是不是很小？查询范围是否覆盖了大部分数据？统计信息是否过旧？SQL 是否对 geom 做了函数包裹或类型转换？这些才是判断索引用不上的关键证据。

有时可以在测试会话中临时关闭顺序扫描，观察优化器是否存在可用索引路径。这个方法只用于诊断，不应作为生产优化方案。

BEGIN;
SET LOCAL enable_seqscan = off;

EXPLAIN
SELECT p.id
FROM public.parcels AS p
WHERE p.geom && ST_MakeEnvelope(120.10, 30.10, 120.35, 30.32, 4326);

ROLLBACK;

如果关闭顺序扫描后出现 GiST 索引路径，说明索引本身可能存在，但优化器认为当前条件下使用它不划算。此时更应该优化选择性、统计信息和查询范围，而不是反复创建同一个索引。

第三步：修复函数包裹导致的索引失效

最常见的索引用不上场景，是在索引列外面包函数。比如把 ST_Transform、ST_Buffer、ST_SetSRID 直接写在大表的 geom 字段上。

SELECT p.id
FROM public.parcels AS p
WHERE ST_Intersects(
  ST_Transform(p.geom, 3857),
  ST_Transform(ST_GeomFromText('POLYGON((...))', 4326), 3857)
);

这个写法的问题是，数据库需要先对表中每一行计算 ST_Transform(p.geom, 3857)，原始 geom 上的 GiST 索引很难直接参与过滤。更稳妥的做法是，把查询几何转换到表字段的 SRID，再和原始 geom 比较。

WITH q AS (
  SELECT ST_Transform(
    ST_GeomFromText('POLYGON((...))', 3857),
    4326
  ) AS geom
)
SELECT p.id
FROM public.parcels AS p
JOIN q
  ON p.geom && q.geom
 AND ST_Intersects(p.geom, q.geom);

如果业务长期需要用另一个投影坐标系查询，例如固定使用米制投影做距离和面积分析，可以考虑建立加工表，或者建立与查询表达式完全一致的函数索引。

CREATE INDEX parcels_geom_3857_gist
ON public.parcels
USING GIST (ST_Transform(geom, 3857));

ANALYZE public.parcels;

函数索引不是万能补丁。它会增加写入和维护成本，也要求查询表达式与索引表达式匹配。对于高频、稳定、读多写少的分析场景，它有价值；对于字段经常更新的业务表，通常更推荐在 ETL 阶段生成统一坐标系的加工表。

第四步：用统计信息和选择性做PostGIS空间索引优化

PostGIS空间索引优化的核心，不只是“加索引”，而是让索引过滤后的候选集足够小。选择性越高，索引越有价值；选择性越低，优化器越可能选择顺序扫描。

批量导入、删除、更新空间数据后，先更新统计信息：

VACUUM ANALYZE public.parcels;
VACUUM ANALYZE public.roads;

如果空间查询总是带有城市、项目、年份、状态等属性过滤，不要只依赖空间索引。普通属性索引同样重要。

CREATE INDEX parcels_city_status_idx
ON public.parcels (city_code, status);

CREATE INDEX roads_city_level_idx
ON public.roads (city_code, road_level);

ANALYZE public.parcels;
ANALYZE public.roads;

然后把查询写成“先业务过滤，再空间过滤”的形式。这样优化器可以综合使用属性索引和 GiST 空间索引，减少进入空间函数的行数。

WITH q AS (
  SELECT ST_MakeEnvelope(120.10, 30.10, 120.35, 30.32, 4326) AS geom
)
SELECT p.id, p.name
FROM public.parcels AS p
JOIN q
  ON p.geom && q.geom
 AND ST_Intersects(p.geom, q.geom)
WHERE p.city_code = '330100'
  AND p.status = '有效';

如果某类查询只访问有效数据，也可以评估部分索引。部分索引能减少索引体积，但必须和稳定的业务条件匹配。

CREATE INDEX parcels_valid_geom_gist
ON public.parcels
USING GIST (geom)
WHERE status = '有效';

部分索引适合条件长期稳定的表，例如只查询有效地块、已发布管线、当前版本行政区。不要为每个临时条件都建部分索引，否则维护成本会超过收益。

第五步：处理大面和复杂几何带来的低选择性

有些查询并不是索引失效，而是索引过滤效果天然不好。典型例子是省级行政区、海岸线、流域边界、生态保护红线这类大面或复杂多面。它们的外包矩形很大，可能覆盖大量要素，GiST 索引只能筛掉很少一部分对象。

对这种数据，可以建立切分后的查询表，用更小的外包矩形提高过滤效率。

CREATE TABLE public.districts_query AS
SELECT
  id AS source_id,
  (ST_Dump(ST_Subdivide(geom, 256))).geom AS geom
FROM public.districts
WHERE geom IS NOT NULL;

CREATE INDEX districts_query_geom_gist
ON public.districts_query
USING GIST (geom);

ANALYZE public.districts_query;

切分表适合读多写少的边界数据。查询时用切分表做空间过滤，最终统计再按 source_id 回到原始对象，避免同一个行政区因为多个切片而重复计数。

SELECT
  d.source_id,
  COUNT(DISTINCT p.id) AS parcel_count
FROM public.districts_query AS d
JOIN public.parcels AS p
  ON p.geom && d.geom
 AND ST_Intersects(p.geom, d.geom)
GROUP BY d.source_id;

这个方法不是为了替代原始边界，而是为高频空间叠加准备一张查询友好的辅助表。原始边界仍应保留，用于制图、归档和业务解释。

常见坑点：这些写法容易让索引失效

• 只建了主键索引。主键索引不能替代 GiST 空间索引，空间字段需要单独建 GiST 索引。
• 索引建在错误字段上。表里有 geom、center_geom、buffer_geom 多个空间列时，要确认查询使用的是哪一个字段。
• 在大表 geom 上套 ST_Transform。优先转换查询参数，或使用加工表、函数索引。
• 用 ST_SetSRID 修坐标系。ST_SetSRID只是修改 SRID 标签，不会真正投影转换，错误使用会同时造成结果错误和排查困难。
• 查询范围过大。全市、全省、全国范围查询选择性很低，索引扫描未必比顺序扫描便宜。
• 统计信息过旧。大批量导入和更新后没有 ANALYZE，优化器可能错误估算成本。
• 复杂大面未切分。外包矩形过大时，GiST 索引能过滤掉的对象很少，需要考虑 ST_Subdivide。
• 把诊断参数当成优化方案。enable_seqscan = off只能用于测试会话，不应写进业务系统。

方法对比：PostGIS GiST索引、属性索引和加工表怎么选

做空间索引优化时，不同手段解决的问题不同。不要把所有慢查询都归因于空间索引。

方法	适合解决的问题	注意点
USING GIST (geom)	空间范围过滤、相交、包含、距离等空间条件的候选集筛选	依赖查询写法和选择性，不能替代精确空间函数
属性 B-tree 索引	城市、状态、类型、年份、项目编号等高频属性过滤	应结合真实 WHERE 条件设计，避免无意义堆索引
函数 GiST 索引	固定投影或固定表达式的高频空间查询	查询表达式要和索引表达式匹配，写入维护成本更高
部分空间索引	只查询有效数据、当前版本数据、发布数据等稳定子集	WHERE 条件必须稳定且能覆盖主要查询模式
切分加工表	复杂大面、低选择性边界、高频叠加分析	最终统计要按原始 ID 汇总，避免切片重复

实际项目里，常见组合是：业务属性索引负责先缩小数据范围，GiST 索引负责空间粗过滤，空间函数负责精确判断，必要时再用加工表处理复杂几何。这比单纯反复重建索引更可靠。

实用清单：一套可复现的空间索引排查流程

1. 确认慢 SQL。记录完整 SQL、参数、调用入口、表名和查询范围，不要只凭“地图打开慢”排查。
2. 检查空间列。确认 geometry_columns 中的字段、类型、SRID 是否符合业务预期。
3. 检查索引定义。用 pg_indexes 确认目标字段存在 USING GIST 索引。
4. 更新统计信息。批量数据变化后执行 ANALYZE 或 VACUUM ANALYZE。
5. 查看执行计划。用 EXPLAIN (ANALYZE, BUFFERS) 判断是否有索引条件、实际行数和数据页读取。
6. 检查函数包裹。避免在大表 geom 上直接套 ST_Transform、ST_Buffer、类型转换等表达式。
7. 提高选择性。加入城市、状态、版本、视图范围和边界框条件，减少候选对象。
8. 处理复杂几何。对高频使用的大面边界建立切分查询表，并保留原始对象 ID。
9. 验证结果。优化后抽样核对空间结果，确认没有因为改写 SQL 导致漏选或误选。

FAQ：空间索引优化和索引失效常见问题

PostGIS空间索引失效是不是说明索引坏了？

不一定。更多时候指查询没有使用索引，或者使用后过滤效果很差。原因可能是函数包裹 geom、统计信息过旧、查询范围过大、表太小、选择性太低，也可能是优化器认为顺序扫描成本更低。真正的判断依据是执行计划。

PostGIS GiST索引和普通索引有什么区别？

这种 GiST 索引面向空间对象，主要用于外包矩形过滤，帮助空间函数减少候选几何。普通 B-tree 索引适合文本、数字、日期等属性字段。空间查询通常需要两者配合：属性索引先缩小业务范围，GiST 索引再做空间粗筛。

PostGIS空间索引优化一定要手写 geom && 查询条件吗？

不一定。许多 PostGIS 空间函数会自动使用边界框预筛选。但在排查阶段，显式写出 geom && 查询几何 可以让执行计划更容易阅读，也能帮助你确认候选集过滤是否存在。最终是否保留，要以结果正确性和执行计划为准。

ST_Transform 会让空间索引用不上吗？

如果把 ST_Transform 直接套在大表的索引字段上，就很容易影响原始空间索引的使用。更推荐把查询参数转换到表字段的 SRID，或者为固定表达式建立函数索引，再配合 ANALYZE 验证执行计划。

建了 GiST 索引后还需要ANALYZE吗？

需要。建索引解决的是访问路径问题，ANALYZE解决的是优化器估算问题。批量导入或大规模更新后，如果统计信息不准确，优化器可能低估或高估空间条件成本，进而选择不理想的执行计划。

什么时候应该重建空间索引？

只有在确认索引膨胀明显、索引损坏、长期大量更新删除后性能异常，或者维护窗口允许时，才考虑重建。大多数慢查询先排查 SQL 写法、统计信息、选择性和复杂几何，比直接重建空间索引更有效。

结论：空间索引优化要看证据，不要只看索引数量

PostGIS空间索引的价值在于减少候选几何，而不是替代所有空间计算。排查慢查询时，先确认 GiST 索引是否存在，再通过执行计划判断是否参与过滤，最后针对函数包裹、统计信息、低选择性和复杂大面逐项修复。

如果把这套优化思路总结成一句话，就是：让查询条件更可索引，让候选集更小，让精确空间函数面对更少、更干净的几何对象。这样处理，才能真正避开“索引建了但查询还是慢”的反复排查。