Request a Demo
Home   >   Blog
  •   PostgreSQL   •   By Pavel Luzanov

PostgreSQL 19: part 5.1 (QPT) or CommitFest 2026-03

PostgreSQL
Source:
habr.com Subscribe to blog
Source:
habr.com Subscribe to blog

We’re wrapping up our series of articles reviewing the changes in PostgreSQL 19. The previous articles covered the first four CommitFests: 2025-07, 2025-09, 2025-11, 2026-01.

Today we begin our review of the March 2026 CommitFest. Why “begin”? As in previous years, the March review is going to be a long one: the final CommitFest of the cycle is where many of the most important changes land. Such a review is hard not only to write but also to read all the way through 🙂. That’s why this time it’s split into four articles.

The first article covers everything related to query optimization, in other words, the topics of our QPT training course. The second will focus on changes more relevant to the DBA1 and DBA2 courses. The third will cover DBA3 and DBS. The fourth will look at new SQL features, as well as changes related to the DEV1 and DEV2 courses.

RevertFest

But first, let’s look at what won’t make it into version 19. An impressive number of features were committed to version 19 before the feature freeze, only to be reverted before the release:

At the time of writing, not all open items have been resolved, and the release is tentatively scheduled for October 29. So the list of new features may shrink further.

CommitFest

With that caveat in mind, let’s move on to the changes related to query optimization. Here’s what’s covered:

Query plan stabilization and plan advice

commit: 5883ff30b02, c10edb102ad

The main idea behind the new pg_plan_advice extension is to make it possible to stabilize query execution plans. If a query performs well, users need a way to lock in that plan so it doesn’t change on later executions. To do that, you first need to see and record the decisions the planner made while building the plan. Then you need a way to tell the planner how to build the plan for this query.

pg_plan_advice uses a dedicated mini-language to describe the key decisions made while building the plan.

The extension doesn’t create any database objects, so to use it, you only need to load the module of the same name. After that, you can add the plan_advice option to EXPLAIN, and it will show the planner’s decisions (Generated Plan Advice):

LOAD 'pg_plan_advice';

EXPLAIN (costs off, plan_advice)
SELECT * FROM bookings;
       QUERY PLAN
------------------------
 Seq Scan on bookings
 Generated Plan Advice:
   SEQ_SCAN(bookings)
   NO_GATHER(bookings)
(4 rows)

For this simple query, the planner made two decisions: to scan the bookings table sequentially and not to use a parallel plan.

Once you have the planner’s key decisions, you can feed them back to the planner as advice to keep the plan stable. Let’s set the extension’s parameter to this advice:

SET pg_plan_advice.advice = 'SEQ_SCAN(bookings) NO_GATHER(bookings)';

Now the query plan shows whether the planner followed the advice (Supplied Plan Advice):

EXPLAIN (costs off, plan_advice)
SELECT * FROM bookings;
             QUERY PLAN
-------------------------------------
 Seq Scan on bookings
 Supplied Plan Advice:
   SEQ_SCAN(bookings) /* matched */
   NO_GATHER(bookings) /* matched */
 Generated Plan Advice:
   SEQ_SCAN(bookings)
   NO_GATHER(bookings)
(7 rows)

In our case, both pieces of advice were followed (/* matched */).

But nothing prevents us from experimenting with the plan. If we think that the query plan is suboptimal, we can advise the planner to execute the query differently. For example, to use a parallel table scan:

SET pg_plan_advice.advice = 'SEQ_SCAN(bookings) GATHER(bookings)';

And the planner obeys:

EXPLAIN (costs off, plan_advice) SELECT * FROM bookings;
             QUERY PLAN
-------------------------------------
 Gather
   Workers Planned: 2
   ->  Parallel Seq Scan on bookings
 Supplied Plan Advice:
   SEQ_SCAN(bookings) /* matched */
   GATHER(bookings) /* matched */
 Generated Plan Advice:
   SEQ_SCAN(bookings)
   GATHER(bookings)
(9 rows)

This is a very simple example of plan advice. The mini-language can do much more: control join methods and join order, table access methods, and so on. The extension’s documentation covers it in detail. There are also limitations, some of which may be lifted in future versions.

