三 WHERE 查詢慢下來

我有一個表 adjacency，它儲存有關連接線的資訊以及它們彼此的角度是由

CREATE TABLE public.adjacency
(
   currentedge integer,
   currentgeom geometry(LineString,25832),
   sourcenode integer,
   targetnode integer,
   nextedge integer,
   nextgeom geometry(LineString,25832),
   degrees double precision
)

現在，我只想選擇具有特定間隔角度的線

DROP TABLE IF EXISTS restriction;
SELECT DISTINCT '100' as to_cost, a.nextedge as target_id, a.currentedge as via_path
INTO restriction
FROM adjacency a ,  adjacency b 
WHERE (a.sourcenode = b.sourcenode AND (a.degrees < 45.0 OR a.degrees > 315.0))  
   OR (a.targetnode = b.targetnode AND (a.degrees < 45.0 OR a.degrees > 315.0))
   OR (a.targetnode = b.sourcenode AND (a.degrees BETWEEN 46.0 AND 224.0))
AND a.nextedge=b.currentedge

最後一個命令的執行似乎沒完沒了。誰能向我解釋這裡要改變什麼？我的執行計劃：

Seq Scan on adjacency a  (cost=0.00..2574.30 rows=30630 width=40)

有時可行的一件事是將一系列 OR 條件重寫為一系列 UNION：

CREATE TABLE restriction
AS
SELECT '100' as to_cost, a.nextedge as target_id, a.currentedge as via_path
FROM adjacency a 
JOIN adjacency b ON a.nextedge = b.currentedge
WHERE a.sourcenode = b.sourcenode AND (a.degrees < 45.0 OR a.degrees > 315.0)
UNION 
SELECT '100' as to_cost, a.nextedge as target_id, a.currentedge as via_path
FROM adjacency a 
JOIN adjacency b ON a.nextedge=b.currentedge
WHERE a.targetnode = b.targetnode AND (a.degrees < 45.0 OR a.degrees > 315.0)
UNION 
SELECT '100' as to_cost, a.nextedge as target_id, a.currentedge as via_path
FROM adjacency a 
JOIN adjacency b ON a.nextedge=b.currentedge
WHERE a.targetnode = b.sourcenode AND (a.degrees BETWEEN 46.0 AND 224.0)
;

您在這裡不需要額外的不同，因為UNION會照顧到這一點。我還將古老的、過時的隱式連接語法替換為顯式的 JOIN 運算符。

您還可以嘗試以下索引：

• adjacency (nextedge, sourcenode)
• adjacency (currentedge, sourcenode)
• adjacency (nextedge, targetnode)
• adjacency (currentedge, targetnode)

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