Postgresql
在 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 個結果行。
我會寫兩個查詢:一個計算幾何,一個使用結果作為常數。