How to Unlock Faster Queries in PostgreSQL 18 with Index Skip Scan

In our previous blog, we explored Async I/O in PostgreSQL 18 and how it improves scan performance by making disk access more efficient. That feature focused on reducing I/O wait time and accelerating large table scans.

In this post, we’ll examine another powerful performance improvement in PostgreSQL 18:

Index Skip Scan

Originally introduced in PostgreSQL 15 (2022), Index Skip Scan is one of the most impact yet often overlooked planner enhancements in recent years. By PostgreSQL 18 (2025–2026), it has become more mature, more stable, and is automatically used in many real-world workloads.

This feature allows PostgreSQL to use multi-column (composite) indexes more intelligently, especially in queries using DISTINCT, GROUP BY, or MIN/MAX. In certain cases, it can dramatically reduce the number of index entries scanned, resulting in significantly faster queries without requiring any changes to your existing indexes.

Understanding the Problem

Composite indexes are ordered based on the sequence of columns defined in the index. PostgreSQL always navigates the index starting from the leading (first) column. If a query does not filter or group by that leading column, the planner cannot directly jump to the relevant section of the index.

Assume we have a composite index like this:

CREATE INDEX idx_orders_tenant_date
ON orders (tenant_id, created_at)

This index is ordered first by tenant_id, then by created_at.

Now consider the following query:

SELECT DISTINCT tenant_id
FROM orders
WHERE created_at >= '2026-01-01'
ORDER BY tenant_id

If a query filters only on created_at, PostgreSQL cannot efficiently use the index structure because it is physically ordered by tenant_id first.

Without Index Skip Scan,

• Scan a large portion of the index
• Apply the filter condition afterward
• Perform sorting or aggregation separately

Even if the final result contains only a small number of distinct values, the database may still read millions of index entries.

This inefficiency becomes more noticeable when:

• The table is very large
• The leading column has many repeated values
• The query uses DISTINCT, GROUP BY, or MIN/MAX

As the dataset grows, scanning unnecessary index entries increases I/O and CPU usage. Before Skip Scan was introduced, developers often solved this by:

• Creating additional indexes
• Reordering index columns
• Restructuring queries

What Index Skip Scan Changes

Index Skip Scan improves how PostgreSQL reads the index.Instead of scanning every matching row, PostgreSQL,

• Finds the first row for a specific tenant_id
• Applies the filter (created_at >= ...)
• Jumps directly to the next distinct tenant_id
• Repeats the process

In other words, it skips large portions of the index rather than scanning everything.As a result, PostgreSQL may read only one or a few index entries per distinct value instead of millions of rows. This significantly reduces unnecessary I/O and CPU work.

Real Example with EXPLAIN ANALYZE

To understand the real impact of Index Skip Scan, let’s look at an actual execution plan comparison from a large production-style dataset.

Assume the transactions table contains around 180 million rows, and there is a composite B-tree index on,

We run the following reporting query:

SELECT account_id, MAX(transaction_date)
FROM transactions
WHERE transaction_date >= '2024-01-01'
GROUP BY account_id
ORDER BY account_id

This query calculates the latest transaction per account for a given period — a very common financial reporting pattern.

Parallel Sequential Scan

transactions=# EXPLAIN (ANALYZE, BUFFERS)
SELECT account_id, MAX(transaction_date)
FROM transactions
WHERE transaction_date >= '2024-01-01'
GROUP BY account_id
ORDER BY account_id;
Gather Merge  (actual time=235000.342..248765.983 rows=2000 loops=1)
  Workers Planned: 4; Workers Launched: 4
  Buffers: shared hit=850000 read=2100000
  ->  Sort  (actual time=230000.112..230001.876 rows=500 loops=4)
        Sort Key: account_id
        ->  Partial HashAggregate  (actual time=228000.344..229500.223 rows=500 loops=4)
              ->  Parallel Seq Scan on transactions
                    (actual time=0.034..210000.789 rows=45000000 loops=4)
                    Filter: (transaction_date >= '2024-01-01'::date)
                    Rows Removed by Filter: 100000000Planning Time: 1.142 ms
Execution Time: 248900.114 ms

Final execution time: 248,900 ms (more than 4 minutes)

• PostgreSQL performed a parallel sequential scan
• Around 45 million rows per worker were scanned
• Heavy aggregation and sorting occurred
• Massive buffer reads (2+ million blocks)

