PostgreSQL 18 Performance Internals: Optimizer and Indexing Advancements

PostgreSQL 18 performance internals are the story of this release — the gains are architectural, not incremental. The three changes that matter most in production are the new asynchronous I/O (AIO) subsystem, B-tree skip scan, and a set of planner transformations (self-join elimination, OR-to-array rewriting, DISTINCT key reordering) that fix long-standing plan-shape problems without any SQL changes. In this deep dive we verify each claim on a live PostgreSQL 18.4 instance with a 5-million-row dataset, show the real EXPLAIN (ANALYZE, BUFFERS) output, and explain the internals so you can predict when each optimization will — and will not — fire. Everything below is reproducible: full DDL, data generator, and configuration are included. If you are evaluating PostgreSQL 18 performance internals ahead of an OLTP or HTAP upgrade, this is the internals-level view behind our upgrade recommendation at the end.

Lab Environment and Methodology

Every PostgreSQL 18 performance internals claim in this article was captured on PostgreSQL 18.4 (x86_64, Linux, 18.x current minor at the time of writing), running with default settings except where a parameter change is explicitly shown. The dataset is a synthetic 5,000,000-row orders table — 8 distinct region_id values and 200,000 distinct customer_id values — deliberately shaped to exercise skip scan cardinality math:

CREATE TABLE orders (
    order_id      bigint GENERATED ALWAYS AS IDENTITY,
    region_id     smallint NOT NULL,
    customer_id   integer  NOT NULL,
    order_date    date     NOT NULL,
    amount        numeric(12,2) NOT NULL
);

INSERT INTO orders (region_id, customer_id, order_date, amount)
SELECT (g % 8) + 1,
       (g % 200000) + 1,
       DATE '2025-01-01' + (g % 365),
       round((random() * 900 + 100)::numeric, 2)
FROM generate_series(1, 5000000) AS g;

ALTER TABLE orders ADD CONSTRAINT pk_orders PRIMARY KEY (order_id);

CREATE INDEX idx_orders_region_customer
    ON orders (region_id, customer_id);

VACUUM ANALYZE orders;

Timing comparisons report the median of 3 runs, with shared buffers cold (server restart between runs) and OS page cache warm — this isolates the I/O submission path rather than raw device latency. The environment is a container on NVMe-backed storage, so treat the absolute numbers as directional for your hardware; the plan shapes and buffer counts are hardware-independent. As always: validate on your own workload in staging before touching production, and keep your DR posture current.

Asynchronous I/O: PostgreSQL 18 Performance Internals at the Storage Layer

For thirty years, a PostgreSQL backend read data pages the same way: issue a synchronous read(), wait, process, repeat. Prefetch relied on posix_fadvise() hints, which merely asked the kernel to be helpful. The single largest advancement in PostgreSQL 18 performance internals replaces this with a true asynchronous I/O subsystem (work by Andres Freund, Thomas Munro, Nazir Bilal Yavuz, and Melanie Plageman): backends now queue batches of read requests and continue working while I/O completes in the background. Sequential scans, bitmap heap scans, and VACUUM use the new read-stream infrastructure in 18.0.

The tuning surface for these PostgreSQL 18 performance internals is three new GUCs plus two revised defaults. Reload-vs-restart semantics matter here, so we list them precisely:

Parameter	Default (18)	Prior default	Change scope
io_method	worker	n/a (new)	restart (postmaster)
io_workers	3	n/a (new)	reload (sighup)
io_combine_limit	16 (128 kB)	n/a (new in 17→18 line)	session (user)
effective_io_concurrency	16	1	session (user)
maintenance_io_concurrency	16	10	session (user)

io_method accepts sync (pre-18 behavior), worker (dedicated I/O worker processes — the portable default), and io_uring (Linux only, requires a build with --with-liburing; submission happens directly from the backend via kernel ring buffers, eliminating inter-process handoff). In-flight I/O is observable in the new pg_aios view, and pg_stat_io now reports read_bytes / write_bytes / extend_bytes so you can finally measure I/O volume, not just operation counts. Measured on our lab table — 5M rows, cold shared buffers, warm OS cache, max_parallel_workers_per_gather = 0, median of 3 runs of SELECT count(*) FROM orders:

