在 WHERE 中使用子查詢會使查詢非常慢

由於我無法弄清楚的原因，我有這個相當基本的查詢非常慢：

SELECT s.id 
FROM segments s
WHERE
   ST_DWithin(
       s.geom::GEOGRAPHY,
       ST_Envelope((SELECT ST_COLLECT(s2.geom) FROM segments s2 WHERE s2.id IN (407820025,  407820024,  407817407,  407817408,  407816908,  407816909,  407817413,  407817414,  407817409,  407817410,  407817405,  407817406,  407816905,  407816907,  407817412,  407817411,  407816906,  407816904,  407816764,  407816765)))::GEOGRAPHY,
       30
   );

                                                                                                                          QUERY PLAN                                                                                                                            
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on segments s  (cost=55.58..48476381.06 rows=7444984 width=4)
  Filter: st_dwithin((geom)::geography, (st_astext(st_envelope($0)))::geography, '30'::double precision)
  InitPlan 1 (returns $0)
    ->  Aggregate  (cost=55.57..55.58 rows=1 width=32)
          ->  Index Scan using segments_pkey on segments s2  (cost=0.44..55.52 rows=20 width=113)
                Index Cond: (id = ANY ('{407820025,407820024,407817407,407817408,407816908,407816909,407817413,407817414,407817409,407817410,407817405,407817406,407816905,407816907,407817412,407817411,407816906,407816904,407816764,407816765}'::integer[]))

我真正感到困惑的是帶有子查詢的 ST_Envelope 本身非常快

SELECT ST_Envelope((SELECT ST_COLLECT(geom) FROM segments WHERE id IN (407820025,  407820024,  407817407,  407817408,  407816908,  407816909,  407817413,  407817414,  407817409,  407817410,  407817405,  407817406,  407816905,  407816907,  407817412,  407817411,  407816906,  407816904,  407816764,  407816765)))::GEOGRAPHY;

                                                                                                                          QUERY PLAN                                                                                                                            
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Result  (cost=55.58..55.60 rows=1 width=32)
  InitPlan 1 (returns $0)
    ->  Aggregate  (cost=55.57..55.58 rows=1 width=32)
          ->  Index Scan using segments_pkey on segments  (cost=0.44..55.52 rows=20 width=113)
                Index Cond: (id = ANY ('{407820025,407820024,407817407,407817408,407816908,407816909,407817413,407817414,407817409,407817410,407817405,407817406,407816905,407816907,407817412,407817411,407816906,407816904,407816764,407816765}'::integer[]))

如果我插入 ST_Envelope 的結果，主查詢也是如此

SELECT id 
FROM segments
WHERE
   st_dwithin(
       geom::geography,
       '0103000020E61000000100000005000000C87B6E0D8FB85EC04BFD8462B9C34640C87B6E0D8FB85EC0929B35C16DC44640BBF8DDA6F2B75EC0929B35C16DC44640BBF8DDA6F2B75EC04BFD8462B9C34640C87B6E0D8FB85EC04BFD8462B9C34640'::GEOGRAPHY,
       30
   );

                                                                                                                                                                                                                                                                               QUERY PLAN                                                                                                                                                                                                                                                                                
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using segments_geom_geo_idx on segments  (cost=0.42..4.82 rows=1 width=4)
  Index Cond: ((geom)::geography && '0103000020E61000000100000005000000C87B6E0D8FB85EC04BFD8462B9C34640C87B6E0D8FB85EC0929B35C16DC44640BBF8DDA6F2B75EC0929B35C16DC44640BBF8DDA6F2B75EC04BFD8462B9C34640C87B6E0D8FB85EC04BFD8462B9C34640'::geography)
  Filter: (('0103000020E61000000100000005000000C87B6E0D8FB85EC04BFD8462B9C34640C87B6E0D8FB85EC0929B35C16DC44640BBF8DDA6F2B75EC0929B35C16DC44640BBF8DDA6F2B75EC04BFD8462B9C34640C87B6E0D8FB85EC04BFD8462B9C34640'::geography && _st_expand((geom)::geography, '30'::double precision)) AND _st_dwithin((geom)::geography, '0103000020E61000000100000005000000C87B6E0D8FB85EC04BFD8462B9C34640C87B6E0D8FB85EC0929B35C16DC44640BBF8DDA6F2B75EC0929B35C16DC44640BBF8DDA6F2B75EC04BFD8462B9C34640C87B6E0D8FB85EC04BFD8462B9C34640'::geography, '30'::double precision, true))

Postgres 不應該計算一次 ST_Envelope 然後將其用於 WHERE 條件，有效地執行我手動執行的操作嗎？我也不明白為什麼在原始查詢中沒有使用索引來執行過濾器。

我嘗試將子查詢放在 CTE 中，但這並沒有解決問題。

原因是在幾何形狀不變的情況下，規劃器知道值並估計一個結果行，這使得索引掃描成為一個很好的策略。

使用原始查詢，planner 不知道該值，因為它只是在執行時確定，所以它猜測會有 7444984 個結果行。

我會寫兩個查詢：一個計算幾何，一個使用結果作為常數。

引用自：https://dba.stackexchange.com/questions/294449