Understanding PostgreSQL 19 Query Planner Changes That May Affect Performance

A database query can return exactly the same result after a PostgreSQL upgrade and still take a completely different amount of time to execute.

The reason is that PostgreSQL does not simply execute SQL statements in the order developers write them. Its query planner evaluates available execution strategies and selects a plan based on table statistics, estimated row counts, indexes, configuration settings, and estimated execution costs.

A change in planner behavior can therefore affect application performance even when the SQL code remains unchanged.

PostgreSQL 19 introduces several query planner and execution improvements. These include additional opportunities for anti-join transformations, better semijoin planning, earlier aggregation in some queries, broader incremental-sort planning, and improvements to hash joins.

These changes can make certain workloads faster, but they can also produce different execution plans that developers should understand before upgrading.

This article explains the important PostgreSQL 19 planner changes, how they affect real SQL queries, how to inspect execution plans, and how to investigate performance differences after an upgrade.

How the PostgreSQL Query Planner Works

When an application sends a SQL query to PostgreSQL, the database does not immediately execute the statement.

It first parses and analyzes the SQL, then generates possible execution strategies. The planner estimates the cost of these strategies and chooses a plan it considers efficient.

For example, consider the following query:

SELECT
    customer_id,
    customer_name
FROM customers
WHERE customer_id = 1001;

PostgreSQL may choose between different access methods:

Sequential scan: Read rows from the table.
Index scan: Use an index to locate matching rows.
Index-only scan: Retrieve the required values directly from an index when visibility information permits it.

The planner considers factors such as:

Table size.
Number of matching rows.
Available indexes.
Column statistics.
Estimated disk and memory costs.
Configuration parameters.
Expected number of rows returned.

A sequential scan is not necessarily inefficient. If a query needs a large percentage of a table, reading the table sequentially may be cheaper than repeatedly accessing an index and fetching individual rows.

Similarly, an index scan is not always the best choice simply because an appropriate index exists.

Estimated cost versus actual execution time

PostgreSQL uses cost estimates to compare execution plans.

Consider this simplified output:

Index Scan using idx_customers_id on customers
  (cost=0.29..8.31 rows=1 width=64)
  (actual time=0.025..0.028 rows=1 loops=1)

The important fields include:

Field	Meaning
cost	Estimated startup and total execution cost
rows	Estimated number of output rows
width	Estimated average row width
actual time	Measured execution time
rows in actual output	Number of rows actually produced
loops	Number of times the plan node executed

The estimated cost is not a measurement in milliseconds. It is an internal value used by the planner to compare alternative plans.

Actual execution time depends on the environment, including hardware, caching, concurrent workload, and storage performance.

This distinction becomes especially important when comparing execution plans across PostgreSQL versions.

Important PostgreSQL 19 Query Planner Changes

PostgreSQL 19 introduces several optimizer improvements that can affect how existing queries are executed.

The following changes are particularly relevant when investigating query performance.

Improved NOT IN Optimization Through Anti-Joins

One important planner improvement allows certain NOT IN expressions to be converted into more efficient anti-join operations when NULL values are not present.

To understand why this matters, consider the following query:

SELECT
    c.customer_id,
    c.customer_name
FROM customers c
WHERE c.customer_id NOT IN (
    SELECT o.customer_id
    FROM orders o
);

The application wants customers who do not have any orders.

PostgreSQL may be able to represent this operation using an anti-join strategy.

An anti-join returns rows from one side for which no matching row exists on the other side.

Why NULL values matter

SQL uses three-valued logic: TRUE, FALSE, and UNKNOWN.

This creates an important difference between NOT IN and NOT EXISTS.

Consider the following values:

Customer ID	Order customer ID
101	101
102	102
103	NULL

If the subquery used by NOT IN returns a NULL value, comparisons against the result can produce UNKNOWN rather than TRUE.

Consequently, a query that developers expect to return customers without orders may return no rows.

For example:

SELECT 104
WHERE 104 NOT IN (101, 102, NULL);

The result is not TRUE because the comparison with NULL is UNKNOWN.

This is why PostgreSQL cannot always transform NOT IN into an ordinary anti-join without preserving its NULL semantics.

PostgreSQL 19 allows the transformation when the planner can establish that NULL values are not present.

For applications, this means that queries with suitable non-null constraints or provable non-null expressions may benefit from a more efficient execution strategy.

When to use NOT EXISTS

For queries that express the requirement "return rows for which no matching record exists," NOT EXISTS is often a clear choice.

SELECT
    c.customer_id,
    c.customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

The query expresses the absence of a matching order directly.

PostgreSQL can consider an anti-join strategy for this type of query.