While pg_plan_advice.advice is quite convenient for testing different query plans, using it to stabilize plans is very awkward: you have to set the parameter to the appropriate value before each query. What we’d really like is a tool that automatically applies different advice to different queries. PostgreSQL 19 ships such a tool too, as a separate extension: pg_stash_advice.

CREATE EXTENSION pg_stash_advice;
LOAD 'pg_stash_advice';

Using the extension’s functions, we create an in-memory advice stash that maps query identifiers to advice strings.

First, let’s find the query identifier, which EXPLAIN shows with the verbose option:

RESET pg_plan_advice.advice;
EXPLAIN (verbose, costs off)
SELECT * FROM bookings;
                 QUERY PLAN
---------------------------------------------
 Seq Scan on bookings.bookings
   Output: book_ref, book_date, total_amount
 Query Identifier: -2160201451481084067
(3 rows)

Everything is ready to create the stash:

SELECT pg_create_advice_stash('demo');
SELECT pg_set_stashed_advice(
  'demo',
   '-2160201451481084067',
   'SEQ_SCAN(bookings) GATHER(bookings)'
);

We’ve stored the advice for one query in the stash. Other functions of the extension let you view the stash contents, as well as remove or add advice for other queries. You can also create separate stashes for different groups of queries. For example, it may be useful to have separate stashes for different databases in the cluster.

To apply the stashed advice automatically, you need to set the stash name for the sessions in which the queries run. This is done with the pg_stash_advice.stash_name parameter. It can be set in postgresql.conf or at a more granular level: for a database (ALTER DATABASE SET), a role, or an individual session.

SET pg_stash_advice.stash_name = 'demo';

Now, in the current session, the planner will look up advice for this query in the demo stash:

EXPLAIN (costs off) SELECT * FROM bookings;
             QUERY PLAN
-------------------------------------
 Gather
   Workers Planned: 2
   ->  Parallel Seq Scan on bookings
 Supplied Plan Advice:
   SEQ_SCAN(bookings) /* matched */
   GATHER(bookings) /* matched */
(6 rows)

Of course, looking up the stash before every query adds overhead. However, this approach has a significant advantage over comment-based hints embedded in the query text: you can influence the plan without touching the application code, which you may not even have access to.

An important question for production use is what happens to the in-memory stash after a server restart.

For that, both modules must be loaded via shared_preload_libraries. You also need to choose how to set the stash name, for example, at the database level:

ALTER SYSTEM SET shared_preload_libraries = 'pg_plan_advice, pg_stash_advice';
ALTER DATABASE demo SET pg_stash_advice.stash_name = 'demo';

Let’s restart the server to apply the changes and recreate the stash:

$ pg_ctl restart -l logfile
demo=# SELECT pg_create_advice_stash('demo');
demo=# SELECT pg_set_stashed_advice(
  'demo',
   '-2160201451481084067',
   'SEQ_SCAN(bookings) GATHER(bookings)'
);

Since the pg_stash_advice.persist parameter is enabled by default, the extension’s background worker saves the stashed advice to a file every 30 seconds (pg_stash_advice.persist_interval):

\! cat $PGDATA/pg_stash_advice.tsv
stash    demo
entry    demo    -2160201451481084067    SEQ_SCAN(bookings) GATHER(bookings)

The background worker was started automatically when the module was loaded at server startup, although it can also be started manually with the pg_start_stash_advice_worker function. On startup, the worker reads the current contents of the file and loads them into memory.

Now we can restart the server without losing anything:

$ pg_ctl restart -l logfile
$ psql -c 'EXPLAIN (costs off) SELECT * FROM bookings'
             QUERY PLAN
-------------------------------------
 Gather
   Workers Planned: 2
   ->  Parallel Seq Scan on bookings
 Supplied Plan Advice:
   SEQ_SCAN(bookings) /* matched */
   GATHER(bookings) /* matched */
(6 rows)

auto_explain: logging EXPLAIN options added by extensions

commit: 0442f1c9eff, e972dff6c30

Version 18 introduced an interface that allows extensions to add new options to the EXPLAIN command. However, auto_explain had no way to use such options when logging plans.

The new auto_explain.log_extension_options parameter lets you include EXPLAIN options added by extensions in the plans that auto_explain logs.

For this demo, let’s configure the psql session to display server log messages. We also load the pg_plan_advice module into the session, since we’re going to log the planner’s decisions.

