PostgreSQL(PostGIS)：WITH子句结果调用ST_Intersects的性能问题

问题分析 #

PostgreSQL的WITH子句默认会做内联优化——数据库会把WITH里的查询逻辑合并到主查询中重新规划执行计划，而不是先执行WITH得到结果再过滤。这就导致你原本想先过滤出10条数据再做空间相交，结果数据库反而把ST_Intersects条件推到最前面，先扫描table4的百万级数据，完全违背了你的预期。

解决方案 #

1. 强制WITH子句物化 #

给WITH子句加上MATERIALIZED关键字，强制数据库先执行WITH里的查询并把结果存入临时表，再基于临时表做空间过滤：

WITH res AS MATERIALIZED (
    SELECT table1.someColumn, table2.someColumn, table3.someColumn, table4.geom
    FROM table1
    INNER JOIN table2 ON <condition>
    INNER JOIN table3 ON <condition>
    INNER JOIN table4 ON <condition>
    WHERE <some condition> AND <another condition>
)
SELECT * FROM res WHERE ST_Intersects(res.geom, <my bbox geometry>)

这样数据库会先执行WITH里的查询得到10条数据，再对这10条做相交判断，耗时会回到5秒左右。

2. 改用子查询替代WITH #

把子查询直接放到FROM里，让数据库明确先执行过滤逻辑：

SELECT *
FROM (
    SELECT table1.someColumn, table2.someColumn, table3.someColumn, table4.geom
    FROM table1
    INNER JOIN table2 ON <condition>
    INNER JOIN table3 ON <condition>
    INNER JOIN table4 ON <condition>
    WHERE <some condition> AND <another condition>
) AS res
WHERE ST_Intersects(res.geom, <my bbox geometry>)

这种写法的执行计划通常会先执行子查询得到小结果集，再做空间过滤，避免提前扫描大表。

3. 直接在主查询中过滤（逻辑允许时） #

如果非空间过滤条件已经能把结果集压缩到10条，也可以直接把空间条件加到主查询的WHERE里，通过子查询先锁定小结果集：

SELECT table1.someColumn, table2.someColumn, table3.someColumn, table4.geom
FROM (
    SELECT table1.someColumn, table2.someColumn, table3.someColumn, table4.geom
    FROM table1
    INNER JOIN table2 ON <condition>
    INNER JOIN table3 ON <condition>
    INNER JOIN table4 ON <condition>
    WHERE <some condition> AND <another condition>
) AS filtered
WHERE ST_Intersects(filtered.geom, <my bbox geometry>)

和方法2本质类似，都是先得到小结果集再做空间判断。

为什么之前的写法会变慢？ #

PostgreSQL的查询优化器会尝试重写查询以提高效率，但有时候会误判：它认为先做空间过滤能减少后续关联的数据量，但实际上你的非空间条件已经能把结果集压得极小，先做空间过滤反而因为扫描大表导致性能暴跌。强制物化或者用子查询能打破这种优化，让查询按照你预期的顺序执行。

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