io_method = sync    →  441 ms  (median; runs: 477 / 441 / 348)
io_method = worker  →  297 ms  (median; runs: 309 / 297 / 273)  ~33% faster

A 33% reduction on a page-cache-resident scan is the syscall-batching and read-combining effect alone. On cloud block storage, where per-request latency is measured in hundreds of microseconds and the old prefetch model stalled constantly, community and vendor testing has reported substantially larger gains for cold sequential scans and bitmap heap scans — we deliberately do not quote a universal multiplier, because the win scales with device latency and queue depth. The mechanism, not the number, is the takeaway: backends no longer wait for each block before requesting the next. Two operational cautions before you bank these PostgreSQL 18 performance internals. First, io_method requires a restart, so it belongs in your upgrade change window plan, not a hotfix. Second, with worker mode the I/O worker pool is shared across all backends — on high-concurrency systems monitor pg_aios and consider raising io_workers from its conservative default of 3 (reload-only, fortunately).

B-tree Skip Scan: Multicolumn Indexes Without the Left-Most Rule

If asynchronous I/O is the biggest architectural shift, B-tree skip scan is the piece of PostgreSQL 18 performance internals DBAs will notice first in query plans. Every PostgreSQL DBA has recited the rule: a multicolumn B-tree index on (a, b) is useless for a predicate on b alone. Since PostgreSQL 18 (commit by Peter Geoghegan), that rule has an asterisk. When the leading column has low cardinality, the executor can now perform a skip scan: it iterates the distinct values of the leading column and descends the tree once per value, applying the b predicate inside each group — turning one giant range scan into a handful of targeted descents.

Here is the real plan on 18.4 — predicate on customer_id only, index on (region_id, customer_id):

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*)
FROM orders
WHERE customer_id = 4242;

 Aggregate (actual time=0.520..0.521 rows=1.00 loops=1)
   Buffers: shared hit=21 read=20
   ->  Index Only Scan using idx_orders_region_customer on orders
         (actual time=0.334..0.513 rows=25.00 loops=1)
         Index Cond: (customer_id = 4242)
         Heap Fetches: 0
         Index Searches: 10
         Buffers: shared hit=21 read=20
 Planning Time: 0.401 ms
 Execution Time: 0.582 ms

Read the plan carefully — three details are new or decisive. The Index Cond references only the second column, yet the scan uses the composite index. Index Searches: 10 is a new EXPLAIN counter in 18 exposing exactly how many B-tree descents occurred (our 8 region groups plus boundary probes). And the buffer math is the entire story: 41 buffers touched instead of ~13,700 leaf pages — a sub-millisecond answer from an index that previous versions could not use for this predicate at all. This is the kind of improvement PostgreSQL 18 performance internals deliver: a plan-shape change, not a config change. The PostgreSQL 18 performance internals determine when this fires. Skip scan is not a separate plan node — it is a capability of the standard B-tree scan machinery, driven by the "skip array" support in nbtree. The planner costs it using n_distinct of the leading column(s) from pg_stats: cost scales roughly with distinct-value count × descent cost. With 8 regions it is a spectacular win; with 200,000 distinct leading values it degrades toward a full index scan and the planner will price it accordingly. Check pg_stats.n_distinct on the leading column before you count on it. Non-equality conditions on later columns and missing prefixes of longer composites are also handled — the general rule in 18 is that the index is considered, and cost decides. The schema-design consequence is real: some single-column indexes that exist only to serve "second column" predicates become redundant in 18. On write-heavy tables, each dropped index buys back insert throughput and vacuum time. Audit with pg_stat_user_indexes.idx_scan after the upgrade settles — never drop an index on day one, and test index consolidation in staging first.

Optimizer Advancements: Smarter Plans From the Same SQL

The optimizer side of PostgreSQL 18 performance internals shares one theme: transformations that remove work before path generation begins. None of them require SQL changes, which is exactly what makes them valuable — and occasionally surprising — in production. The three we demonstrate below fired on our lab instance exactly as documented.

Self-join elimination

ORM-generated SQL and view-on-view compositions routinely join a table to itself on its primary key. PostgreSQL 18 (Andrey Lepikhov, Alexander Kuzmenkov, Alexander Korotkov, Alena Rybakina) detects that a self-join on a unique key cannot change the result and deletes it during planning:

