Postgres 18：Skip Scan - 擺脱最左索引限制詳情 - 數據庫,postgresql IvorySQL 博客

Postgres 18 於 2025 年 9 月 25 日發佈，帶來了多項性能增強和新功能。隨着版本迭代，Postgres 在關鍵業務與非關鍵業務場景中均表現出更高的穩健性、可靠性和響應能力。

Postgres 18 包含多項實用增強特性，此前已被關注的異步 I/O（AIO）子系統便是重要性能優化之一。該特性能夠提升順序掃描、位圖堆掃描和 VACUUM 操作期間的 I/O 吞吐量，可為多數 Postgres 用户帶來性能提升。在 Linux 系統（藉助 io_uring）上，通過將磁盤訪問與處理過程重疊，可實現 2-3 倍的性能提升，更多細節可參考博客鏈接：

https://www.pgedge.com/blog/highlights-of-postgresql-18

在眾多更新中，增強的 RETURNING 子句與 Skip Scan 優化對實際應用場景尤為重要。這兩項功能進一步提升查詢性能、優化 SQL 編寫體驗，並降低應用側的複雜度，無需進行 schema 調整或複雜調優。

RETURNING 子句增強：在 INSERT、UPDATE、DELETE 與 MERGE 語句中，可同時訪問 OLD 與 NEW 行值，適用於審計、API 返回、ETL 等場景，有助於減少往返、提升原子性並保持 SQL 的自包含性。
Skip Scan 優化：使查詢在未過濾前導列時仍可高效利用多列 B-tree 索引，可顯著提升分析型查詢與報表查詢的性能，無需額外創建索引。

兩項功能體現了 Postgres 18 在智能性能與簡化開發方面的設計理念。Skip Scan 功能由核心貢獻者 Peter Geoghegan 開發，展示了社區對代碼質量與審查流程的嚴格要求。

理解最左索引問題

B-tree Skip Scan 是 Postgres 18 最受關注的優化之一，用於解決多年來限制多列 B-tree 索引使用的“最左索引”問題。

在此前版本中，多列 B-tree 索引的最優使用依賴於查詢必須包含前導列的過濾條件。索引結構按照前導列優先排序，再按第二列排序，以此類推。

例如，多列索引 (status, customer_id, order_date) 的葉子節點按字典序存儲。

('active',101,'2024-01-01')
('active',101,'2024-01-15')
('active',102,'2024-01-03')
('pending',101,'2024-01-10')
('pending',103,'2024-01-20')
('shipped',101,'2024-01-05')
...

查詢若包含 status = 'active' AND customer_id = 101，會觸發連續範圍掃描，效率極高。但若只過濾 customer_id = 101 而忽略 status，則索引中的匹配項會分散在不同的 status 值下，規劃器通常會選擇順序掃描或使用其他索引，使該多列索引無法發揮作用。

這使得實際應用中常需要按不同列順序創建多個索引，導致：

存儲佔用增加
寫入性能降低
索引維護成本提升

Skip Scan 解決方案

Postgres 18 在 B-tree 索引中引入 Skip Scan 功能，使查詢規劃器能夠在前導列缺少等值條件時仍然使用多列索引。該能力消除了索引因未過濾首列而被閒置的情況，使原本可用的索引得以重新發揮作用。

Skip Scan 優化的核心是讓 Postgres 智能 “跳過” 索引的部分區域以查找相關數據。當查詢索引中靠後的列而未指定前導列時，Postgres 可實現以下操作：

識別被省略前導列中的所有 distinct 值。
將查詢邏輯有效轉換為包含這些前導列匹配條件的等價形式。
利用既有索引基礎設施，在掃描過程中跨前導列執行優化查找，跳過與查詢條件不匹配的索引頁。

該功能對於分析型與報表型工作負載尤為重要，因為此類場景經常需要基於不同字段組合執行查詢，而無需始終指定索引的前導列。

Skip Scan 的底層工作原理

以下示例展示 skip scan 的典型應用場景。存在一張 orders 表，並創建了多列 B-tree 索引：

CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
status VARCHAR(20),
customer_id INTEGER,
order_date DATE,
amount DECIMAL(10,2));

CREATE INDEX idx_orders ON orders(status,customer_id,order_date);

在 Postgres 18 以前，執行如下查詢：

SELECT * FROM orders
WHERE customer_id = 123
AND order_date > '2025-01-01';

由於謂詞未包含索引的前導列 status，該索引通常無法被有效利用，執行計劃往往退化為順序掃描。

Postgres 18 引入 skip scan 後，多列索引在前導列缺失過濾條件的情況下仍可發揮作用。查詢優化過程中，會對status 列的全部不同取值進行識別（如 pending、active、shipped），隨後基於每個取值與 customer_id、order_date 的組合執行定向索引掃描。邏輯等價形式如下：

SELECT * FROM orders WHERE status = 'pending' AND customer_id = 123 AND order_date > '2025-01-01'
UNION ALL
SELECT * FROM orders WHERE status = 'active' AND customer_id = 123 AND order_date > '2025-01-01'
UNION ALL
SELECT * FROM orders WHERE status = 'shipped' AND customer_id = 123 AND order_date > '2025-01-01';

當前導列的基數較低時，逐一掃描其不同取值的代價顯著低於順序掃描，因此 skip scan 在此類場景中能夠實現更優的性能表現。查詢優化器在執行計劃生成階段會自動評估此策略的收益，並選擇最合適的執行方式。

Skip Scan 的適用場景

Skip scan 在以下場景中性能優勢最為突出：

前導列低基數：當省略的前導列具有低基數時，優化效果最顯著。例如，status 列僅包含 3–5 個不同取值時，skip scan 能夠高效執行；若 distinct 值達到數千，則性能提升明顯下降。
後續列等值條件：Skip scan 針對索引中後續列被等值引用的情況進行了優化，當前實現針對這些特定模式進行高效處理。
分析與報表型工作負載：在需要靈活組合不同索引列進行查詢的分析場景中，skip scan 能顯著提高性能。這類場景常見於商業智能工具及臨時報表查詢。
避免索引氾濫：無需為不同列順序創建多個索引，可依靠單個設計合理的多列索引，通過 skip scan 實現高效查詢。

重要限制與注意事項

Skip scan 功能雖強大，但存在以下當前限制：

僅支持 B-tree 索引：Skip scan 目前僅適用於 B-tree 索引，這是最常用的索引類型。
性能依賴基數：隨着被省略列的 distinct 值數量增加，性能提升會顯著下降。對於高基數的前導列，仍可能需要專門索引以保證性能。
需等值條件：Skip scan 至少要求索引中後續列包含一個等值條件。對於任意範圍或複雜謂詞的後續列，不可期望該功能帶來優化效果。
大數據集結果：對於返回大量結果的查詢，傳統的位圖掃描或順序掃描計劃可能仍然是更優選擇。

實用示例與性能分析

通過一個更詳細的示例説明 skip scan 的應用。創建一張 sales 表，數據分佈貼近實際場景：

-- Create the sales table
CREATE TABLE sales (
    sale_id SERIAL PRIMARY KEY,
    region VARCHAR(20),
    product_category VARCHAR(50),
    sale_date DATE,
    amount DECIMAL(10,2)
);

-- Create multicolumn index
CREATE INDEX idx_sales_region_category_date
ON sales (region, product_category, sale_date);

-- Insert sample data
INSERT INTO sales (region, product_category, sale_date, amount)
SELECT
    CASE (random() * 4)::int
        WHEN 0 THEN 'North'
        WHEN 1 THEN 'South'
        WHEN 2 THEN 'East'
        ELSE 'West'
    END,
    'Category_' || (random() * 20)::int,
    '2024-01-01'::date + (random() * 365)::int,
    (random() * 1000)::numeric(10,2)
FROM generate_series(1, 1000000);

ANALYZE sales;

在 Postgres 17 中，按 product_category 查詢而未指定 region 列：

EXPLAIN ANALYZE
testdb-# SELECT * FROM sales
testdb-# WHERE product_category = 'Category_5'
testdb-# AND sale_date > '2024-06-01';

QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..18244.90 rows=29289 width=30) (actual time=0.382..47.816 rows=29343 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on sales  (cost=0.00..14316.00 rows=12204 width=30) (actual time=0.015..29.794 rows=9781 loops=3)
         Filter: ((sale_date > '2024-06-01'::date) AND ((product_category)::text = 'Category_5'::text))
         Rows Removed by Filter: 323552
 Planning Time: 0.216 ms
 Execution Time: 48.527 ms
(8 rows)

在 Postgres 17 中，由於未指定前導列 region，該查詢會執行順序掃描。Postgres 18 中，skip scan 可以高效利用索引，對 region 的四個不同值依次進行掃描，並執行定向查找。

同一查詢在 Postgres 18 中執行如下：

EXPLAIN ANALYZE
postgres-#SELECT *FROM sales
postgres-#WHERE product_category='Category_5'
postgres-#AND sale_date>'2024-06-01';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on sales(cost=457.63..8955.11rows=28832width=30)(actual time=2.671..11.931 rows=29202.00 loops=1)
RecheckCond(((product_category)::text='Category_5'::text)AND (sale_date>'2024-06-01'::date))
Heap Blocks: exact=7850 Buffers:shared hit=7917
->Bitmap Index Scan on idx_sales_region_category_date (cost=0.00..450.43rows=28832width=0)(actual time=1.916..1.917rows=29202.00loops=1)
Index Cond:(((product_category)::text = 'Category_5'::text)AND (sale_date>'2024-06-01'::date))
Index Searches:9
Buffers:sharedhit=67
Planning:
Buffers:shared hit=45 read=1
PlanningTime:0.189ms
ExecutionTime:12.801ms
(12rows)

執行計劃顯示 skip scan 正在發揮作用，相較順序掃描，緩衝區讀取顯著減少，執行時間得到明顯優化。

配置與調優

Postgres 18 將 skip scan 功能納入查詢規劃器工具集。查詢規劃器會基於成本估算自動決定何時使用 skip scan。

與其他規劃器優化類似，Postgres 提供通過配置啓用或禁用 skip scan 的靈活性，但在正常運行中，應依賴統計信息和成本估算由規劃器自動選擇最優策略。

展望未來

Skip scan 功能在查詢優化和索引利用方面邁出了重要一步，體現了社區在持續提升性能的同時，保持 Postgres 高可靠性和穩健性的承諾。

該功能解決了長期存在的索引使用痛點。通過實現多列索引的更靈活使用，skip scan 簡化了數據庫設計，降低了存儲開銷，並在廣泛場景中提升查詢性能。

隨着 Postgres 的持續發展，skip scan 及其他查詢優化能力預計將進一步增強。Postgres 18 打下的基礎，有望在未來版本中擴展至更復雜的查詢模式和更多類型的索引支持。

結論

Postgres 18 的 B-tree skip scan 功能解決了多列索引長期存在的可用性限制。在省略最左前綴列時，多列 B-tree 不再是“全有或全無”。對於特定工作負載——前導列基數低且後續列有等值條件——可以在無需創建額外索引的情況下充分發揮索引效能。

Postgres 社區在每一次版本迭代中持續提升數據庫性能、可擴展性和企業級適用性。Skip scan 是 Postgres 18 中眾多改進之一，共同增強了數據庫對現代應用工作負載的支持能力。

在 18 版本之後，Postgres 將繼續發展和優化，包括更多查詢優化功能、更完善的分析型工作負載支持，以及持續關注性能與可擴展性。Skip scan 等功能體現了社區對用户需求的響應及對實際場景挑戰的解決。

對於使用 Postgres 的數據庫管理員和開發者，skip scan 簡化了索引管理，並提升了查詢性能。在規劃升級至 Postgres 18 時，可審視現有多列索引，並識別可利用 skip scan 優化的查詢，發現合併索引和提升整體數據庫性能的機會。

原文鏈接：

https://www.pgedge.com/blog/postgres-18-skip-scan-breaking-fr...

作者：Ahsan Hadi

IvorySQL 博客

IvorySQL 博客

博客 / 詳情