報告的索引大小和執行計劃中的緩衝區數量之間存在巨大的不匹配

問題

我們有一個類似的查詢

SELECT COUNT(1) 
 FROM article
 JOIN reservation ON a_id = r_article_id 
WHERE r_last_modified < now() - '8 weeks'::interval 
  AND r_group_id = 1 
  AND r_status = 'OPEN';

由於它經常遇到超時（10 分鐘後），我決定調查這個問題。

EXPLAIN (ANALYZE, BUFFERS)輸出如下所示：

Aggregate  (cost=264775.48..264775.49 rows=1 width=0) (actual time=238960.290..238960.291 rows=1 loops=1)
  Buffers: shared hit=200483 read=64361 dirtied=666 written=8, temp read=3631 written=3617
  I/O Timings: read=169806.955 write=0.154
  ->  Hash Join  (cost=52413.67..264647.65 rows=51130 width=0) (actual time=1845.483..238957.588 rows=21644 loops=1)
        Hash Cond: (reservation.r_article_id = article.a_id)
        Buffers: shared hit=200483 read=64361 dirtied=666 written=8, temp read=3631 written=3617
        I/O Timings: read=169806.955 write=0.154
        ->  Index Scan using reservation_r_article_id_idx1 on reservation  (cost=0.42..205458.72 rows=51130 width=4) (actual time=34.035..237000.197 rows=21644 loops=1)
              Filter: ((r_group_id = 1) AND (r_status = 'OPEN') AND (r_last_modified < (now() - '56 days'::interval)))
              Rows Removed by Filter: 151549
              Buffers: shared hit=200193 read=48853 dirtied=450 written=8
              I/O Timings: read=168614.105 write=0.154
        ->  Hash  (cost=29662.22..29662.22 rows=1386722 width=4) (actual time=1749.392..1749.392 rows=1386814 loops=1)
              Buckets: 32768  Batches: 8  Memory Usage: 6109kB
              Buffers: shared hit=287 read=15508 dirtied=216, temp written=3551
              I/O Timings: read=1192.850
              ->  Seq Scan on article  (cost=0.00..29662.22 rows=1386722 width=4) (actual time=23.822..1439.310 rows=1386814 loops=1)
                    Buffers: shared hit=287 read=15508 dirtied=216
                    I/O Timings: read=1192.850
Total runtime: 238961.812 ms

瓶頸節點顯然是索引掃描。那麼讓我們看看索引定義：

CREATE INDEX reservation_r_article_id_idx1 
   ON reservation USING btree (r_article_id)
WHERE (r_status <> ALL (ARRAY['FULFILLED', 'CLOSED', 'CANCELED']));

尺寸和行號

它的大小（由\di+或通過訪問物理文件報告）為 36 MB。由於保留在上面未列出的所有狀態中通常只花費相對較短的時間，因此發生了很多更新，因此索引非常臃腫（這裡浪費了大約 24 MB） - 不過，大小相對較小。

該reservation表大小約為 3.8 GB，包含約 4000 萬行。尚未關閉的預留數量約為 170,000（確切數量在上面的索引掃描節點中報告）。

現在令人驚訝的是：索引掃描報告獲取了大量緩衝區（即 8 kb 頁面）：

Buffers: shared hit=200193 read=48853 dirtied=450 written=8

從記憶體和磁碟（或作業系統記憶體）讀取的數字加起來為 1.9 GB！

最壞的情況是

另一方面，最壞的情況是，當每個元組都位於表的不同頁面上時，將佔訪問 (21644 + 151549) + 4608 個頁面（從表中獲取的總行數加上來自物理的索引頁碼）尺寸）。這仍然僅低於 180,000 - 遠低於觀察到的近 250,000。

有趣（也許很重要）是磁碟讀取速度大約為 2.2 MB/s，我猜這很正常。

所以呢？

有誰知道這種差異可能來自哪裡？

**注意：**要清楚，我們有想法在這裡改進/改變，但我真的很想了解我得到的數字 - 這就是問題所在。

更新：檢查記憶體或微真空的效果

根據jjanes 的回答，我檢查了當我立即重新執行完全相同的查詢時會發生什麼。受影響緩衝區的數量並沒有真正改變。（為此，我將查詢簡化為仍然顯示問題的最低限度。）這是我從第一次執行中看到的：

Aggregate  (cost=240541.52..240541.53 rows=1 width=0) (actual time=97703.589..97703.590 rows=1 loops=1)
  Buffers: shared hit=413981 read=46977 dirtied=56
  I/O Timings: read=96807.444
  ->  Index Scan using reservation_r_article_id_idx1 on reservation  (cost=0.42..240380.54 rows=64392 width=0) (actual time=13.757..97698.461 rows=19236 loops=1)
        Filter: ((r_group_id = 1) AND (r_status = 'OPEN') AND (r_last_modified < (now() - '56 days'::interval)))
        Rows Removed by Filter: 232481
        Buffers: shared hit=413981 read=46977 dirtied=56
        I/O Timings: read=96807.444
Total runtime: 97703.694 ms

在第二個之後：

Aggregate  (cost=240543.26..240543.27 rows=1 width=0) (actual time=388.123..388.124 rows=1 loops=1)
  Buffers: shared hit=460990
  ->  Index Scan using reservation_r_article_id_idx1 on reservation  (cost=0.42..240382.28 rows=64392 width=0) (actual time=0.032..385.900 rows=19236 loops=1)
        Filter: ((r_group_id = 1) AND (r_status = 'OPEN') AND (r_last_modified < (now() - '56 days'::interval)))
        Rows Removed by Filter: 232584
        Buffers: shared hit=460990
Total runtime: 388.187 ms

我認為這裡的關鍵是大量更新和索引膨脹。

索引包含指向表中不再“活動”的行的指針。這些是更新行的舊版本。舊的行版本會保留一段時間，以滿足對舊快照的查詢，然後再保留一段時間，因為沒有人願意更頻繁地刪除它們。

掃描索引時，它必須去訪問這些行，然後注意到它們不再可見，因此忽略它們。該explain (analyze,buffers)語句不會顯式報告此活動，除非在檢查這些行的過程中計數讀取/命中的緩衝區。

btrees 有一些“微真空”程式碼，這樣當掃描再次回到索引時，它會記住它追逐的指針不再存在，並將其標記為索引中的死。這樣，下一個執行的類似查詢就不需要再次追踪它了。因此，如果您再次執行完全相同的查詢，您可能會看到緩衝區訪問量下降到更接近您的預測。

您還可以VACUUM更頻繁地使用表，這會將死元組從表本身中清除，而不僅僅是從部分索引中清除。一般來說，具有高周轉部分索引的表可能會從比預設級別更激進的真空中受益。

我通過以下兩個步驟解決了這個問題：

1. 殺死所有長時間執行的事務，然後執行清理。
2. 通過添加多列索引。在您的最後一個查詢計劃中，您正在獲取 19236+232584 行。比您實際需要的多 12 倍。過濾不是免費的，您必須在丟棄之前獲取實際的行。因此，您最終會讀取大量緩衝區。盡量減少過濾的行數。可能為 0。

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