簡單連接中未使用的主鍵索引

我有以下表格和索引定義：

CREATE TABLE munkalap (
   munkalap_id serial PRIMARY KEY,
   ...
);

CREATE TABLE munkalap_lepes (
   munkalap_lepes_id serial PRIMARY KEY,
   munkalap_id integer REFERENCES munkalap (munkalap_id),
   ...
);

CREATE INDEX idx_munkalap_lepes_munkalap_id ON munkalap_lepes (munkalap_id);

為什麼以下查詢中沒有使用 munkalap_id 上的任何索引？

EXPLAIN ANALYZE SELECT ml.* FROM munkalap m JOIN munkalap_lepes ml USING (munkalap_id);

QUERY PLAN
Hash Join  (cost=119.17..2050.88 rows=38046 width=214) (actual time=0.824..18.011 rows=38046 loops=1)
 Hash Cond: (ml.munkalap_id = m.munkalap_id)
 ->  Seq Scan on munkalap_lepes ml  (cost=0.00..1313.46 rows=38046 width=214) (actual time=0.005..4.574 rows=38046 loops=1)
 ->  Hash  (cost=78.52..78.52 rows=3252 width=4) (actual time=0.810..0.810 rows=3253 loops=1)
       Buckets: 1024  Batches: 1  Memory Usage: 115kB
       ->  Seq Scan on munkalap m  (cost=0.00..78.52 rows=3252 width=4) (actual time=0.003..0.398 rows=3253 loops=1)
Total runtime: 19.786 ms

即使我添加一個過濾器也是一樣的：

EXPLAIN ANALYZE SELECT ml.* FROM munkalap m JOIN munkalap_lepes ml USING (munkalap_id) WHERE NOT lezarva;

QUERY PLAN
Hash Join  (cost=79.60..1545.79 rows=1006 width=214) (actual time=0.616..10.824 rows=964 loops=1)
 Hash Cond: (ml.munkalap_id = m.munkalap_id)
 ->  Seq Scan on munkalap_lepes ml  (cost=0.00..1313.46 rows=38046 width=214) (actual time=0.007..5.061 rows=38046 loops=1)
 ->  Hash  (cost=78.52..78.52 rows=86 width=4) (actual time=0.587..0.587 rows=87 loops=1)
       Buckets: 1024  Batches: 1  Memory Usage: 4kB
       ->  Seq Scan on munkalap m  (cost=0.00..78.52 rows=86 width=4) (actual time=0.014..0.560 rows=87 loops=1)
             Filter: (NOT lezarva)
Total runtime: 10.911 ms

許多人聽說過“順序掃描不好”的指導，並試圖將其從計劃中消除，但這並不是那麼簡單。如果查詢要覆蓋表中的每一行，順序掃描是獲取這些行的最快方法。這就是您的原始連接查詢使用 seq 掃描的原因，因為兩個表中的所有行都是必需的。

在規劃查詢時，Postgres 的規劃器會估計不同可能方案下各種操作（計算、順序和隨機 IO）的成本，並選擇它估計成本最低的計劃。從旋轉儲存（磁碟）進行 IO 時，隨機 IO 通常比順序 IO 慢很多，random_page_cost 和 seq_page_cost的預設 pg 配置估計成本差異為 4:1。

在考慮使用索引的連接或過濾方法與順序掃描表的方法時，這些考慮因素會起作用。使用索引時，計劃可能會通過索引快速找到一行，然後必須考慮隨機讀取塊來解析行數據。在您的第二個查詢添加了過濾謂詞的情況下WHERE NOT lezarva，您可以看到這如何影響 EXPLAIN ANALYZE 結果中的計劃估計。規劃器估計連接產生的 1006 行（與實際結果集 964 非常接近）。鑑於較大的表 munkalap_lepes 包含大約 38K 行，規劃器看到連接將不得不訪問表中大約 1006/38046 或 1/38 的行。它還知道平均行寬是 214 字節，一個塊是 8K，所以大約有 38 行/塊。

有了這些統計資訊，計劃者認為連接很可能必須讀取所有或大部分錶的數據塊。由於索引查找也不是免費的，並且掃描評估過濾條件的塊的計算相對於 IO 非常便宜，因此規劃器選擇順序掃描表並在計算 seq 掃描時避免索引成本和隨機讀取會更快。

在現實世界中，數據通常通過 OS 頁面記憶體在記憶體中可用，因此並非每個塊讀取都需要 IO。很難預測記憶體對給定查詢的有效性，但 Pg 計劃器確實使用了一些簡單的啟發式方法。配置值Effective_cache_size 通知規劃者估計產生實際 IO 成本的可能性。較大的值將導致它估計隨機 IO 的成本較低，因此可能會將其偏向於索引驅動方法而不是順序掃描。

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