PostgreSQL 18: Multi-Column Indexing Gets Smarter with Skip Scan

Asif Raza

2 min read

·

2 hours ago

--

Listen

Share

Press enter or click to view image in full size

Multi-column (composite) B-tree indexes are powerful for optimizing complex queries, but column order matters. Using them incorrectly can significantly impact query performance.

PostgreSQL 17 and Earlier: The Leftmost Prefix Rule

Consider this composite index:

CREATE INDEX CUSTOMER_COMP_IDX
ON CUSTOMER(country, state, city);

• All columns: Queries filtering by country, state, and city can efficiently use the index.
• Skipping a middle column: Queries filtering by country and city can still benefit from the index.
• Skipping the leading column: Queries filtering only by state and city generally cannot efficiently use the index.

Why? Traditional B-tree indexes rely on the leftmost column(s) to locate matching records.

PostgreSQL 18: Introducing Skip Scan

PostgreSQL 18 introduces B-tree Skip Scan, making composite indexes more flexible.

Consider:

SELECT *
FROM CUSTOMER
WHERE state = 'IL'
AND city = 'Chicago';

Previously, this query could require a sequential scan. With Skip Scan, PostgreSQL can dynamically iterate through distinct country values and search for matching state and city combinations within the index.

Key takeaway: Skip Scan is particularly beneficial when the omitted leading column has relatively few distinct values.

Best Practices

• Don’t create indexes without understanding actual query patterns.
• Use composite indexes when queries require multiple-column filtering.
• Consider column order carefully.
• Avoid over-indexing because indexes consume storage and increase INSERT, UPDATE, and DELETE costs.
• PostgreSQL supports up to 32 index columns, but wider indexes aren’t necessarily better.

Remember: PostgreSQL 18 Skip Scan improves composite B-tree index utilization, but it doesn’t eliminate the importance of proper index design.