Postgres 18: Skip Scan - Breaking Free from the Left-Most Index Limitation

Postgres 18, released on September 25, 2025, introduces an exciting set of performance improvements and new capabilities. Postgres has grown remarkably over the years, and with each major release has become a more robust, reliable, and responsive database for both mission critical and non-mission critical enterprise applications.

I’ve been writing about these enhancements since the release, and today I’m diving into two more features that are especially useful in real-world applications.

I previously blogged about a major new performance feature, the Asynchronous I/O (AIO) sub-system feature. AIO boosts I/O throughput during sequential scans, bitmap heap scans, and VACUUM operations, providing a performance boost for essentially everyone who uses Postgres. On Linux (with io_uring), this can offer 2–3× performance improvements by overlapping disk access with processing. Please see my blog for more details: https://www.pgedge.com/blog/highlights-of-postgresql-18

This week I'll add to my new feature series with a discussion of two more features from the latest release that focus on improving everyday performance and developer efficiency. Both the enhanced RETURNINGclause and Skip Scan optimization represent the kind of improvements developers use every day—these features make queries faster, code cleaner, and applications simpler without any schema redesign or complex tuning. I have picked these features from among the other exciting features due to their impact on the performance and optimization required by the application developer.

• RETURNING clause: Now you can access both OLD and NEW row values in INSERT, UPDATE, DELETE, and MERGE statements — this is perfect for auditing, API responses, and ETL workflows. This feature reduces round trips, ensures atomicity, and keeps your SQL self-contained and elegant.

• Skip Scan optimization: This optimization allows Postgres to use multi-column B-tree indexes even when leading columns aren’t filtered, unlocking major performance boosts for real-world analytical queries and reports — all without adding new indexes.

Together, these enhancements reflect Postgres 18’s core philosophy, that features should provide smarter performance and simplified development.

Postgres's success depends on diligently ensuring that all changes are carefully scrutinized and reviewed before they are added to the project source code. The skip scan feature, developed by Peter Geoghegan (a major Postgres contributor and committer), exemplifies this rigorous development process.

Understanding the Left-Most Index Problem

One of the most anticipated query optimization improvements in Postgres 18 is the B-tree skip scan capability. This feature addresses a long-standing limitation that has frustrated DBAs and developers for years, and demonstrates the Postgres community's continued commitment to making the database more performant and efficient.

Before diving into the skip scan feature, let's first understand the limitation it addresses. In previous Postgres versions, multicolumn B-tree indexes were most effective when queries included conditions on the leading columns. The index structure organizes data first by the first column, then by the second column within each first column value, and so on.

Consider a multicolumn B-tree index on (status, customer_id, and order_date). The index entries at the leaves are stored in lexicographic order:

('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')
...

A query with a predicate like status = 'active'AND customer_id = 101 would perform a single contiguous range scan, which is highly efficient. However, a query that only filters on customer_id = 101 (omitting the leading status column) would hit scattered entries across all status groups. In such cases, Postgres would typically resort to a sequential scan or use a different index if available, leaving your carefully designed multicolumn index completely unused.

This limitation forced DBAs to create multiple indexes with different column orderings to cover various query patterns, leading to increased storage overhead, slower write performance, and more complex index management.

Skip Scan to the Rescue

Postgres 18 introduces skip scan functionality for B-tree indexes, allowing the query planner to use multicolumn indexes even when early columns lack equality restrictions. This eliminates the frustrating scenario where perfectly good indexes sat unused because queries didn't filter on the first indexed column.

The skip scan optimization works by allowing Postgres to intelligently "skip"over portions of the index to find relevant data. When you query by later columns in the index without specifying the leading column, Postgres can now:

• Identify all the distinct values in the omitted leading column(s).

• Effectively transform the query to add conditions that match the leading values.

• Use existing infrastructure to optimize lookups across multiple leading columns, effectively skipping any pages in the index scan which do not match the query conditions.

