ZrobDev/sql-tuning

SQL 調優（以 PostgreSQL 為優先）

在進行任何變更之前，先進行測量。沒有前後對照計畫和計時的調優變更，只是一種猜測。

程序

1. 找出目標：按總成本排序，而不是按單一最慢的呼叫（參見 triage 循環）。
2. 捕捉基準：在生產規模的資料上執行 EXPLAIN (ANALYZE, BUFFERS) <查詢>。極小的開發資料會選擇不同的執行計畫。
3. 找出時間或行數出錯的節點（參見下面的解讀規則）。僅修正該節點。
4. 重新執行相同的計畫捕捉。僅當變更在時間和緩衝區兩方面都表現更好時才保留。
5. 記錄索引或改寫的原因，並將其放在 schema 遷移旁邊。

將對 INSERT/UPDATE/DELETE 的 EXPLAIN ANALYZE 包裝在 BEGIN; ... ROLLBACK; 中，因為 ANALYZE 確實會執行該語句。

解讀執行計畫

• 從底部向上讀取，先處理內部節點。每個節點的 actual time 是每次循環的時間；乘以 loops 得到實際成本。
• 比較 rows（估算值）與 actual rows。相差 10 倍或更多通常是根本原因，遠比缺少索引更常見：執行 ANALYZE <資料表>，提高欄位統計資料目標，或為相關欄位添加擴展統計資訊 (CREATE STATISTICS ... (dependencies, ndistinct))。
• Rows Removed by Filter 遠大於返回的行數，表示索引（或缺少索引）對此謂詞不具有選擇性。
• Buffers: shared read 表示磁碟或 OS 快取；shared hit 表示 shared_buffers。重複執行時讀取次數高表示工作集無法放入記憶體。
• Sort Method: external merge Disk: 表示此排序的 work_mem 設定太小。為每個會話或角色提高此設定，而不是全域提高，因為它會乘以每個連接的每個排序節點。
• Nested Loop 具有大量的內部 loops 計數和未索引的內部端，是經典的慢速連接。Hash Join 溢出（Batches: >1）是同樣的 work_mem 問題。
• 當查詢讀取資料表的大部分資料時，Seq Scan 是正確的。不要為了消除返回 20% 行數的 Seq Scan 而添加索引。
• Index Only Scan 上的 Heap Fetches 表示可見性對應已過時；在該資料表上執行 VACUUM。

選擇索引

僅為你測量過的謂詞、連接或 ORDER BY 建立索引。每個索引都會耗費寫入時間、WAL 和 vacuum 工作。

• btree：用於相等、範圍、排序的預設選項。欄位順序：先放相等條件的欄位，再放範圍或排序欄位。PG 18 中的跳躍掃描有助於某些缺少前導欄位的情況，但不要圍繞它來設計。
• 部分索引 (WHERE status = 'open')：當查詢目標是一個小型、穩定的資料切片時（例如開放的項目、尚未處理的行）。查詢的 WHERE 條件必須隱含索引謂詞。
• 覆蓋索引 (INCLUDE (col))：當 Index Only Scan 可以為一個熱門查詢移除 heap fetch 時。包含的欄位不是可搜尋的鍵。
• 表達式索引 (ON (lower(email)), ON ((payload->>'id')))：當查詢對欄位套用函數時。查詢必須使用完全相同的表達式。
• BRIN：適用於大型僅追加資料表，其欄位與物理順序相關（時間戳記、序列 ID）。索引體積小，粗略修剪。檢查 pg_stats.correlation 是否接近 1；如果很低，BRIN 將無法修剪。
• GIN：用於 jsonb 包含、陣列、全文搜尋。GiST：用於範圍、幾何、排除約束。
• 使用 CREATE INDEX CONCURRENTLY 在運行中的資料表上建立索引；它不能在交易區塊內執行，且失敗的執行會留下一個需要刪除的 INVALID 索引。
• 僅在檢查 pg_stat_user_indexes.idx_scan 覆蓋一個完整的業務週期（財務工作負載在月底）並在複本上也檢查後，才刪除未使用的索引。

索引可利用性（Sargability）

只有當被索引的欄位獨立出現在一側時，謂詞才能使用索引。

• 使用 WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'，而不是 WHERE extract(year from created_at) = 2026 或 date_trunc(...) = ...。
• 使用 WHERE col = $1 且型別匹配；numeric_col = '5'::text 這類對欄位側的型別轉換會封鎖索引。對參數進行轉換，而不是對欄位。
• 避免開頭為萬用字元的 LIKE '%x'；使用 pg_trgm GIN 或全文搜尋。
• 跨不同欄位的 OR 常常會失去索引；嘗試使用兩個索引分支的 UNION ALL。
• NOT IN (子查詢) 對 NULL 不友善且速度慢；使用 NOT EXISTS。
• 對於半連接，優先使用 EXISTS 而不是連接後的 DISTINCT，因為它在找到第一個匹配項時就會停止，並且不會重複行。

視窗函數 vs 自連接

