scary-production/claude-plugins

PostgreSQL 查詢調校

驗證聲明請見 CHECKS.md。以下內容均適用於標準 PostgreSQL。首先查詢版本 (SHOW server_version)：因為預設 BUFFERS (PG18)、skip scan (PG18) 以及預設的 hash_mem_multiplier (從 PG15 起為 2.0) 取決於版本。

主要原則

不要盲目修補。應遵循以下順序：

1. 找出時間實際消耗在哪裡；
2. 找出是哪個查詢造成的，以及它被呼叫的次數；
3. 在執行計畫的哪個節點上，對行數的估計出現錯誤；
4. 修正問題；
5. 使用生產環境規模的資料，根據頁面數量進行驗證。

只有在診斷結果指向它們時，才調整索引、work_mem 和 random_page_cost。原因：若不先進行效能剖析，可能會修補一個只佔 1/60 執行時間的「可怕」查詢而毫無所獲；而隨意添加的索引反而會拖慢長時間運行的查詢。

時間花在哪裡

• 抱怨「速度慢」：首先比較資料庫內花費的時間與用戶端感知的時間。如果查詢總耗時約 100 毫秒，但用戶抱怨等待了一分鐘，那麼問題不在資料庫。不要去動查詢，而應檢查應用程式、網路或前端。
• 包含多個查詢的操作：透過 pg_stat_statements 進行效能剖析。
  1. 設定 pg_stat_statements.track = all。預設值為 top，這會導致函數內部的查詢不被記錄。
  2. 呼叫 pg_stat_statements_reset()。這需要超級用戶權限或已授予該函數的權限。 重置會清除所有使用者的統計資料，因此僅在測試環境或離峰時間執行此操作。
  3. 執行操作一次，然後根據 total_exec_time 排序結果。
  4. 查看 calls、shared_blks_hit + shared_blks_read 和 toplevel。toplevel = true 的行所花的時間已包含其內部的巢狀查詢時間。如果簡單地加總所有行，時間會被重複計算。
• 只修正佔據主要時間份額的部分。 效能提升不會超過這個份額：一個佔 60 秒中 1 秒的子任務，最多只能獲得 1/60 的改善。
• pg_stat_statements 中的查詢是正規化的：常數被替換為 $1，無法按連線區分，儲存的是彙總資料（平均值、最小值/最大值、標準差）。如果懷疑執行計畫取決於具體值（例如大客戶 vs 小客戶），請取得特定值並分別使用 EXPLAIN 進行分析。可以透過 pg_stat_activity 和 EXPLAIN VERBOSE 根據 queryid 關聯到具體查詢字串，這需要啟用 compute_query_id（自 PG14 起；設為 auto 時，會在載入 pg_stat_statements 時自動啟用）。
• 通過日誌：在 log_line_prefix 中需要包含 %p 或 %c 以及時間 %m。巢狀查詢只能透過 auto_explain 看到。設定 log_min_duration（預設 -1，即停用），啟用 log_analyze 和 log_nested_statements（預設為 off；修改需要超級用戶或 GRANT SET 權限，從 PG15 起）。使用 pgBadger 解析日誌。對於單個連線，使用 SET log_min_duration_statement（需要超級用戶或 GRANT SET ON PARAMETER 權限）和 LOAD 'auto_explain'（僅超級用戶）就足夠了。

在什麼資料上進行測量

• 不要在資料量小的開發資料庫上得出結論。 執行計畫依賴於表的大小和統計資訊，一個小型資料庫可以完全載入快取。如果查詢在小資料庫上很快，請說明這並不證明任何問題。
• 資料量和分佈應與生產環境一致。 如果生產環境包含個人資料，請勿將其副本用於測試環境。應生成相同模式的合成資料：主表中的行數以及它們在客戶之間的分佈情況。只有在收集統計資訊（pg_stat_statements，pg_class）時才訪問生產環境。
• 不要將包含生產環境常數的執行計畫貼到外部可視化工具中：常數中可能包含個人資料。請使用本地工具。