This is particularly valuable for analytics and reporting workloads where you often need to query different combinations of indexed columns without always specifying the leading ones.

How Skip Scan Works - Under the Hood

Let's look at a practical example. Suppose we have an orders table with an index:

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);

Before Postgres 18, if you ran a query like:

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

The index would be largely ineffective because the query doesn't filter on the leading status column. At that point, Postgres would likely perform a sequential scan.

With Postgres 18's skip scan capability, the planner can efficiently use this index by internally transforming the query logic. It identifies the distinct values for status (for example, pending, active, and shipped), and then performs targeted index scans for each status value combined with the customer_id and order_date conditions. It essentially rewrites the query as:

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';

The key insight is that if the omitted leading column has low cardinality (a small number of distinct values), the overhead of probing each distinct value is minimal compared to a sequential scan. The planner automatically decides when skip scans provide better performance than sequential scans or other alternatives.

When Does Skip Scan Shine?

Skip scan is most beneficial in the following scenarios:

• Low Cardinality Leading Columns: The optimization is most effective when the omitted leading columns have low cardinality. If status has only 3-5 distinct values, skip scan will perform excellently. However, if it has thousands of distinct values, the benefit diminishes significantly.

• Equality Conditions on Later Columns: The skip scan implementation targets cases where later columns in the index are referenced with equality conditions. The current implementation is optimized to check for these specific patterns.

• Analytics and Reporting Workloads: Skip scan is particularly valuable for analytics queries where you need flexibility to query different column combinations. This is common in business intelligence tools and ad-hoc reporting scenarios.

• Avoiding Index Proliferation: Rather than creating multiple indexes with different column orderings, you can now rely on a single well-designed multicolumn index that skip scan can use effectively.

Important Limitations and Considerations

While skip scan is a powerful feature, it's important to understand its current limitations:

• B-tree Indexes Only: Skip scan currently works only with B-tree indexes (the most common index type).

• Performance Depends on Cardinality: The performance benefit decreases significantly as the number of distinct values in omitted columns increases. With high cardinality leading columns, you may still need dedicated indexes.

• Requires Equality Conditions: The feature requires at least one equality condition on a later column in the index. Don't expect magic for arbitrary ranges or complex predicates on later columns.

• Large Result Sets: For queries returning large result sets, traditional Bitmap/Sequential scan plans may still be the right answer. 

Practical Example and Performance Analysis

Let me demonstrate skip scan with a more detailed example. We'll create a table (sales) with realistic data distribution:

-- 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;

Now, let's query by product_category without specifying the region using Postgres 17:

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)

As you can see, with Postgres 17, this query is using a sequential scan because the leading region column isn't specified. With Postgres 18, the planner can use skip scan to efficiently utilize the index by scanning through each of the four region values and performing targeted lookups.

Now let's run the same query in 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)

The execution plan in Postgres 18 shows the skip scan in action, with significantly reduced buffer reads and improved execution time compared to a sequential scan.

Configuration and Tuning

Postgres 18 introduces the skip scan capability as part of the query planner's arsenal. The planner automatically decides when to use skip scan based on cost estimation. 

As with other planner optimizations, Postgres provides the flexibility to enable or disable skip scan through configuration, though in normal operation you should let the planner make intelligent decisions based on statistics and cost estimates.

Looking Ahead

The skip scan feature represents an important step forward in query optimization and index utilization. It demonstrates the community's commitment to continuously improving performance while maintaining Postgres's reputation for reliability and robustness.

This feature addresses a real pain point that developers and DBAs have worked around for years. By allowing more flexible use of multicolumn indexes, skip scan simplifies database design, reduces storage overhead, and improves query performance across a wide range of scenarios.

As Postgres continues to evolve, we can expect further enhancements to skip scan and other query optimization capabilities. The foundation laid in Postgres 18 will likely be built upon in future releases, potentially extending skip scan support to more complex query patterns and other index types.