Multicolumn indexes are one of the most useful tools for improving PostgreSQL query performance.
A common index looks like this:
CREATE INDEX idx_orders_customer_status
ON orders (customer_id, status);
Traditionally, the order of the columns matters.
A query filtering on:
WHERE customer_id = 100
AND status = 'Pending'
is an obvious candidate for that index.
But what happens when the query filters only on the second column?
WHERE status = 'Pending'
Historically, a B-tree index whose leading column is customer_id was much less useful for directly narrowing the scan.
PostgreSQL 18 introduces skip scan support for multicolumn B-tree indexes, allowing the planner to use such indexes in more cases when a later indexed column has a useful restriction but one or more earlier columns lack an equality condition.
The important word is can.
Skip scan is an optimizer choice, not a guarantee that every query will use the multicolumn index.
The practical question is therefore:
When does skip scan make an existing multicolumn index useful, and when is a dedicated index still better?
What Is a Multicolumn B-Tree Index?
Consider:
CREATE INDEX idx_orders_customer_status
ON orders (customer_id, status);
The logical index order is:
customer_id
↓
status
The leading column is customer_id.
A query such as:
SELECT *
FROM orders
WHERE customer_id = 100
AND status = 'Pending';
matches the index structure naturally.
PostgreSQL documentation explains that B-tree multicolumn indexes are most effective when constraints exist on the leading columns.
The Traditional Problem With the Second Column
Now consider:
SELECT *
FROM orders
WHERE status = 'Pending';
The query does not constrain:
customer_id
which is the first index column.
The database cannot simply navigate directly to one contiguous range based only on status because the index is ordered primarily by customer_id.
Conceptually, the index may look like:
customer_id = 1
Pending
Shipped
Cancelled
customer_id = 2
Pending
Shipped
Cancelled
customer_id = 3
Pending
Shipped
Cancelled
A search for:
status = Pending
has matching entries spread across the leading-column groups.
What Skip Scan Changes
PostgreSQL 18 can sometimes transform that search conceptually into repeated searches:
customer_id = 1 AND status = 'Pending'
customer_id = 2 AND status = 'Pending'
customer_id = 3 AND status = 'Pending'
...
This is not literal SQL rewriting.
It is an internal index-navigation strategy.
PostgreSQL's documentation describes skip scan as generating an internal equality condition for an underspecified leading column and repeatedly navigating the index when that is expected to be efficient.
The strategy is particularly attractive when the skipped leading column has relatively few distinct values.
A Simple Example
Create a table:
CREATE TABLE orders
(
order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id integer NOT NULL,
status text NOT NULL,
total numeric(12, 2) NOT NULL,
created_at timestamptz NOT NULL
);
Create the multicolumn index:
CREATE INDEX idx_orders_customer_status
ON orders (customer_id, status);
Now query only the second column:
SELECT order_id, customer_id, status
FROM orders
WHERE status = 'Pending';
PostgreSQL 18 can consider skip scan if the cost model predicts that repeatedly navigating the index is cheaper than the alternatives.
The Cardinality of the Leading Column Matters
This is one of the most important practical details.
Suppose:
customer_id
Distinct values = 20
The planner may consider repeated searches relatively inexpensive.
Now suppose:
customer_id
Distinct values = 10,000,000
Repeatedly searching the index for every possible customer value may be much less attractive.
PostgreSQL's documentation explicitly notes that skip scan is generally useful when the leading column has relatively few distinct values. With many distinct values, a sequential scan may be cheaper.
Therefore:
Low cardinality leading column
+
Selective later-column predicate
=
Potentially good skip-scan candidate
Example With a Low-Cardinality Column
A more obvious example is:
CREATE TABLE events
(
event_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
event_type text NOT NULL,
tenant_id bigint NOT NULL,
payload jsonb NOT NULL
);
Suppose:
event_type
=
Login
Logout
Purchase
PasswordReset
and the index is:
CREATE INDEX idx_events_type_tenant
ON events (event_type, tenant_id);
Now query:
SELECT event_id, event_type, tenant_id
FROM events
WHERE tenant_id = 5001;
The leading column has only a small number of distinct values.
PostgreSQL 18 may navigate the index through the relevant event_type groups rather than treating the entire index as one undifferentiated structure.
Skip Scan Is Cost-Based
Do not assume:
Multicolumn index exists
+
Later column has a filter
=
Skip scan
The planner evaluates alternatives.
It may choose:
Index Scan
or:
Index Only Scan
or:
Seq Scan
depending on estimated cost.
The PostgreSQL documentation specifically states that the optimizer chooses skip scan when it expects the repeated index searches to be the fastest approach.
Use EXPLAIN ANALYZE
The best way to determine what actually happened is:
EXPLAIN (ANALYZE, BUFFERS)
SELECT event_id, event_type, tenant_id
FROM events
WHERE tenant_id = 5001;
Look for:
Index Scan
or:
Index Only Scan
and inspect:
Index Cond
Index Searches
Buffers
Execution Time
PostgreSQL 18 adds Index Searches information to EXPLAIN ANALYZE, which is especially useful when a B-tree scan applies skip scan.
Understanding Index Searches
Consider the conceptual query:
SELECT four, unique1
FROM tenk1
WHERE four BETWEEN 1 AND 3
AND unique1 = 42;
PostgreSQL's documentation shows an index-only scan using a multicolumn index and:
Index Searches: 3
The optimizer effectively performs separate index searches for the relevant leading-column values.
This is valuable diagnostic information.
Instead of merely seeing:
Index Only Scan
you can see evidence of repeated index navigation.
Skip Scan Is Not Loose Index Scan
These terms are sometimes confused.
PostgreSQL's skip scan is about efficiently navigating a multicolumn B-tree when an earlier indexed column lacks an equality condition but a later column has a useful restriction.
It is not the same as a loose index scan used for certain DISTINCT or GROUP BY patterns.
The PostgreSQL development discussion explicitly distinguishes these two concepts.
That distinction matters when designing benchmarks.
A Benchmark Setup
To test skip scan properly, create a controlled workload.
For example:
CREATE TABLE customer_events
(
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
event_type smallint NOT NULL,
customer_id bigint NOT NULL,
created_at timestamptz NOT NULL,
payload text NOT NULL
);
Create a multicolumn index:
CREATE INDEX idx_customer_events_type_customer
ON customer_events (event_type, customer_id);
Populate representative data:
INSERT INTO customer_events
(
event_type,
customer_id,
created_at,
payload
)
SELECT
(random() * 4)::int,
(random() * 1000000)::bigint,
now() - (random() * interval '365 days'),
md5(random()::text)
FROM generate_series(1, 5000000);
Then refresh statistics:
ANALYZE customer_events;
The exact data volume should be selected based on the machine and workload being tested.
Do not present benchmark numbers as universal performance guarantees.
Test the Second Column
Run:
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, customer_id
FROM customer_events
WHERE customer_id = 500001;
The interesting question is whether PostgreSQL chooses the multicolumn index and whether the plan exposes multiple index searches.
Compare Against a Dedicated Index
Now create:
CREATE INDEX idx_customer_events_customer
ON customer_events (customer_id);
Run the same query:
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, customer_id
FROM customer_events
WHERE customer_id = 500001;
Now you have two possible strategies:
Multicolumn index + skip scan
versus:
Dedicated single-column index
This is the comparison that matters in production.
Benchmark More Than Execution Time
Do not record only:
Execution Time
Also capture:
Planning Time
Execution Time
Buffers
Index Searches
Rows returned
Rows scanned
Plan type
For repeated tests, consider:
Median
P95
P99
rather than reporting only one execution.
The goal is to understand behavior, not manufacture a benchmark result.
Test Different Cardinalities
A useful experiment changes the number of distinct values in the leading column.
For example:
| Leading Column Cardinality | Query on Second Column | Expected Question |
|---|
| 2 | Yes | Is skip scan attractive? |
| 10 | Yes | Does repeated navigation remain efficient? |
| 100 | Yes | Where does the benefit change? |
| 1,000 | Yes | Does planner prefer another plan? |
| 100,000 | Yes | Is sequential scan cheaper? |
Do not assume the transition happens at a fixed cardinality.
The planner's decision depends on:
Data distribution
Table size
Index size
Statistics
Selectivity
Cache state
Hardware
Query shape
Test Selectivity
Cardinality is not the only variable.
Consider:
WHERE customer_id = 500001
versus:
WHERE customer_id BETWEEN 1 AND 900000
The second query may match a large portion of the table.
Even if skip scan is technically possible, it may not be the most efficient strategy.
Test Data Distribution
Uniform random data and highly skewed data can produce very different plans.
For example:
90% of events
=
event_type 1
versus:
20% per event_type
The planner's estimates depend on statistics describing the data distribution.
Always benchmark with representative distributions.
Run ANALYZE
After loading test data:
ANALYZE customer_events;
is important because PostgreSQL uses statistics for planning.
If statistics do not reflect the current data distribution, the optimizer may make poor cost estimates.
Compare With a Sequential Scan
A complete benchmark should include:
Candidate A:
Multicolumn index
Candidate B:
Dedicated index
Candidate C:
Sequential scan
This matters because the optimizer is not choosing between only two index designs.
It can decide that scanning the table is cheaper.
Use BUFFERS for Diagnostic Information
Run:
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, customer_id
FROM customer_events
WHERE customer_id = 500001;
Look at:
shared hit
shared read
shared dirtied
This helps distinguish CPU/query-navigation effects from storage/cache effects.
For a production investigation, buffer behavior can be more informative than execution time alone.
Index-Only Scan Can Change the Result
Suppose your query needs only:
SELECT customer_id
and the index contains the required data.
PostgreSQL may use:
Index Only Scan
rather than:
Index Scan
This can change the cost comparison significantly.
Therefore, benchmark both:
SELECT customer_id
and:
SELECT *
when those query shapes exist in the real workload.
Covering Indexes Change the Trade-Off
You might consider:
CREATE INDEX idx_events_type_customer
ON customer_events (event_type, customer_id)
INCLUDE (created_at);
This can support index-only queries that need created_at.
But additional columns increase index size and write overhead.
PostgreSQL documentation emphasizes that indexes add system overhead and should be used sensibly.
Skip scan does not eliminate the cost of maintaining indexes.
Skip Scan Does Not Mean Column Order No Longer Matters
This is a critical misconception.
Suppose:
CREATE INDEX idx_orders_customer_status
ON orders (customer_id, status);
and:
WHERE status = 'Pending'
can benefit from skip scan.
That does not mean:
(customer_id, status)
and:
(status, customer_id)
are interchangeable.
The order still matters for:
Equality predicates
Range predicates
Ordering
Join conditions
Covering behavior
Other query patterns
Skip scan expands the useful cases for a multicolumn B-tree.
It does not remove the importance of index design.
Compare Query Patterns Before Designing the Index
Suppose your workload contains:
WHERE customer_id = ?
WHERE customer_id = ?
AND status = ?
WHERE status = ?
ORDER BY customer_id, status
The ideal index depends on the complete workload.
Possible indexes include:
(customer_id, status)
(status)
or:
(status, customer_id)
or a combination.
PostgreSQL's documentation recommends considering workload patterns rather than automatically creating every possible index.
Multicolumn Index vs Separate Indexes
Consider:
CREATE INDEX idx_a_b
ON table_name (a, b);
versus:
CREATE INDEX idx_a
ON table_name (a);
CREATE INDEX idx_b
ON table_name (b);
The choice depends on the workload.
Separate indexes can sometimes be combined using bitmap scans.
PostgreSQL documents that it can combine multiple indexes using AND and OR operations, although this introduces its own trade-offs.
Skip scan adds another option to this design space.
When Skip Scan Can Be Attractive
A strong candidate often has:
Multicolumn B-tree index
+
Low-cardinality leading column
+
Selective later-column predicate
+
Large enough index/table
For example:
(event_type, customer_id)
where:
event_type
=
small number of values
customer_id
=
many values
A query on customer_id may be a reasonable skip-scan candidate.
When a Dedicated Index May Still Win
A dedicated index on the later column can be preferable when:
Later-column queries are extremely common
Leading column has high cardinality
The dedicated index is significantly smaller
Query latency is highly sensitive
The workload needs predictable access paths
The correct decision comes from workload measurement.
Do not add a second index simply because skip scan exists.
Write Amplification Matters
Every additional index increases write work.
For an insert:
INSERT
↓
Table
↓
Index 1
↓
Index 2
↓
Index 3
Updates can also require index maintenance.
Therefore:
Faster read
must be evaluated against:
More storage
More write work
More vacuum/index maintenance
More operational complexity
PostgreSQL explicitly warns that indexes add overhead to the database system.
Check Production Query Patterns
Before changing an index, inspect real query behavior.
Useful sources include:
pg_stat_statements
Application logs
APM traces
Database monitoring
EXPLAIN plans
Look for:
Frequently executed queries
High total execution time
High average latency
High buffer usage
Repeated filtering on later index columns
The objective is to optimize actual workload patterns rather than synthetic queries.
Use EXPLAIN Before and After
For an existing workload:
EXPLAIN (ANALYZE, BUFFERS)
SELECT ...
Record:
Plan
Execution Time
Buffers
Index Searches
Rows
Then make the index change and repeat the measurement.
This gives you evidence for the architectural decision.
Common Mistakes
Assuming PostgreSQL Always Uses Skip Scan
It is cost-based.
Assuming Skip Scan Makes Index Column Order Irrelevant
Column order remains important.
Confusing Skip Scan With Loose Index Scan
They are different techniques.
Benchmarking Only One Data Distribution
Planner behavior can change with cardinality and skew.
Measuring Only Execution Time
Include buffers and plan details.
Ignoring Statistics
Run ANALYZE after representative data changes.
Creating Every Possible Index
Additional indexes increase storage and write overhead.
Ignoring Query Shape
SELECT * and index-only queries can produce very different plans.
Assuming One Benchmark Applies Everywhere
Hardware, cache state, table size, data distribution, and workload all matter.
Troubleshooting
PostgreSQL Does Not Use the Multicolumn Index
Check:
Statistics
Predicate selectivity
Leading-column cardinality
Table size
Available indexes
Estimated cost
Then run:
EXPLAIN (ANALYZE, BUFFERS)
...
Do not force an index before understanding the planner's decision.
Skip Scan Works on One Database but Not Another
Compare:
PostgreSQL version
Statistics
Data distribution
Table size
Index definitions
Configuration
Cache state
A planner decision is workload-dependent.
Index Searches Is Higher Than Expected
Do not assume it must equal the number of distinct values in the skipped column.
The number of index searches reflects how the executor navigates the index and can differ from a simple cardinality count. PostgreSQL exposes this value specifically to help understand index-scan behavior.
Sequential Scan Is Faster
That can be the correct result.
If a query returns a large percentage of the table, a sequential scan may be cheaper than repeated index navigation.
Adding a Dedicated Index Improves Reads but Hurts Writes
Measure the complete workload.
The index may be beneficial for latency-sensitive reads but too expensive for a write-heavy system.
Best Practices
Understand the complete query workload before designing indexes.
Remember that B-tree column order still matters.
Use skip scan as an optimizer capability, not a guarantee.
Pay attention to leading-column cardinality.
Benchmark representative data distributions.
Run ANALYZE after significant data changes.
Use EXPLAIN (ANALYZE, BUFFERS).
Inspect Index Searches in PostgreSQL 18.
Compare skip scan with dedicated indexes.
Compare both against sequential scans.
Measure read and write workloads.
Consider index storage and maintenance cost.
Avoid creating redundant indexes.
Test index-only query patterns separately.
Distinguish skip scan from loose index scan.
Validate changes using production-like workloads.
Do not force an index without evidence.
Monitor query plans after deployment.
Benchmark Matrix
A useful PostgreSQL 18 experiment can use this matrix:
| Test | Leading Cardinality | Predicate | Index | Primary Observation |
|---|
| A | Low | Second column equality | Multicolumn | Skip-scan potential |
| B | Medium | Second column equality | Multicolumn | Planner threshold |
| C | High | Second column equality | Multicolumn | Alternative plan |
| D | Low | Second column equality | Dedicated index | Baseline |
| E | Low | Broad range | Multicolumn | Selectivity effect |
| F | Low | Second column equality | No index | Sequential-scan baseline |
This produces a much more useful analysis than a single benchmark.
A Practical Decision Framework
When you have:
INDEX (A, B)
and a query:
WHERE B = ?
ask:
1. How many distinct values does A have?
2. How selective is B?
3. How large is the table?
4. How large is the index?
5. How frequently is this query executed?
6. Does PostgreSQL choose skip scan?
7. How many Index Searches occur?
8. Would INDEX (B) perform better?
9. What is the write overhead of another index?
10. Does the complete workload justify the additional index?
This turns skip scan from a feature announcement into an indexing decision.
Frequently Asked Questions
What is PostgreSQL 18 skip scan?
Skip scan is an optimization for multicolumn B-tree indexes that can make them useful for queries where one or more leading indexed columns lack equality restrictions but a later column has a useful predicate. PostgreSQL 18 officially introduced this capability.
Does skip scan work with every multicolumn index?
The PostgreSQL 18 feature discussed here applies to multicolumn B-tree indexes. Other index types have different multicolumn behavior.
Does PostgreSQL always use skip scan?
No.
The optimizer chooses it based on estimated cost.
Is skip scan useful when the leading column has millions of distinct values?
Usually it is less attractive because repeated searches across many distinct leading values can become expensive. PostgreSQL's documentation specifically describes low distinct counts in the skipped column as a favorable condition.
Does skip scan replace single-column indexes?
No.
A dedicated index on the queried column may still be better for frequently executed queries or workloads where the leading column has high cardinality.
How can I tell whether skip scan is being used?
Use:
EXPLAIN (ANALYZE, BUFFERS)
and inspect the plan, particularly Index Searches in PostgreSQL 18.
What is the difference between skip scan and loose index scan?
PostgreSQL's skip scan efficiently navigates a multicolumn B-tree when earlier columns lack equality conditions.
Loose index scan is a different optimization associated with certain DISTINCT or GROUP BY patterns.
They should not be treated as interchangeable concepts.
Should I remove existing indexes because PostgreSQL 18 supports skip scan?
Not automatically.
Existing indexes may still provide better plans for common queries.
Measure before removing anything.
Does skip scan reduce index storage?
No.
It changes how the existing B-tree can be navigated.
The index still consumes storage and incurs maintenance overhead.
Conclusion
PostgreSQL 18 changes an important assumption about multicolumn B-tree indexes.
Previously, developers often had to think in terms of:
Leading column
↓
Later column
when evaluating whether a B-tree index could efficiently support a query.
With skip scan, PostgreSQL can sometimes navigate:
(A, B)
for a query that primarily restricts:
B
by internally performing repeated searches across relevant values of A.
But this does not mean:
Every (A, B) index
=
Good index for B
The optimizer still considers:
Cardinality
Selectivity
Table size
Statistics
Available indexes
Query shape
Estimated cost
The most useful production workflow is therefore:
Real query
↓
EXPLAIN ANALYZE
↓
Inspect plan
↓
Inspect Index Searches
↓
Compare dedicated index
↓
Measure buffers
↓
Measure read/write trade-offs
↓
Choose based on workload
PostgreSQL 18's Index Searches information makes this analysis particularly useful because it exposes how many index searches an executor node performed, including searches associated with skip-scan navigation.
The practical lesson is straightforward:
Skip scan makes some previously awkward multicolumn-index queries more viable, but it does not eliminate the need for thoughtful index design.
Before adding or removing an index, measure the actual workload.
In PostgreSQL, the best index is not the one that looks most powerful on paper.
It is the one that provides the right access path for the queries your system actually runs while keeping storage, write, and maintenance costs under control.
Join the conversation! Your thoughts help the community grow.