大量快速呼叫 (N+1)

• 如果查詢本身已經很快，但在單次操作中被呼叫了數十或數百次，不要試圖加速單個查詢。應將任務重寫為對整個批次進行單次查詢：

  • 不要在程式碼中對 id 列表進行迴圈查詢，而是使用 WHERE id = ANY($1) 或批次操作；
  • 對於按類別統計，使用 count(*) FILTER (WHERE …)；
  • 為了不丟失沒有資料的行，使用 LEFT JOIN。

  原因：一個耗時 1 毫秒的查詢已經無法再加速，但一千次這樣的呼叫會導致效能問題。例如，27 次呼叫合併為一個查詢後，讀取了約 6 萬頁，而不是原來的約 90 萬頁。

• 如果無法用一個查詢解決，應重新檢視服務中的資料存取方式，而不是調整資料庫設定。

如何比較不同方案

• 主要指標是頁面數。 執行 EXPLAIN (ANALYZE, BUFFERS) 並查看頂層節點的 shared hit + read：這裡已經是子節點的總和。將 hit 和 read 相加，因為它們的比例取決於快取。在 PG18 中，如果使用了 ANALYZE，BUFFERS 會自動啟用。
• 時間僅供參考。 時間會受到快取、負載、硬體以及 ANALYZE 測量本身影響。第二次執行總是更快，這並不能證明任何問題。只有在多次執行的情況下，才能寫下「變快了」。
• 頁面數無法反映 CPU 負載：例如記憶體內排序、函數、JIT、並行計畫等。如果查詢的計算量遠大於讀取量，請單獨檢查時間。
• 如果只需要行數和緩衝區資訊，請添加 TIMING FALSE：這樣可以避免測量時間的額外開銷。

分析執行計畫

1. 查詢類型。
   • 短查詢：傳回少量行，由人類等待回應。例如：物件卡片、列表頁面、按鍵值搜尋。預期會使用索引存取和巢狀迴圈 (nested loop)。在分析期間，設定 SET max_parallel_workers_per_gather = 0。
   • 長查詢：幾乎讀取整個表，然後進行彙總。例如：報表、分析、資料匯出。預期會使用順序掃描 (seq scan) 和雜湊連接 (hash join)，並行處理在此是正常的。

這只是預期，不是規則。如果執行計畫與預期不符，請在評估中尋找原因，而不是添加索引。 2. 評估與實際情況的比較。 逐節點比較 rows 和 actual rows。當 loops > 1 時，actual rows 的值是針對單次執行給出的，需要將其乘以 loops。修正首次出現偏差的最內層節點。錯誤會沿著執行計畫向上傳播，導致錯誤的存取方法和連接方式。 3. 頁面數與表大小的比較（針對長查詢）。 先執行 ANALYZE。然後將頂層節點的 hit + read 與 pg_class 中查詢所涉及表的 relpages 總和進行比較。如果查詢會讀取索引和 TOAST，則需單獨加上它們。

• 數值相近或更小：資料被讀取一次，執行計畫是合理的。
• 數值大很多：資料被重複讀取，例如巢狀迴圈中的 Index Scan。

對於使用索引的短查詢，此標準不適用。 4. 記憶體。 如果 BUFFERS 中出現了 temp read/written，或者 Hash 的 Batches > 1，表示排序操作使用了磁碟或記憶體不足。處理方法請參閱「修正什麼」一節。

修正什麼

首先修正評估和查詢本身，然後才是記憶體。成本參數最後才調整。