However, NOT EXISTS and NOT IN are not interchangeable in every situation. Before rewriting existing SQL, verify NULL behavior and the expected results.

The important lesson is not to replace every NOT IN expression. It is to understand the data constraints and inspect the plan selected by PostgreSQL.

More LEFT JOIN Queries Can Be Converted Into Anti-Joins

PostgreSQL 19 also allows more LEFT JOIN patterns to be transformed into anti-joins.

Consider the following query:

SELECT
    c.customer_id,
    c.customer_name
FROM customers c
LEFT JOIN orders o
    ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

The query retrieves customers without matching orders.

A LEFT JOIN preserves rows from the left table even when no matching record exists in the right table. The WHERE condition then keeps only rows where the right-side order identifier is NULL.

PostgreSQL can sometimes recognize this pattern as an anti-join.

This is useful because an anti-join directly represents the operation the application needs.

Why the transformation matters

A LEFT JOIN can produce intermediate rows that must be processed before the filtering condition is applied.

An anti-join can avoid some unnecessary work by focusing on whether a matching row exists.

The exact benefit depends on the query, table sizes, indexes, and available statistics.

For example, an index on the orders customer identifier may help PostgreSQL efficiently determine whether a matching order exists.

CREATE INDEX idx_orders_customer_id
ON orders(customer_id);

Do not create an index only because a query contains a LEFT JOIN. First inspect the execution plan and determine whether the index supports the actual workload.

Also, be careful when the WHERE condition checks a nullable column that is not guaranteed to distinguish a missing joined row from a matching row containing NULL. The transformation must preserve the original SQL semantics.

Better Memoize Support for Anti-Joins

PostgreSQL uses the Memoize plan node to cache results from parameterized scans inside certain execution plans.

This can reduce repeated work when the same lookup values appear multiple times.

PostgreSQL 19 expands the use of Memoize for anti-joins with unique inner sides.

Consider a query that checks whether related records exist for multiple rows.

SELECT
    c.customer_id,
    c.customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

Depending on the selected plan and workload, PostgreSQL may benefit from caching repeated lookup results.

The Memoize node can reuse a previously calculated result instead of repeating the same parameterized lookup.

Understanding Memoize in EXPLAIN

An execution plan may contain a section similar to this:

Memoize
  Cache Key: c.customer_id
  Cache Mode: logical
  Hits: 850
  Misses: 150
  Evictions: 0
  Overflows: 0

This is an illustrative example.

The fields help explain cache behavior:

Field	Meaning
Cache Key	Values used to identify cached results
Hits	Number of times a cached result was reused
Misses	Number of times the result had to be calculated
Evictions	Cached entries removed to make room
Overflows	Cases where results could not be cached as expected

PostgreSQL 19 also improves Memoize-related estimates in EXPLAIN output, helping developers understand why the planner selected this strategy.

A high cache-hit count can indicate that Memoize is avoiding repeated work. However, a Memoize node is not automatically beneficial for every query.

The size of cached results, number of distinct parameter values, and memory limits all matter.

Earlier Aggregation Before Joins

Another important PostgreSQL 19 improvement allows some aggregate processing to happen before joins.

This can reduce the number of rows that need to be processed by later execution steps.

Consider an application that calculates the total order value for each customer.

SELECT
    c.customer_id,
    c.customer_name,
    SUM(o.total_amount) AS total_spent
FROM customers c
JOIN orders o
    ON o.customer_id = c.customer_id
GROUP BY
    c.customer_id,
    c.customer_name;

The query joins customer and order records before calculating the final aggregate.

If a customer has thousands of orders, many rows may need to participate in the join and aggregation process.

In some query structures, PostgreSQL can perform partial aggregation earlier and join the reduced result with the customer table.

A conceptual equivalent for this example is:

SELECT
    c.customer_id,
    c.customer_name,
    order_totals.total_spent
FROM customers c
JOIN (
    SELECT
        customer_id,
        SUM(total_amount) AS total_spent
    FROM orders
    GROUP BY customer_id
) order_totals
    ON order_totals.customer_id = c.customer_id;

This rewritten SQL illustrates the idea of reducing order rows before joining. It is not a guarantee that PostgreSQL will produce this exact plan for the original statement.

The optimizer must prove that the transformation preserves the query's semantics.

When earlier aggregation can help

It may be beneficial when:

A large table contains many rows per grouping key.
The aggregation significantly reduces the number of rows.
The reduced result is cheaper to join.
The query's expressions and grouping conditions allow the transformation.

It may offer little benefit when aggregation does not reduce the row count or when the join conditions require processing individual rows.

