PostgreSQL+PostGIS环境下顺序扫描读relpages超10倍的原因是什么？

问题成因 #

你观察到的共享缓存命中页数波动，主要来自两个核心原因：

1. 并行查询的循环计数累加
   PostgreSQL的EXPLAIN ANALYZE输出中，节点的Buffers统计是所有循环次数的累加值，并非单次扫描的页数。你提供的执行计划里Seq Scan on a1 st节点的loops=6，对应启动了5个并行worker + 1个主进程，每个进程都会独立执行一次全表顺序扫描：

• 你查询到的a1主表大小为252个relpages，6次扫描的主表总页数就达到252*6=1512
• 你使用的geom是变长空间类型，大概率存储在TOAST表中，每次执行ST_DWithin都需要访问TOAST页，单次扫描需要额外读取约280页TOAST数据，累加6次后刚好和执行计划里该节点的shared hit=3192吻合。
  当查询因为系统资源不足、并行配置限制没有启动满并行worker时，总扫描页数就会降低；如果走单进程顺序扫描，总页数只有约400（252页主表+150页左右TOAST），也就是你所说的合理范围。

2. 缓存命中率波动
   如果部分TOAST页已经提前加载到共享缓存中，部分扫描不需要重新从磁盘加载（shared hit统计仍会计数命中的页，仅IO耗时更低）；如果出现缓存淘汰，也会导致命中数出现小幅波动。

优化建议 #

• 给a1.geom列创建GIST索引：CREATE INDEX idx_a1_geom_gist ON public.a1 USING GIST(geography(geom));，可以完全避免全表顺序扫描，大幅降低总扫描页数
• 将查询中的OR条件改写为UNION ALL拼接两个独立子查询，更容易触发空间索引和时序索引的组合使用
• 可以考虑将geom列直接存为geography类型，避免查询时的强制类型转换开销

内容的提问来源于stack exchange，提问作者Settembre Nero