SET client_min_messages = 'LOG';
LOAD 'pg_plan_advice';
SET pg_plan_advice.always_store_advice_details = on;

Now we load the auto_explain module and set the auto_explain.log_extension_options parameter, whose value will be automatically added to EXPLAIN:

LOAD 'auto_explain';
SET auto_explain.log_min_duration = 0;
SET auto_explain.log_extension_options = 'plan_advice';

Now, for every query, auto_explain logs both the plan and the planner’s decisions (Generated Plan Advice):

SELECT count(*) FROM bookings;
LOG:  duration: 194.624 ms  plan:
Query Text: SELECT count(*) FROM bookings;
Finalize Aggregate  (cost=57792.69..57792.70 rows=1 width=8)
  ->  Gather  (cost=57792.48..57792.69 rows=2 width=8)
        Workers Planned: 2
        ->  Partial Aggregate  (cost=56792.48..56792.49 rows=1 width=8)
              ->  Parallel Seq Scan on bookings  (cost=0.00..51682.78 rows=2043878 width=0)
Generated Plan Advice:
  SEQ_SCAN(bookings)
  GATHER(bookings)
  count
---------
 4905238
(1 row)

EXPLAIN (analyze, timing): reducing timing overhead

commit: 7d9b74df53e, 5a79e78501f, 0022622c93d, bcb2cf41f96, 294520c4448, 16fca482548, 544000288ec, 2c16deee2f7, 7fc36c5db55

Previously, EXPLAIN with the analyze and timing options measured plan node execution time using the clock_gettime system call, whose overhead could skew the results.

In version 19, time can be measured by reading the CPU’s TSC register directly (rdtsc), with the TSC frequency obtained via CPUID. This reduces profiling overhead. The catch is that on virtual machines and hypervisors the TSC frequency isn’t always reliable, so the clock source can also be selected manually.

You can check which clock source is recommended with the pg_test_timing utility:

$ pg_test_timing
…
… part of the output omitted
…
TSC clock source will be used by default, unless timing_clock_source is set to 'system'.

As the message suggests, the clock source can be overridden with the new timing_clock_source parameter:

\dconfig+ timing_clock_source
                    List of configuration parameters
      Parameter      |   Value    | Type |  Context  | Access privileges
---------------------+------------+------+-----------+-------------------
 timing_clock_source | auto (tsc) | enum | superuser |
(1 row)

Planner: IS DISTINCT FROM with non-nullable inputs

commit: 0a379612540, 0aaf0de7fed, f41ab51573a

The planner can transform IS DISTINCT FROM into the inequality <>, which is simpler to evaluate, when both sides of the expression are non-null constants. But what if table columns are used in the expression instead of constants? Take this query, contrived specifically to illustrate the point:

18=# EXPLAIN (costs off)
SELECT t.*
FROM tickets t, segments s
WHERE t.ticket_no IS NOT DISTINCT FROM s.ticket_no;
                              QUERY PLAN
-----------------------------------------------------------------------
 Gather
   Workers Planned: 2
   ->  Nested Loop
         Join Filter: (NOT (t.ticket_no IS DISTINCT FROM s.ticket_no))
         ->  Parallel Seq Scan on tickets t
         ->  Materialize
               ->  Seq Scan on segments s
(7 rows)

In version 18, the planner doesn’t know whether the ticket_no column in either of the joined tables can contain NULL. Therefore, it performs the full, more expensive NOT … IS DISTINCT FROM check (see Join Filter). This also limits the planner’s choice of join methods.

In version 19, the IS NOT DISTINCT FROM condition on two NOT NULL columns is replaced with the equivalent =, which not only simplifies the check, but also allows the planner to choose a better plan:

19=# EXPLAIN (costs off)
SELECT t.*
FROM tickets t, segments s
WHERE t.ticket_no IS NOT DISTINCT FROM s.ticket_no;
                QUERY PLAN
------------------------------------------
 Hash Join
   Hash Cond: (s.ticket_no = t.ticket_no)
   ->  Seq Scan on segments s
   ->  Hash
         ->  Seq Scan on tickets t
(5 rows)

Planner: cardinality estimation for NOT IN (NULL, …) expressions

commit: c95cd2991f1