Do not manually rewrite every aggregation query. Compare the original and rewritten plans before deciding which form is better.

Improved Semijoin Planning

A semijoin returns rows from one relation when at least one matching row exists in another relation.

A common SQL expression that can produce semijoin behavior is EXISTS.

For example:

SELECT
    c.customer_id,
    c.customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

The query returns customers who have at least one order.

It does not need to return every matching order.

This is different from a regular join, which may produce multiple rows for a customer if that customer has multiple orders.

PostgreSQL 19 improves semijoin planning, which can affect how these existence checks are executed.

Possible strategies include:

Nested-loop semijoins.
Hash-based semijoins.
Other planner transformations based on available conditions.

The best strategy depends on the number of customers, number of orders, data distribution, and available indexes.

For example, if the customer table contains a small number of rows and the orders table has an appropriate index, a nested-loop strategy may be efficient.

For larger inputs, a hash-based strategy may be more appropriate.

The improvement is in the planner's ability to evaluate suitable alternatives, not a guarantee that every EXISTS query will become faster.

Broader Incremental Sort Planning

PostgreSQL can use incremental sorting when input rows are already sorted by part of the required ordering.

Instead of sorting the entire result from scratch, the database can sort smaller groups using the existing ordering.

PostgreSQL 19 allows Append and MergeAppend planning to consider explicit incremental-sort paths in additional situations.

Consider a query that retrieves customer orders:

SELECT
    customer_id,
    order_date,
    order_id
FROM orders
ORDER BY
    customer_id,
    order_date DESC;

Suppose the input is already ordered by customer_id.

The database may only need to sort rows within each customer group by order_date.

That can be cheaper than sorting the complete result using both columns.

Understanding incremental sort

An execution plan may contain:

Incremental Sort
  Sort Key: customer_id, order_date DESC
  Presorted Key: customer_id
  -> Index Scan

The presorted key tells you which part of the requested ordering is already available.

Incremental sort is particularly relevant to partitioned tables and execution plans involving Append or MergeAppend.

However, incremental sorting is not always faster.

If there are very large groups or the input is poorly ordered, the additional processing may not provide a benefit.

Compare the number of groups, sort methods, memory consumption, and execution time before changing indexes or SQL statements.

Better Hash Join Handling for NULL Join Keys

Hash joins are commonly used when PostgreSQL joins larger data sets.

The database builds a hash table from one input and uses it to find matching rows from the other input.

For example:

SELECT
    c.customer_id,
    o.order_id
FROM customers c
JOIN orders o
    ON c.customer_id = o.customer_id;

PostgreSQL 19 improves hash join handling of tuples with NULL join keys.

This is an execution improvement rather than a change that makes NULL values match under ordinary equality comparisons.

In SQL, the expression:

NULL = NULL

does not evaluate to TRUE. It evaluates to UNKNOWN.

Therefore, an ordinary equality join does not treat two NULL join keys as matching rows.

PostgreSQL must preserve this behavior while managing NULL values efficiently inside hash-based execution.

For developers, the practical point is that queries involving nullable join keys should be included in regression testing.

Do not change a normal equality join to use IS NOT DISTINCT FROM simply to try to benefit from the new hash join behavior. That changes the comparison semantics by treating two NULL values as equal.

Better Selectivity Estimates for Boolean Functions

The query planner relies on selectivity estimates to predict how many rows will satisfy a condition.

For example:

SELECT *
FROM users
WHERE is_active = TRUE;

If 95 percent of users are active, the planner should estimate that the condition returns approximately 95 percent of the table.

However, conditions involving functions that return Boolean values can be difficult to estimate accurately without appropriate statistics.

PostgreSQL 19 adds proper optimizer statistics for functions returning Boolean values.

This can improve planning decisions for queries that use supported function expressions.

For example:

SELECT *
FROM orders
WHERE is_priority_order(order_id);

The quality of the plan depends on the function's behavior and the statistics available to the optimizer.

A Boolean function should not be assumed to return TRUE for half the rows. Its actual distribution may be very different.

When investigating these queries, compare estimated rows with actual rows and verify whether statistics are available and representative.

Extended Statistics for Virtual Generated Columns

PostgreSQL uses extended statistics to understand relationships between multiple columns.

Without them, the planner may assume that certain column conditions are independent, which can result in inaccurate row estimates.

PostgreSQL 19 allows extended statistics on virtual generated columns.

Consider a table that calculates a value from existing columns:

