Postgresql
執行計劃更喜歡連接而不是排序
最近我在我們的生產伺服器（Pg 9.6）上遇到了一個問題，即使我找到了解決問題的方法，我也想更好地了解它背後的原因。為了更容易解釋，我使用了虛擬數據和表格。讓我們進行以下設置：
我們有 2 個表 a,b 都具有復合鍵和 1:1 關係（如果 id+brand 存在於 b => 它存在於 a 中，否則不存在）。
CREATE TABLE a ( id text NOT NULL, brand text NOT NULL, timestamp BIGINT NOT NULL, PRIMARY KEY (id, brand) ); CREATE TABLE b ( id text NOT NULL, brand text NOT NULL, timestamp BIGINT NOT NULL, PRIMARY KEY (id, brand) ); CREATE INDEX ON a (timestamp); CREATE INDEX ON b (timestamp);我在使用此查詢搜尋最新行時遇到了一些性能問題（請注意，執行計劃的時間是合成的，在實際情況下需要 8 秒或更長時間）：
SELECT * FROM a LEFT JOIN b ON a.id = b.id AND a.brand = b.brand WHERE a.brand = 'SA-00010-1' ORDER BY a.timestamp DESC, a.id DESC LIMIT 20 +---------------------------------------------------------------------------------------------------------------------------------------+ |QUERY PLAN | +---------------------------------------------------------------------------------------------------------------------------------------+ |Limit (cost=35161.94..35161.99 rows=20 width=143) (actual time=533.962..533.970 rows=20 loops=1) | | Output: a.id, a.brand, a."timestamp", b.id, b.brand, b."timestamp", a."timestamp", a.id | | -> Sort (cost=35161.94..35650.70 rows=195506 width=143) (actual time=533.960..533.961 rows=20 loops=1) | | Output: a.id, a.brand, a."timestamp", b.id, b.brand, b."timestamp", a."timestamp", a.id | | Sort Key: a."timestamp" DESC, a.id DESC | | Sort Method: top-N heapsort Memory: 30kB | | -> Hash Right Join (cost=13269.70..29959.59 rows=195506 width=143) (actual time=135.707..462.819 rows=195314 loops=1) | | Output: a.id, a.brand, a."timestamp", b.id, b.brand, b."timestamp", a."timestamp", a.id | | Hash Cond: ((b.brand = a.brand) AND (b.id = a.id)) | | -> Seq Scan on public.b (cost=0.00..8427.11 rows=196452 width=51) (actual time=0.019..100.077 rows=195314 loops=1) | | Output: b.id, b.brand, b."timestamp" | | Filter: (b.brand = 'SA-00010-1'::text) | | Rows Removed by Filter: 173495 | | -> Hash (cost=8427.11..8427.11 rows=195506 width=51) (actual time=135.422..135.422 rows=195314 loops=1) | | Output: a.id, a.brand, a."timestamp" | | Buckets: 65536 Batches: 8 Memory Usage: 2814kB | | -> Seq Scan on public.a (cost=0.00..8427.11 rows=195506 width=51) (actual time=0.017..79.168 rows=195314 loops=1)| | Output: a.id, a.brand, a."timestamp" | | Filter: (a.brand = 'SA-00010-1'::text) | | Rows Removed by Filter: 173495 | |Planning time: 0.523 ms | |Execution time: 534.025 ms | +---------------------------------------------------------------------------------------------------------------------------------------+這裡的問題是數據庫首先將 a 與b表連接起來（在我的實際情況下，這有點慢），對結果進行排序並刪除除前 20 行之外的所有內容。即使我在表b中創建與a相關的外鍵，這種情況也會發生
我的問題的解決方案是通過以下查詢解決****的
WITH a AS ( SELECT a.id, a.brand, a.timestamp FROM a WHERE a.brand = 'SA-00010-1' ORDER BY a.timestamp DESC, a.id DESC LIMIT 20 ) SELECT * FROM a LEFT JOIN b ON a.id = b.id AND a.brand = b.brand WHERE a.brand = 'SA-00010-1' ORDER BY a.timestamp DESC, a.id DESC LIMIT 20 +---------------------------------------------------------------------------------------------------------------------------------------+ |QUERY PLAN | +---------------------------------------------------------------------------------------------------------------------------------------+ |Limit (cost=13638.42..13638.43 rows=1 width=163) (actual time=80.982..80.985 rows=20 loops=1) | | Output: a.id, a.brand, a."timestamp", b.id, b.brand, b."timestamp", a."timestamp", a.id | | CTE a | | -> Limit (cost=13629.46..13629.51 rows=20 width=51) (actual time=80.761..80.770 rows=20 loops=1) | | Output: a_1.id, a_1.brand, a_1."timestamp" | | -> Sort (cost=13629.46..14118.22 rows=195506 width=51) (actual time=80.760..80.766 rows=20 loops=1) | | Output: a_1.id, a_1.brand, a_1."timestamp" | | Sort Key: a_1."timestamp" DESC, a_1.id DESC | | Sort Method: top-N heapsort Memory: 27kB | | -> Seq Scan on public.a a_1 (cost=0.00..8427.11 rows=195506 width=51) (actual time=0.041..59.052 rows=195314 loops=1)| | Output: a_1.id, a_1.brand, a_1."timestamp" | | Filter: (a_1.brand = 'SA-00010-1'::text) | | Rows Removed by Filter: 173495 | | -> Sort (cost=8.91..8.92 rows=1 width=163) (actual time=80.982..80.983 rows=20 loops=1) | | Output: a.id, a.brand, a."timestamp", b.id, b.brand, b."timestamp", a."timestamp", a.id | | Sort Key: a."timestamp" DESC, a.id DESC | | Sort Method: quicksort Memory: 30kB | | -> Nested Loop Left Join (cost=0.42..8.90 rows=1 width=163) (actual time=80.808..80.965 rows=20 loops=1) | | Output: a.id, a.brand, a."timestamp", b.id, b.brand, b."timestamp", a."timestamp", a.id | | -> CTE Scan on a (cost=0.00..0.45 rows=1 width=72) (actual time=80.775..80.791 rows=20 loops=1) | | Output: a.id, a.brand, a."timestamp" | | Filter: (a.brand = 'SA-00010-1'::text) | | -> Index Scan using b_pkey on public.b (cost=0.42..8.44 rows=1 width=51) (actual time=0.008..0.008 rows=1 loops=20) | | Output: b.id, b.brand, b."timestamp" | | Index Cond: ((a.id = b.id) AND (a.brand = b.brand) AND (b.brand = 'SA-00010-1'::text)) | |Planning time: 0.265 ms | |Execution time: 81.038 ms | +---------------------------------------------------------------------------------------------------------------------------------------+似乎第一個查詢只是慢了一點，但在我的實際情況下，它是 20 倍，並且問題與多個 LEFT JOIN 級聯（SELECT * FROM a LEFT JOIN b LEFT JOIN c 等）。我知道由於 LEFT JOIN 將 SORT 作為查詢執行中的最後一個是有道理的，但在這種情況下偏愛 SORT 並解決第一個問題會帶來巨大的加速。
有人可能會爭辯說，DB 不知道這種關係，但 PRIMARY KEY（或我的其他表中的 FOREIGN KEY）的存在可以暗示 Postgres 更喜歡排序會導致更快的查詢……
所以這是我的問題。有什麼方法可以建議數據庫在我的第一個查詢中更喜歡排序而不是加入？我是否忽略了表格結構的某些內容？
感謝您閱讀所有這些內容，並提前感謝您的提示
你需要索引
- 支持
WHERE條件和ORDER BY條款a- 在加入條件上
b然後你可以得到一個快速的嵌套循環連接。
在你的情況下，那將是
CREATE INDEX ON a (brand, timestamp, id); CREATE INDEX ON b (id, brand);