As you know, if the list of constants in a NOT IN (list) expression contains at least one NULL value, the result of the expression is always NULL. This means that there’s no need to iterate over all the constants to estimate the cardinality of the expression. The cardinality can safely be assumed to be 0.

This is exactly what the planner does now, which reduces planning time.

Planner: anti-join for NOT IN with a subquery

commit: 383eb21ebff

Previously, the planner couldn’t use an anti-join for NOT IN with a subquery:

18=# EXPLAIN (costs off) SELECT *
FROM tickets t
WHERE t.ticket_no NOT IN (
  SELECT s.ticket_no FROM segments s WHERE s.fare_conditions = 'Economy'
);
                            QUERY PLAN
-------------------------------------------------------------------
 Gather
   Workers Planned: 2
   ->  Parallel Seq Scan on tickets t
         Filter: (NOT (ANY (ticket_no = (SubPlan 1).col1)))
         SubPlan 1
           ->  Materialize
                 ->  Seq Scan on segments s
                       Filter: (fare_conditions = 'Economy'::text)
(8 rows)

This often led to a suboptimal plan. On my home computer, this query against the demo database with one year of flights had to be cancelled after about 40 minutes. It’s all because of possible NULLs: if the subquery returns even one NULL, the whole expression must return NULL, and an anti-join can’t produce that.

But what if the columns on both sides of the expression are guaranteed not to contain NULL? There’s plenty of advice online to rewrite NOT IN with a subquery as NOT EXISTS with a correlated subquery.

In version 19, the planner will consider anti-join plans for NOT IN queries with a subquery if the absence of NULL values is guaranteed. In particular, for this query, where both columns are declared NOT NULL, Hash Right Anti Join is used, and the execution takes about 8 seconds:

19=# EXPLAIN (costs off) SELECT *
FROM tickets t
WHERE t.ticket_no NOT IN (
  SELECT s.ticket_no FROM segments s WHERE s.fare_conditions = 'Economy'
);
                    QUERY PLAN
-----------------------------------------------------
 Hash Right Anti Join
   Hash Cond: (s.ticket_no = t.ticket_no)
   ->  Seq Scan on segments s
         Filter: (fare_conditions = 'Economy'::text)
   ->  Hash
         ->  Seq Scan on tickets t
(6 rows)

Planner: anti-join in queries with a left outer join

commit: cf74558feb8

The planner could already execute queries of the form “LEFT JOIN + IS NULL check on the right side” as an anti-join, but only if the check was performed on a non-nullable column that participates in the join. For example, suppose we want to find all flight segments (the segments table) for which boarding passes haven’t been issued yet:

18=# EXPLAIN (costs off)
SELECT *
FROM segments s
     LEFT JOIN boarding_passes bp USING (ticket_no, flight_id)
WHERE bp.ticket_no IS NULL;
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Gather
   Workers Planned: 2
   ->  Parallel Hash Anti Join
         Hash Cond: ((s.ticket_no = bp.ticket_no) AND (s.flight_id = bp.flight_id))
         ->  Parallel Seq Scan on segments s
         ->  Parallel Hash
               ->  Parallel Seq Scan on boarding_passes bp
(7 rows)

However, if the IS NULL check is on another NOT NULL column (seat_no) rather than on ticket_no or flight_id, the planner falls back to a more expensive left outer join:

18=# EXPLAIN (costs off)
SELECT *
FROM segments s
     LEFT JOIN boarding_passes bp USING (ticket_no, flight_id)
WHERE bp.seat_no IS NULL;
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Gather
   Workers Planned: 2
   ->  Parallel Hash Left Join
         Hash Cond: ((s.ticket_no = bp.ticket_no) AND (s.flight_id = bp.flight_id))
         Filter: (bp.seat_no IS NULL)
         ->  Parallel Seq Scan on segments s
         ->  Parallel Hash
               ->  Parallel Seq Scan on boarding_passes bp
(8 rows)

In version 19, the planner additionally checks that the seat_no column can’t be NULL, which means that an anti-join can be used:

19=# EXPLAIN (costs off)
SELECT *
FROM segments s
     LEFT JOIN boarding_passes bp USING (ticket_no, flight_id)