CREATE TABLE sales (
    sale_id BIGINT PRIMARY KEY,
    quantity INTEGER,
    unit_price NUMERIC(12, 2),
    discount NUMERIC(5, 2),
    total_amount NUMERIC(14, 2)
        GENERATED ALWAYS AS (
            quantity * unit_price * (1 - discount / 100)
        ) VIRTUAL
);

A virtual generated column is computed when it is read rather than stored as a regular table value.

Applications may filter or group records using generated expressions.

PostgreSQL 19 expands the options for collecting extended statistics involving these columns.

For example:

CREATE STATISTICS sales_amount_stats
    (dependencies, ndistinct)
ON quantity, unit_price, discount
FROM sales;

ANALYZE sales;

This example creates statistics on the underlying columns. In PostgreSQL 19, supported extended-statistics definitions can also involve virtual generated columns.

The practical benefit is better information for the planner when column relationships affect query selectivity.

Statistics do not automatically make a query faster. They improve the information available when PostgreSQL chooses an execution plan.

New Planner Advice Extensions

PostgreSQL 19 introduces two extensions that are particularly interesting for query-plan management.

pg_plan_advice

The pg_plan_advice extension provides a mechanism for controlling planner decisions through plan advice.

This is useful when a team needs to investigate or stabilize a query plan rather than repeatedly changing SQL statements or globally modifying planner configuration.

For example, an application may depend on a critical reporting query that produces inconsistent plans under different data distributions.

Plan advice can provide a way to guide planner decisions for supported scenarios.

However, planner advice should not be the first response to a slow query.

Before introducing it, investigate:

Whether table statistics are current.
Whether the query has appropriate indexes.
Whether estimated and actual row counts differ significantly.
Whether the SQL expression can be simplified.
Whether configuration settings are appropriate.

pg_stash_advice

The pg_stash_advice extension provides a way to apply planner advice automatically using query identifiers.

This can be useful when managing recurring query patterns.

Instead of relying entirely on manually identifying a statement each time, advice can be associated with its query identity.

These extensions introduce additional possibilities for database administrators, but they also create operational responsibilities.

Planner advice should be documented, tested, and monitored. A plan that performs well for one data distribution may become inefficient after the underlying workload changes.

How to Identify Planner Regressions After an Upgrade

The most reliable way to identify a query-planning regression is to compare the actual execution plans from both database versions.

Start with the same SQL statement, representative data, and comparable configuration.

Use:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT
    c.customer_id,
    COUNT(o.order_id) AS total_orders
FROM customers c
JOIN orders o
    ON o.customer_id = c.customer_id
WHERE o.order_date >= DATE '2026-01-01'
GROUP BY c.customer_id;

Capture the output on both versions.

Pay attention to the following areas:

Plan information	What to investigate
Scan method	Did an index scan become a sequential scan?
Join strategy	Did the join method change?
Estimated rows	Are planner estimates realistic?
Actual rows	Did the query process more records than expected?
Loops	Are plan nodes executing repeatedly?
Buffers	Did the query read more blocks?
Sort method	Is sorting using more memory or temporary files?
Execution time	Is the query consistently slower?

Find large estimation errors

Consider this illustrative plan fragment:

Hash Join
  (cost=100.00..500.00 rows=100)
  (actual time=5.20..90.00 rows=50000 loops=1)

The planner estimated 100 rows but actually produced 50,000.

That is a substantial estimation difference.

The optimizer may have selected a plan based on assumptions that did not match the actual data.

Investigate the following:

ANALYZE customers;
ANALYZE orders;

Also review whether the columns used in joins and filters have appropriate statistics.

For correlated columns, consider extended statistics where supported.

Do not immediately force another join method. First identify why the estimate was inaccurate.

Monitor Query Performance with pg_stat_statements

Execution plans help investigate individual queries, but production monitoring should also identify statements that consume significant database resources.

The pg_stat_statements extension tracks aggregated execution statistics.

For example:

SELECT
    queryid,
    calls,
    total_exec_time,
    mean_exec_time,
    rows,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

This query helps identify statements with high cumulative execution time.

PostgreSQL 19 also adds generic and custom plan counts to pg_stat_statements.

This is useful for understanding prepared statements that may use different planning strategies.

A generic plan is reused across executions, while a custom plan is generated using the specific parameter values supplied for an execution.

The difference matters when parameter values have very different data distributions.

For example, a query filtering by customer_id may behave differently for a customer with ten orders compared with a customer with several million orders.

PostgreSQL's generic and custom planning decisions can affect performance in these situations.

When investigating a regression, review the plan counts and compare the behavior of representative parameter values.

Why JIT Changes Can Also Affect Query Performance

Not every performance difference after an upgrade comes directly from a planner transformation.

PostgreSQL 19 changes the default setting for just-in-time compilation.