Even though the final result contains only 2,000 distinct accounts, PostgreSQL had to scan and process tens of millions of rows before performing the aggregation. The database spent most of its time reading data and grouping it afterward — this is exactly where performance breaks down in large tables.

Index Skip Scan (PostgreSQL 18)

Now let’s see the same query on PostgreSQL 18.

transactions=# EXPLAIN (ANALYZE, BUFFERS)
SELECT account_id, MAX(transaction_date)
FROM transactions
WHERE transaction_date >= '2024-01-01'
GROUP BY account_id
ORDER BY account_id;
Index Skip Scan using idx_transactions_account_date on transactions
(actual time=0.048..18542.672 rows=2000 loops=1)
  Output: account_id, max(transaction_date)
  Filter: (transaction_date >= '2024-01-01'::date)
  Heap Fetches: 2000
  Buffers: shared hit=15200 read=8400
  Index Searches: 2000
Planning Time: 0.893 ms
Execution Time: 18560.214 ms

Final execution time: 18560 ms (under 20 sec)

Instead of scanning millions of rows, PostgreSQL:

• Used the composite index efficiently
• Performed exactly one index search per distinct account_id
• Avoided full table scanning
• Eliminated heavy aggregation overhead

When PostgreSQL Chooses Index Skip Scan

Index Skip Scan is not manually enabled. PostgreSQL automatically decides whether to use it based on cost estimation and statistics.

The planner is most likely to choose Index Skip Scan when:

• The query uses DISTINCT, GROUP BY, MIN(), or MAX()
• A composite B-tree index exists
• The leading column has relatively low cardinality (many repeated values)
• The table is large
• A full index scan or sequential scan is estimated to be expensive

For example, with an index like:

(account_id, transaction_date)

And a query such as:

SELECT account_id, MAX(transaction_date)
FROM transactions
WHERE transaction_date >= '2024-01-01'
GROUP BY account_id;

If PostgreSQL estimates that scanning millions of rows is more expensive than performing controlled index jumps, it will automatically use Index Skip Scan. This decision is fully cost-based, there is no special configuration parameter to enable it.

When It Does NOT Help

Index Skip Scan is powerful, but it is not a magic solution for every query.

It will not help when:

• The leading column is highly unique (for example, nearly one row per value)
• The table is small
• There is no DISTINCT, GROUP BY, or MIN/MAX
• The composite index does not match the query pattern
• Statistics are outdated

For example, if account_id is unique for every row, there is nothing to “skip.” In that case, Skip Scan provides no advantage over a regular index scan.

It is also important to understand that Skip Scan works only with B-tree indexes. It does not apply to GIN, BRIN, or Hash indexes.

How to Test and Tune It

Before assuming Index Skip Scan is working, you should verify it using execution plans and basic planner checks. Since it is a cost-based optimization, small changes in statistics or configuration can affect whether PostgreSQL chooses it.

Verify with EXPLAIN

• Run: EXPLAIN (ANALYZE, BUFFERS) <your_query>;
• Look for: Index Skip Scan using …
• If you see Seq Scan or large HashAggregate, Skip Scan is not being used.

Keep Statistics Updated

• Run: ANALYZE transactions;
• Outdated statistics can prevent the planner from selecting Skip Scan.

Confirm Index Design

• Requires a composite B-tree index.
• Column order must match the query pattern.
• Good: (account_id, transaction_date)
• Poor: (transaction_date, account_id)

Final Takeaway

Index Skip Scan is one of the most practical and impactful planner improvements in modern PostgreSQL. It allows the database to intelligently navigate composite B-tree indexes by jumping between distinct leading-column values instead of scanning every matching row.

In large datasets, this can dramatically reduce I/O, CPU usage, and execution time. As demonstrated earlier, queries that previously took several minutes can drop to seconds when Skip Scan is chosen.

However, it is not automatic magic for every query. The benefit depends on:

• Proper composite index design
• Low-to-medium cardinality of the leading column
• Queries using DISTINCT, GROUP BY, or MIN/MAX
• Accurate statistics

The good news is that in PostgreSQL 18, no special configuration is required. If the index and data distribution are suitable, the planner will automatically choose Index Skip Scan when it is the cheapest option.

If you see “Index Skip Scan” in your execution plan, it means PostgreSQL is efficiently skipping unnecessary work — and that usually translates directly into better performance in real production systems.