WHERE bp.seat_no IS NULL;
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Gather
   Workers Planned: 2
   ->  Parallel Hash Anti Join
         Hash Cond: ((s.ticket_no = bp.ticket_no) AND (s.flight_id = bp.flight_id))
         ->  Parallel Seq Scan on segments s
         ->  Parallel Hash
               ->  Parallel Seq Scan on boarding_passes bp
(7 rows)

Extended statistics on virtual generated columns

commit: f7f4052a4e9

Statistics aren’t collected for virtual generated columns, which can lead to large cardinality estimation errors.

CREATE TABLE test (
  id int,
  amount numeric,
  tax numeric GENERATED ALWAYS AS (amount*0.22) VIRTUAL,
  CONSTRAINT check_tax CHECK (tax > 0)
);
INSERT INTO test
SELECT g.x, random(1,1000_000.00)
FROM generate_series(1,1000_000) AS g(x);
ANALYZE test;

If a query has a condition on the tax column, the planner can’t correctly estimate the number of rows returned:

EXPLAIN(analyze, buffers off, timing off, summary off)
SELECT * FROM test WHERE tax < 10;
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..21239.33 rows=333333 width=44) (actual rows=49.00 loops=1)
   Filter: ((amount * 0.22) < '10'::numeric)
   Rows Removed by Filter: 999951
(3 rows)

In principle, you can create extended statistics on the column’s generation expression. But this isn’t very convenient, especially since the expression can change, and then you must remember to recreate the extended statistics.

In version 19, you can create extended statistics simply by specifying the virtual column’s name:

CREATE STATISTICS s ON tax FROM test;
ANALYZE test;

Now the cardinality estimate is much more accurate:

EXPLAIN(analyze, buffers off, timing off, summary off)
SELECT * FROM test WHERE tax < 10;
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..12666.10 rows=100 width=44) (actual rows=49.00 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on test  (cost=0.00..11656.10 rows=42 width=44) (actual rows=16.33 loops=3)
         Filter: ((amount * 0.22) < '10'::numeric)
         Rows Removed by Filter: 333317
(6 rows)

postgres_fdw: importing statistics for foreign tables

commit: 28972b6fc3d

When ANALYZE runs on a foreign table, data can be sampled either on the remote side or locally. This is determined by the analyze_sampling option at the foreign server or foreign table level.

postgres_fdw now has a new import_stats option. When it’s enabled, ANALYZE copies the existing statistics (pg_stats) of the remote table. It’s the user’s responsibility to keep the statistics on the remote server up to date, so the option is disabled by default.

We have a foreign table remote_tickets:

\det
         List of foreign tables
 Schema |     Table      |    Server
--------+----------------+---------------
 public | remote_tickets | remote_server
(1 row)

To copy statistics from the remote server, let’s enable the import_stats option at the table level:

ALTER FOREIGN TABLE remote_tickets OPTIONS (import_stats 'on');

\det+x
List of foreign tables
-[ RECORD 1 ]------------------------------------------------------------------
Schema      | public
Table       | remote_tickets
Server      | remote_server
FDW options | (schema_name 'bookings', table_name 'tickets', import_stats 'on')
Description |

After ANALYZE, the statistics of the remote table are copied to the local server:

ANALYZE remote_tickets;

SELECT attname, null_frac, avg_width, n_distinct
FROM pg_stats
WHERE tablename = 'remote_tickets';
    attname     | null_frac | avg_width | n_distinct
----------------+-----------+-----------+-------------
 book_ref       |         0 |         7 | -0.12510937
 outbound       |         0 |         1 |           2
 passenger_id   |         0 |        17 | -0.24015339
 passenger_name |         0 |        13 |      151215
 ticket_no      |         0 |        14 |          -1
(5 rows)

JIT compilation is disabled by default

commit: 7f8c88c2b87

JIT compilation first appeared in version 11, disabled by default, and was enabled by default in version 12. Admittedly, though, for the short, fast queries typical of OLTP workloads, JIT rarely pays off, so it’s often turned off. For example, in our QPT query optimization course, we disable jit so that JIT compilation messages don’t clutter query plans.

In version 19, jit is disabled by default:

\dconfig jit
List of configuration parameters
 Parameter | Value
-----------+-------
 jit       | off
(1 row)

 

That’s all for now. Next time, we’ll look at the changes related to the DBA1 and DBA2 courses.

← Back to all articles

Pavel Luzanov