PostgreSQL 18 Skip Scan Delivers 21x Speedup on Aurora

Anyone who has been paged at 3am because a composite index stopped matching a WHERE clause pattern knows the shape of this problem. PostgreSQL 18 just cut a representative query from 73.2 ms to 3.4 ms on the same hardware, a 21x improvement, without adding a single new index. That is the headline number out of AWS this week, and it changes the arithmetic on how a lot of teams design their B-tree layouts.

The Numbers

The setup is boring in the best possible way, which is what makes it credible. As Amazon Web Services (AWS) reported, the benchmark ran on a db.r6g.large instance, a modest ARM box that plenty of teams use in staging and small production tiers. The table held one million rows in a customer_orders schema with a composite index on (status, created_date). The status column had exactly 5 distinct values. Both runs used a warm buffer cache, so this is not an I/O trick.

On Aurora PostgreSQL 17.10, the planner did what it has always done when the leading column is missing from the WHERE clause: it fell back to a Parallel Seq Scan with 2 workers planned and 2 workers launched. It read 7,353 shared blocks. Each worker loop filtered out 332,433 rows. Planning time was 0.091 ms, execution time was 73.200 ms.

On Aurora PostgreSQL 18.4, same instance, same data, same query filtering created_date = '2026-03-15', the planner switched to a Bitmap Index Scan on the composite index. It performed 11 index searches, one per distinct leading value plus overhead, and read 2,266 shared blocks. Planning time was 0.065 ms. Execution time was 3.374 ms.

So the block count dropped by roughly 69%. Execution time dropped by roughly 95%. That is the 21x figure, and it is the kind of number that pays for a migration on its own.

Think about what that means in operational budget terms. A query that fires 500 times a second on a busy checkout path just gave you back roughly 35 seconds of CPU time per wall-clock second across the fleet. On a hot iGaming settlement service, that is the difference between needing a bigger writer and not. I have seen production incidents where teams sharded prematurely because a single planner limitation like this looked structural. It was not. It was a version away.

My take: the 5-distinct-values detail is the one to internalize. Skip scan wins big when leading columns have low cardinality. As cardinality climbs, the 11 index searches become 11,000 and the math changes.

What's Actually New

The four items in Part 1 of the AWS series are skip scan optimization for multicolumn B-tree indexes, enhanced EXPLAIN output that exposes storage behavior, automatic removal of unnecessary self-joins, and vacuum/autovacuum improvements. Part 2 will cover security, monitoring, developer, and logical replication changes. That is a lot of surface area, but skip scan is the one that reshapes schema design.

Here is what changed underneath. In PostgreSQL 17 and earlier, a multicolumn index on (a, b) was only usable when the WHERE clause referenced a, or a and b together. Filter on b alone and the planner had two options: sequential scan, or build a second index just on b. Teams built the second index. Then the third. Then the write amplification bill arrived.

Skip scan lets the planner iterate through the distinct values of the leading column and probe the index for each one. The PostgreSQL docs describe the B-tree access method well enough that the mechanic is intuitive once you see it: instead of "scan from the leading key", the planner does "for each distinct leading key, scan". Eleven index searches in the AWS benchmark maps directly to that: 5 distinct status values plus the boundary probes the executor needs.

The enhanced EXPLAIN output is quietly the second most important change. The version 18 plan surfaces Index Searches: 11 as a first-class line item. That is exactly the signal you need to catch skip scan misuse in code review or in a slow query log. Without that visibility, you would be reverse-engineering the strategy from buffer counts.

Optimizer statistics that survive major version upgrades is the operational sleeper hit. Anyone who has done a Postgres major bump knows the ritual: cut over, watch dashboards, wait for the plans to regress because pg_statistic got wiped, then run ANALYZE at 2am while praying autovacuum does not stampede. Keeping stats across the upgrade removes an entire category of post-cutover incident.

Automatic self-join removal is a nice compiler-level win for ORM-generated SQL. Vacuum improvements matter for anyone running high-churn tables. Neither is as headline-worthy as skip scan.

What's Priced In for Engineering Teams

Most senior Postgres teams already assumed PG18 would land skip scan. It has been discussed in the -hackers list for years, and Oracle and SQL Server have had equivalents for a decade. What is priced in: the feature exists, it works on low-cardinality leading columns, it will not save you on high-cardinality ones. Nobody sensible is ripping out their btree_gin indexes on the strength of a blog post.

What is not priced in, at least based on the platform leads I have compared notes with: the sheer size of the index cleanup opportunity. A lot of production schemas carry two, three, four "just in case" single-column indexes that were added specifically because a composite index would not cover a query pattern. Each of those indexes costs write throughput and WAL volume on every INSERT and UPDATE. Skip scan lets you delete some of them.

The uncomfortable read: most teams will not do that cleanup. They will get PG18, keep every existing index, and just absorb the skip scan win as bonus performance on queries that were already fine. The teams that audit their index list and drop redundancies are the ones who will see the write-side gains, and those gains tend to be bigger than the read-side headline number over a quarter.

Also worth flagging: Aurora and RDS timelines are aligned here, which is not always the case. Both services have PG18 available now. Teams on RDS do not have to wait a release cycle behind Aurora for this one.

Contrarian View

Skip scan is a footgun in the wrong hands. The AWS benchmark used 5 distinct status values, which is close to the ideal case. Point that same optimization at a leading column with a million distinct values and the planner will burn CPU doing index probes when a sequential scan would finish faster. The planner is supposed to cost-model its way out of that, but planner cost models are famously optimistic on synthetic data and pessimistic on real data.

The second contrarian angle: enhanced EXPLAIN output is going to generate a wave of "why is my query slow now" tickets. Teams that previously did not see Index Searches in their plans will start seeing high numbers and panic. Expect a spike in Stack Overflow questions and a lot of premature index tuning.

Third, the "stats survive major version upgrade" feature is going to lull some teams into skipping the post-upgrade validation window. That window exists for reasons beyond pg_statistic. Do not shorten your canary period just because one class of regression got easier.

Key Takeaways

• PostgreSQL 18 skip scan turned a 73.2 ms parallel sequential scan into a 3.4 ms bitmap index scan on the AWS benchmark, a 21x speedup on a db.r6g.large with one million rows.
• The win is largest when the leading column of the composite index has low cardinality. Five distinct values yielded 11 index searches. High-cardinality leading columns will not see the same result.
• Audit your index list after upgrading. The real payoff is deleting redundant single-column indexes that were only added to work around the old planner limitation, cutting write amplification and WAL volume.
• Optimizer statistics that survive major version upgrades remove a common cause of post-cutover plan regressions, but do not use that as an excuse to shorten your canary window.
• Enhanced EXPLAIN output now exposes Index Searches as a first-class metric. Add it to your slow query dashboards before you need it.