EXPLAIN (COSTS OFF)
SELECT o1.order_id, o1.amount
FROM orders o1
JOIN orders o2 ON o1.order_id = o2.order_id
WHERE o2.customer_id = 4242;

-- PostgreSQL 18 (enable_self_join_elimination = on):
 Index Scan using idx_orders_region_customer on orders o2
   Index Cond: (customer_id = 4242)

-- Same query, SET enable_self_join_elimination = off:
 Nested Loop
   ->  Index Scan using idx_orders_region_customer on orders o2
         Index Cond: (customer_id = 4242)
   ->  Index Scan using pk_orders on orders o1
         Index Cond: (order_id = o2.order_id)

The eliminated plan does half the work: one index scan instead of a scan plus 25 primary-key lookups. The precondition is provable uniqueness — the join key must carry a unique constraint or primary key, which is why the transformation silently declines on tables missing one (we verified this: before adding pk_orders, the join survived planning). One more argument for declared constraints on every table, even "internal" ones.

OR-clause and IN (VALUES) normalization

Chained equality ORs on one column now become a single array condition, and IN (VALUES …) lists are converted to = ANY where beneficial — both producing identical, index-friendly plans:

EXPLAIN (COSTS OFF)
SELECT count(*)
FROM orders
WHERE customer_id = 101 OR customer_id = 202 OR customer_id = 303;

 Aggregate
   ->  Index Only Scan using idx_orders_region_customer on orders
         Index Cond: (customer_id = ANY ('{101,202,303}'::integer[]))

Pre-18, this shape typically planned as a BitmapOr of three separate index scans — three descents, three bitmap merges — or worse, a sequential scan once the OR chain grew long. The array form is one scan primitive, gets a single selectivity estimate, and (note the plan above) composes with skip scan: the condition is on the second column of the composite index. Application teams generating long OR chains from filter UIs get this PostgreSQL 18 performance internals win for free.

DISTINCT key reordering

SELECT DISTINCT region_id, order_date with an index on (order_date, region_id) historically required an explicit Sort, because DISTINCT key order was taken literally. 18 reorders the keys to match available input ordering (enable_distinct_reordering, default on):

SET max_parallel_workers_per_gather = 0;  -- isolate the plan shape

EXPLAIN (COSTS OFF)
SELECT DISTINCT region_id, order_date
FROM orders
WHERE order_date < DATE '2025-01-08';

 Unique
   ->  Index Only Scan using idx_orders_date_region on orders
         Index Cond: (order_date < '2025-01-08'::date)

No Sort node, no work_mem consumption, no spill risk — a PostgreSQL 18 performance internals win that costs nothing to adopt: a Unique over presorted index output. Semantics are unchanged (DISTINCT is set-defined); only the physical plan differs. Beyond these three, the 18 planner also gained: reordering-aware GROUP BY (redundant grouping columns functionally dependent on unique indexes are ignored), Right Semi Join support, merge joins over incremental sorts, HAVING-to-WHERE pushdown for GROUPING SETS, materially cheaper planning for queries touching many partitions, and better row estimates for generate_series(). Each is a small planning-time or plan-shape win; together they compound into measurable gains from PostgreSQL 18 performance internals — particularly for partitioned OLTP schemas where planning cost itself had become a bottleneck.

Other Indexing Advancements in PostgreSQL 18

Three further index-side changes round out the PostgreSQL 18 performance internals picture. Parallel GIN builds (Tomas Vondra, Matthias van de Meent): GIN index creation — the pain point of every JSONB- and full-text-heavy schema — now uses parallel workers, governed by max_parallel_maintenance_workers. B-tree and BRIN already had this; GIN was the long-standing gap, and on multi-core boxes it converts hours-long index builds on large JSONB corpora into a fraction of the wall-clock time. Sorted GiST/btree range builds (Bernd Helmle): values are pre-sorted to accelerate range-type index construction. Non-btree unique indexes as partition keys (Mark Dilger): any index AM that supports equality can now back partition keys and materialized-view uniqueness — relevant if you run hash-like or extension AMs. Also worth knowing: amcheck gained gin_index_check() for GIN consistency verification — add it to your corruption-audit runbooks alongside the existing B-tree checks.

Supporting Improvements: Hash Joins, Vacuum, pg_upgrade Statistics