當目標是從同一集合中的相關行獲取每行值時，請使用視窗函數：運行總計、排名、lag/lead、「每個鍵的最新行」（row_number() OVER (PARTITION BY k ORDER BY ts DESC) = 1），因為一次掃描和一次排序取代了資料表與自身的連接。 僅當第二端需要自己的過濾條件或索引探查時（從大型資料表中獲取每個鍵的前 N 筆：JOIN LATERAL (SELECT ... ORDER BY ts DESC LIMIT 3)，並在 (k, ts DESC) 上有索引），才使用自連接或 LATERAL。 DISTINCT ON (k) ... ORDER BY k, ts DESC 是用於每個鍵取最新行的簡短 PostgreSQL 語法。

CTE

從 PG 12 開始，一個非遞迴、無副作用且僅被引用一次的 CTE 會像子查詢一樣被內聯。預設情況下以可讀性為優先來撰寫。

• 當 CTE 成本高昂且被多次引用時，或者當你必須阻止規劃器將不良謂詞推入其中時，添加 AS MATERIALIZED。
• 當外部過濾條件使多次引用的 CTE 變得便宜時，添加 AS NOT MATERIALIZED 以強制內聯。
• 修改資料的 CTE 總是執行一次；它們不會被最佳化掉。

pg_stat_statements 分類循環

需要在 shared_preload_libraries 中啟用 pg_stat_statements（需重啟）並 CREATE EXTENSION。

1. 按 total_exec_time DESC 排序（也要檢查 shared_blks_read、temp_blks_written、rows）。一個被呼叫 4000 萬次的 5 毫秒查詢，比一個執行 30 秒的夜間查詢更值得關注。
2. 取前 3 名。對於每個查詢，獲取一組真實的參數（統計資料是正規化的），然後執行程序的步驟 2。
3. 修正、部署，然後對該查詢執行 SELECT pg_stat_statements_reset(<userid>, <dbid>, <queryid>)，等待一個有代表性的時間窗口，進行比較。
4. 重複直到頂部條目是合理的成本。到那裡就停止。 搭配 auto_explain（謹慎使用 log_min_duration、log_analyze）來捕捉你無法重現的查詢的執行計畫。

PL/pgSQL 僅在值得使用時才用

當函數或程序能夠消除針對無法以集合為基礎的逐行邏輯的往返次數、強制執行觸發器不變量，或封裝多語句交易時，才使用它。不要用它來遍歷行並對每一行發出一次 UPDATE：將其改寫為一個 UPDATE ... FROM 或 INSERT ... SELECT。正確地將純函數標記為 IMMUTABLE/STABLE（錯誤的標籤會導致錯誤的快取結果或阻止內聯）。盡可能優先使用純 SQL 函數，以便規劃器可以內聯它們。

N+1 與分頁

• N+1：從應用程式或 ORM 程式碼為每個父行執行一個查詢。使用單一的 WHERE id = ANY($1) 批次、一個連接，或 ORM 的預先載入選項來修正。透過 pg_stat_statements 檢測：一個正規化的語句，其 calls 接近父行的數量。
• OFFSET 分頁會讀取並丟棄所有跳過的行，因此第 N 頁的成本是 O(N)。使用 keyset 分頁：WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 50，並在 (created_at DESC, id DESC) 上有索引。決勝欄位是必須的，否則行會被跳過或重複。Keyset 無法跳轉到第 N 頁；僅對小型有界集合使用 OFFSET。
• 大型資料表上的 count(*) 是一次完整掃描；顯示近似計數（reltuples）或使用 SELECT count(*) FROM (SELECT 1 ... LIMIT 10001) 設定上限。

批量 ETL 注意事項

使用 COPY（或 INSERT ... SELECT）進行批量載入，而不是逐行插入；僅在大型初始載入時才刪除或延遲次要索引，然後在之後執行 ANALYZE。大型 UPDATE 會使資料表膨脹：按鍵範圍分批處理，每批提交，並讓 autovacuum 跟上。

其他方言（簡要）

• BigQuery：成本是按位元組計費，而非行數。按日期/時間戳記欄位進行分區，並使用字面值或參數過濾它以啟用修剪；在下一個最常被過濾的欄位上進行聚簇。SELECT * 會對每個欄位計費；請指明欄位名稱。在執行前使用試運行查看位元組數。LIMIT 不會減少計費的位元組數。
• Snowflake：修剪使用微分割區的最小/最大元資料，因此請對那些值在載入時自然排序的欄位進行過濾；在查詢設定檔中檢查「掃描的分割區 vs 總數」。僅對具有穩定過濾模式的大型資料表添加聚簇鍵，因為重新聚簇會消耗積分。為了處理溢出（設定檔中的本地/遠端溢出），向上調整倉庫大小；為了解決佇列/並發問題，則向外擴展（多叢集）。自動暫停時間短。

當前版本和文件連結：references/landscape-2026-11.md。來自實際使用經驗的教訓：references/gotchas.md。