Tiny table 會導致性能極度下降，由強制 VACUUM 修復。為什麼？

我使用 PostgreSQL 9.6。

我有一個連接 17 個表的查詢，其中 9 個表有幾百萬行。該查詢執行良好，但本周其性能迅速下降。EXPLAIN 的輸出沒有幫助（所有掃描都是索引掃描，除了非常小的表），我不得不嘗試從查詢中刪除表以隔離導致降級的表。

事實證明，一個包含 40 行的不起眼的表破壞了查詢：沒有表的時間為 800 毫秒，而有表的時間為 30 秒。我在桌子上執行了 VACUUM FULL，它在大約一秒鐘內執行，現在性能恢復正常。

我的問題：

1. 什麼可以解釋一個 <10kb 的表會破壞這樣的性能？
2. 以後如何避免同樣的問題？

在調試過程中，我對另一台伺服器進行了基本備份，因此我有兩個數據庫的文件系統級副本，其中一個我沒有執行 VACUUM FULL。當我使用 pgAdmin 登錄到 unvacuumed 副本時，我收到以下消息：

表“public.clients”上的估計行數與實際行數有很大偏差。您應該在此表上執行 VACUUM ANALYZE。

未清空的表有 40 行計數和 0 估計。以下是螢幕截圖中的其餘統計資訊。

該表可能很小，但只要 Postgres 預計大約有0行，它就有可能選擇與大約40行不同的查詢計劃——相同的查詢計劃效率不高。

由於連接會乘以結果行而不是僅僅添加結果行，因此當連接到具有數百萬行的大表時，小表中的 40 行可能會產生巨大的影響，例如您的範例。這種差異可以很容易地解釋執行時間的 30 倍。

或者正如手冊所說：

擁有相當準確的統計數據很重要，否則計劃選擇不當可能會降低數據庫性能。

autovacuum對於大多數安裝，預設設置都可以。考慮：

• 在 9.1 下是否仍建議使用正常 VACUUM ANALYZE？

但是對於包含數百萬行的多個表的數據庫，我會考慮ANALYZE不時調整選定表的每個表設置和整個數據庫的手冊。

剩下的問題

***Q1。為什麼 autovacuum 沒有ANALYZE自動啟動？

Q2。為什麼VACUUM FULL解決了問題？***

Q2很簡單：雖然其他重要統計數據僅由 更新ANALYZE，但基本計數估計pg_class.reltuples更新更頻繁。手冊：

表中的行數。這只是規劃者使用的估計值。它由VACUUM、ANALYZE和一些 DDL 命令（例如CREATE INDEX.

Q1更複雜。

再次使用手冊：

守護程序ANALYZE嚴格根據插入或更新的行數進行調度；它不知道這是否會導致有意義的統計變化。

相關設置（除其他外）：

autovacuum_analyze_threshold( integer)

指定ANALYZE在任何一個表中觸發所需的最小插入、更新或刪除元組數。預設值為50 個 元組。該參數只能在postgresql.conf文件中或伺服器命令行中設置；但是可以通過更改表儲存參數來覆蓋單個表的設置。

autovacuum_analyze_scale_factor( floating point)

指定 autovacuum_analyze_threshold在決定是否觸發 ANALYZE. 預設值為0.1（表大小的 10%）。該參數只能在 postgresql.conf 文件或伺服器命令行中設置；但是可以通過更改表儲存參數來覆蓋單個表的設置。

大膽強調我的。

展示

確保測試數據庫大部分處於空閒狀態以避免測試工件，並且您正在使用預設設置執行：

SELECT * FROM pg_settings WHERE name ~ '^autovacuum|track_counts';

最重要的是：

autovacuum_analyze_scale_factor = 0.1
autovacuum_analyze_threshold = 50
autovacuum_naptime = 60
track_counts = on

基本上，autovacuum 每分鐘檢查一次是否有任何表更改了last_estimate / 100 + 50 行並ANALYZE為這些行啟動。

要了解您的情況發生了什麼：

CREATE TABLE t50 (id int primary key, foo text);
INSERT INTO t50 SELECT g, 'txt' || g FROM generate_series(1,50) g;
SELECT reltuples FROM pg_class WHERE oid = 't50'::regclass;

pg_class.reltuples是表的估計行數。更多在這裡：

• 在 PostgreSQL 中發現表的行數的快速方法

你會得到***0. 等待 2 分鐘以確保我們跨越 1 分鐘的延遲。再檢查一遍。還是0***。現在再插入一行並再次檢查：

INSERT INTO t50 VALUES (51, 'txt51 triggers analyze');
SELECT reltuples FROM pg_class WHERE oid = 't50'::regclass;

還是***0。再等2分鐘，再次檢查。多田！我們看到更新的計數51***。Autovacuum 直到 51 行被插入（或更新或刪除）後才開始。

要查看更多詳細資訊（包括 的時間戳last_autoanalyze）：

SELECT * FROM pg_stat_all_tables WHERE relid = 't50'::regclass;

有關的：

• Postgres autovacuum 何時執行

解決方案

手動執行一次（或ANALYZE在public.clients整個數據庫上，它很便宜）並為這個重要的表使用更積極的每表自動清理設置。喜歡：

ALTER TABLE public.clients SET (autovacuum_analyze_scale_factor = 0.01
                             , autovacuum_analyze_threshold = 10);

出於其他原因，您可能還希望審核某些大表的設置。比較：

• 在有時很慢的大表上調試查詢

也很重要

您正在加入17 個join_collapse_limit表，這遠遠超出了預設設置8。您可能希望使用顯式連接語法（也許您已經這樣做了）並重寫您的查詢以將選擇性最強的表（或具有最選擇性謂詞的表）放在SELECT列表​​的首位。有關的：

• 在具有較小 LIMIT 的外部查詢中添加 ORDER BY 時，複雜視圖會變慢
• 我們可以在第一次執行 PL/pgSQL 函式時執行最優計劃而不是通用計劃嗎？

---

PS：我想我在執行上面的測試時發現了一個小的文件錯誤。手冊上autovacuum_analyze_threshold寫道：

指定在任何一個表中觸發所需的最小插入、更新或刪除元組數ANALYZE

這表明50 個插入觸發器ANALYZE，而不是我觀察到的***51 個。***類似pg_settings.short_desc：

在分析之前插入、更新或刪除的最小元組數。

事實上，此處手冊中對 autovacuum 的解釋與我的觀察相符：

否則，如果自上次以來廢棄的元組數VACUUM 超過“真空門檻值”，則表被真空。

前兩句似乎有些不正確。

我送出了錯誤報告。

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