• 條件中使用函數包裝欄位 (starts_at::date = …、date_trunc('day', starts_at) = …、round(col) …)。將其改寫為半開區間 starts_at >= $1 AND starts_at < $2。對於 timestamptz，邊界應根據用戶時區計算，而不是伺服器時區。原因：對於沒有統計資訊的表達式，規劃器使用固定的選擇性（對於 = 是 0.5% 的行），因此會產生很大的誤差。對原始欄位的範圍查詢可以根據統計資訊進行評估，並使用索引。只有在無法改寫查詢時，才考慮為表達式建立索引或統計資訊。
• 檢查選擇了哪個索引。 如果索引是複合的，而過濾條件中的欄位不是第一個，則在 PG18 之前，它會讀取整個索引。PG18 中的 skip scan 可以幫助解決這個問題（執行計畫中顯示 Index Searches: N），但僅當第一個欄位只有少量不同值時才有利。否則，需要一個將該欄位放在第一位的索引。
• 宣告複合外部鍵 (account_id, user_id)，即使應用程式自行檢查所有約束，並在 JOIN 條件中使用所有外部鍵欄位進行簡單的等值比較。規劃器根據外部鍵來評估多個欄位的連接。沒有外部鍵時，它會假設欄位是獨立的，從而產生嚴重誤差（例如 1000 行 vs 10 萬行），然後選擇巢狀迴圈而不是雜湊連接。如果 JOIN 未完整覆蓋外部鍵（例如一個欄位是表達式或在 WHERE 子句中），則不會應用基於外部鍵的評估。目標表需要在相同的欄位上有一個唯一鍵。代價是寫入時的額外開銷。
• 長查詢中的冗餘 JOIN。 如果某個表沒有過濾作用，並且通過外部鍵和 NOT NULL 確保了關聯性，則移除它：這通常能獲得最大的效能提升。不要指望規劃器會自動移除與其他表的 INNER JOIN，即使有外部鍵也一樣：它只會移除與唯一鍵的 LEFT JOIN（且該查詢未讀取該唯一鍵的欄位），以及從 PG18 開始，移除與自身基於唯一鍵的連接。如果沒有外部鍵和 NOT NULL 的保證，不要移除這種 JOIN，否則會改變結果。
• 評估不正確但無法改寫查詢：為相關欄位建立擴展統計資訊，或設定 ALTER TABLE … ALTER COLUMN … SET STATISTICS n（預設為 100）。
• 記憶體不足（出現 temp 檔案，Batches > 1）。為當前連線或角色調高 work_mem 或 hash_mem_multiplier（使用 SET、ALTER ROLE … SET），但不要全域設定：因為 work_mem 是為每個連線中的每個節點分配的。用於雜湊操作的記憶體上限為 work_mem × hash_mem_multiplier。預設情況下是 4MB × 2.0 = 8MB；在 PG15 之前，這個乘數是 1.0。請使用 SHOW 檢查並計算最終的記憶體限制。

假設「規劃器錯了」

• 在交易中驗證：BEGIN; SET LOCAL enable_nestloop = off;（或 enable_seqscan），然後執行 EXPLAIN (ANALYZE, BUFFERS)，最後執行 ROLLBACK。根據頁面數進行比較——這樣可以確認是否存在更好的執行計畫。
• 不要將 enable_ 系列參數留在設定檔或服務代碼中。* 它們只是讓規劃器避開特定節點類型，但會影響所有查詢。文件將它們描述為粗糙的臨時手段。
• 接下來有兩種路徑：
  • 行數評估不正確——修正評估（參見「修正什麼」）。
  • 評估正確，但所需的計畫在多個查詢中系統性地未被選中——調整成本參數。如果使用 SSD 或資料庫幾乎完全在快取中，請將 random_page_cost 從 4.0 降低到 1.1–2，但不要低於 seq_page_cost (1.0)。將 effective_cache_size 設定為真實估計值：shared_buffers 加上作業系統快取（預設為 4GB）。只有在多個查詢上驗證後，才能全域修改這些參數。

不在此技能範圍內

• pg_query_state —— 需要修改核心。
• plprofiler 和 plpgsql_check —— 用於 PL/pgSQL 的邏輯；此技能假設邏輯在應用程式中。
• 需要使用商業 PostgreSQL 發行版的技巧。