Several changes below the headline features round out PostgreSQL 18 performance internals in production estates:

• Hash join and hash aggregation memory efficiency (David Rowley, Jeff Davis): reduced memory footprint and better performance for hashed GROUP BY, hash set operations (EXCEPT), and subplan hash lookups — fewer work_mem spills at the same setting.
• Faster lock acquisition for many-relation queries (Tomas Vondra): queries touching hundreds of partitions or relations pay less in the lock manager — a direct complement to the cheaper partition planning.
• Eager freezing during normal vacuum (Melanie Plageman, vacuum_max_eager_freeze_failure_rate): normal vacuums opportunistically freeze all-visible pages, flattening the periodic aggressive-vacuum spikes that estates with monotonic insert patterns know too well — and reducing wraparound-driven emergency vacuum risk.
• pg_upgrade retains optimizer statistics: the post-upgrade "run ANALYZE everywhere before opening traffic" scramble is largely gone — planner statistics survive the upgrade (extended statistics do not; plan a targeted ANALYZE for tables relying on them).
• Observability for the I/O era: pg_stat_io byte columns, per-backend I/O via pg_stat_get_backend_io(), WAL I/O timing under track_wal_io_timing, and vacuum/analyze delay accounting under track_cost_delay_timing. Baseline these views before and after your 18 cutover — they are the measurement layer for everything else in this article.

Production Implications and Upgrade Stance

Our stance, dated August 2026: with 18.4 as the current minor, the 18.x line has cleared the early-adopter phase and is our recommended target for general OLTP/HTAP estates on 15 or older — and PostgreSQL 14 users should note community EOL arrives November 2026, making 18 the natural landing zone. For estates already on 16/17, the PostgreSQL 18 performance internals case alone — AIO for I/O-bound scans, skip scan for composite-index coverage, statistics-preserving pg_upgrade shrinking cutover risk — justifies scheduling the upgrade this cycle rather than next. The staged adoption path we use in client engagements:

1. Upgrade with defaults first — io_method = worker is the tested, portable configuration. Baseline pg_stat_io and pg_stat_statements for two weeks.
2. Verify plan changes: diff pg_stat_statements top-N before/after; watch for skip scans appearing (Index Searches in EXPLAIN) and self-join eliminations changing plan shapes. Every new-in-18 transformation has an escape hatch (enable_self_join_elimination, enable_distinct_reordering) if a regression surfaces — session-settable, zero-downtime rollback.
3. Only then evaluate io_uring on Linux fleets where the build supports it, and index consolidation where skip scan makes single-column helpers redundant.

Blast radius note: everything demonstrated here is a planner or executor behavior change — reversible per-session — except io_method, which is restart-scoped. That is an unusually low-risk profile for a release this consequential.

Version Boundaries and What We Did Not Test

Honest edges matter more than headline numbers when you evaluate PostgreSQL 18 performance internals. All behavior above is verified on PostgreSQL 18.4 and applies to the 18.x line onward; none of it back-patches to 17. Skip scan requires B-tree indexes — no GIN/GiST/BRIN equivalent. Our AIO measurement isolates the submission path (warm OS cache); we did not measure io_uring (not compiled in our lab build), direct I/O (debug_io_direct remains developer-only in 18), write-path AIO (18.0 is read-focused), or parallel GIN build scaling curves. Numbers from a containerized single-node lab are directional — your storage, your workload, your mileage: measure with the pg_stat_io baselines described above before drawing fleet-wide conclusions.

References and Further Reading

Primary sources: the official PostgreSQL 18.0 release notes, the PostgreSQL 18 release announcement, and the multicolumn index documentation covering skip scan. On minervadb.com, this PostgreSQL 18 performance internals article builds on our earlier work: index selection and the query planner, troubleshooting long-running queries and wait events, and bind variables and plan caching in PostgreSQL 16. Planning an upgrade, or need a measurement-first PostgreSQL 18 performance internals assessment on your estate? MinervaDB provides vendor-neutral PostgreSQL consulting, 24×7 consultative support, and managed DBA services for 900+ enterprises worldwide — from optimizer-level troubleshooting to full upgrade and HA engineering. Talk to our PostgreSQL team. Standard caveat: test every change in staging before production, and keep a robust DR posture.