JIT is disabled by default in PostgreSQL 19 because the previous cost-based activation behavior was considered unreliable.

This is relevant to applications that execute large analytical queries.

JIT compilation can reduce execution overhead for some expensive expressions and operations, but compilation itself also introduces overhead.

A query that executes quickly may not benefit from JIT, while a large analytical query may benefit depending on its workload.

Review the current setting:

SHOW jit;

You can also inspect relevant JIT configuration:

SHOW jit_above_cost;
SHOW jit_inline_above_cost;
SHOW jit_optimize_above_cost;

Do not enable JIT globally just because an analytical query becomes slower.

Benchmark the actual workload and compare execution time, planning overhead, and JIT compilation time before changing the setting.

A Practical PostgreSQL 19 Query Planner Testing Strategy

A structured testing process helps separate real planner regressions from differences caused by data, configuration, or environment.

Use the following workflow:

Identify important application queries
              |
              v
Capture baseline execution plans
              |
              v
Restore representative data
              |
              v
Run the same queries on PostgreSQL 19
              |
              v
Compare estimated and actual rows
              |
              v
Review join, sort, and aggregate strategies
              |
              v
Check statistics and configuration
              |
              v
Benchmark repeated executions
              |
              v
Document and validate any required changes

A practical test matrix can help organize the results.

Query category	Important areas to validate
NOT IN and NOT EXISTS	NULL behavior and anti-join plans
LEFT JOIN	Anti-join transformations
EXISTS	Semijoin strategies
Aggregation	Earlier aggregation and row reduction
Ordered queries	Incremental sort and sorting cost
Hash joins	NULL join keys and memory behavior
Boolean functions	Selectivity estimates
Prepared statements	Generic and custom plans
Analytical queries	JIT behavior and execution cost

Run each query with representative parameters and data distributions.

Also compare the database settings, indexes, and statistics. Otherwise, the test may measure environmental differences instead of changes introduced by PostgreSQL 19.

Common Mistakes When Investigating Query Planner Changes

Mistake	Why it causes problems	Better approach
Assuming a new plan is automatically better	A different plan may perform worse for the actual workload	Compare actual execution metrics
Treating estimated cost as milliseconds	Planner cost is not elapsed time	Use EXPLAIN ANALYZE
Ignoring NULL semantics	Query rewrites may change returned results	Test NULL and non-NULL cases
Forcing join methods too early	Can hide inaccurate statistics or poor query design	Investigate row estimates first
Comparing different data sets	Produces misleading performance conclusions	Use equivalent representative data
Ignoring prepared statements	Parameter-sensitive workloads may behave differently	Compare generic and custom plans
Changing multiple settings simultaneously	Makes it difficult to identify the cause	Change one factor at a time
Treating one execution as a benchmark	Cache state and system load can distort results	Repeat tests under controlled conditions

Best Practices for PostgreSQL 19 Query Optimization

Before changing production queries or database settings, follow a disciplined approach:

Capture execution plans for important queries before upgrading.
Use representative production data in a separate test environment.
Compare estimated and actual row counts.
Review join strategies and aggregation placement.
Test NOT IN queries with NULL values.
Validate queries that use EXISTS and LEFT JOIN patterns.
Monitor Memoize behavior where it appears in execution plans.
Check incremental-sort performance for ordered and partitioned queries.
Review statistics for correlated columns and Boolean expressions.
Monitor prepared-statement generic and custom plan behavior.
Evaluate JIT settings for analytical workloads.
Use pg_stat_statements to identify expensive production queries.
Avoid forcing planner behavior without evidence.
Document planner advice and configuration changes.

The most important practice is to optimize based on measured workload behavior rather than assuming that every new optimizer feature should improve every query.

Conclusion

PostgreSQL 19 introduces several query planner improvements that can change how existing application queries are executed. Better anti-join transformations, expanded Memoize support, earlier aggregation, improved semijoin planning, broader incremental-sort opportunities, and better selectivity estimates can help reduce unnecessary database work.

However, a new planner strategy does not automatically guarantee better performance. Actual results depend on data distribution, indexes, statistics, query structure, configuration, and workload characteristics.

Developers and database administrators should compare execution plans before and after upgrading, pay attention to estimated versus actual rows, and validate queries that depend on NULL semantics, prepared statements, and complex joins.

Use EXPLAIN ANALYZE and pg_stat_statements to investigate real workloads, and avoid forcing planner decisions before understanding why the selected plan is inefficient.

The goal is not to make every query use a particular execution strategy. It is to ensure that PostgreSQL chooses an efficient plan for the data and workload the application actually processes.