PostgreSQL中子查询标量值能否在查询条件前求值？

我希望根据子查询的结果，在两个列中选择其一进行过滤。但分析查询发现，子查询值求值过晚，导致使用了效率较低的索引扫描或全表扫描。

示例说明 #

查询需要先执行一次计算（本文使用公共表表达式CTE），再根据计算结果选择条件中使用的列。以下为演示示例：

预期执行的查询 #

WITH "polygon" AS (SELECT ST_SetSRID(ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[[[-0.004,0.004],[0.004,0.004],[0.004,-0.004],[-0.004,-0.004],[-0.004,0.004]]]}'), 4326) AS "polygon"),
     "geometry" AS (SELECT ST_Transform("polygon", 900913) AS "geometry", ST_Area("polygon"::GEOGRAPHY) AS "area" FROM "polygon"),
     "parameters" AS (
         SELECT "area" > 1000000 AS "use_geom_alt"
         FROM "geometry"
     )
SELECT r.*
      FROM "record_record" "r"
      WHERE ("record_date" >= '2000-01-01' AND "record_date" <= '2019-12-31')
        AND (
              (NOT (SELECT use_geom_alt FROM parameters) AND ST_Intersects((SELECT geometry FROM geometry), r.geom))
              OR ((SELECT use_geom_alt FROM parameters) AND ST_Intersects((SELECT geometry FROM geometry), r.geom_alt))
          );

此示例尝试基于use_geom_alt的值短路条件，当use_geom_alt为FALSE时，应计算ST_Intersects((SELECT geometry FROM geometry), r.geom)并优先使用其索引。但查询计划显示子查询求值过晚，最终使用了效率较低的record_date索引扫描。

期望的执行效果 #

将子查询替换为布尔字面量后，查询使用了预期的高效索引扫描：

WITH "polygon" AS (SELECT ST_SetSRID(ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[[[-0.004,0.004],[0.004,0.004],[0.004,-0.004],[-0.004,-0.004],[-0.004,0.004]]]}'), 4326) AS "polygon"),
     "geometry" AS (SELECT ST_Transform("polygon", 900913) AS "geometry", ST_Area("polygon"::GEOGRAPHY) AS "area" FROM "polygon"),
     "parameters" AS (
         SELECT "area" > 1000000 AS "use_geom_alt"
         FROM "geometry"
     )
SELECT r.*
      FROM "record_record" "r"
      WHERE ("record_date" >= '2000-01-01' AND "record_date" <= '2019-12-31')
        AND (
              (TRUE AND ST_Intersects((SELECT geometry FROM geometry), r.geom))
              OR (FALSE AND ST_Intersects((SELECT geometry FROM geometry), r.geom_alt))
          );

问题总结 #

• 能否实现让子查询标量值在查询条件前求值的预期效果？
• 若无法实现，有哪些替代方案可以解决该问题？

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