PG19 Hacktober: 31 Days of New Features: pg_stash_advice: Applying Plan Advice Automatically per Query ID

By /

PostgreSQL 19’s new extension pg_stash_advice introduced by Robert Haas (Major Developer and Committer to the PostgreSQL Global Development Group) allows DBAs to store plan advice against specific query identifiers and automatically apply that advice whenever those queries are planned.’ This extension also helps DBAs save and reuse query execution advice to simplifying PostgreSQL query plan management and improving query performance. Sometimes, a query that runs quickly can become slow because PostgreSQL chooses a different execution plan. With pg_stash_advice, DBAs can save advice for a query and automatically apply it later using the query’s unique ID, called query_id.

This means developers may not need to change the application code or rewrite SQL queries to address certain query plan issues.

Key features:

  • Shared memory: Stores query advice in memory for quick access.
  • Persistence: Can save advice to a file so it can be restored after a PostgreSQL restart.
  • Flexible control: Supports applying advice globally, per database, or per application role.
  • Production support: Helps DBAs address query performance regressions without requiring application code changes or redeployment.

Overall, pg_stash_advice provides DBAs with another way to manage query execution plans and respond to performance issues more easily. In this blog, we will understand what pg_stash_advice adds in PostgreSQL 19, how to configure it, and how to use it with a practical example.

Background / Context

PostgreSQL’s query planner normally chooses an execution plan dynamically based on factors such as statistics, data distribution, available indexes, and estimated costs.

For example, the planner may choose an index scan when only a small percentage of rows match a condition:

Small number of matching rows
          ↓
      Index Scan

As the data distribution changes, the planner may choose a different plan:

Large number of matching rows
          ↓
      Sequential Scan

This ability to change plans is normally a benefit.

However, there can be situations where a DBA has identified a query for which the planner needs specific guidance. PostgreSQL 19 introduces pg_plan_advice to describe such guidance and pg_stash_advice to store that advice against query identifiers.

What was available before PostgreSQL 19?

PostgreSQL 18 did not have pg_stash_advice or pg_plan_advice.

Therefore, PostgreSQL 18 did not provide this built-in mechanism to maintain a mapping such as:

Query ID → Plan Advice

PostgreSQL 19 introduces this capability.

What’s New in PostgreSQL 19?

What is pg_stash_advice?

pg_stash_advice is a PostgreSQL extension that stores mappings between query identifiers and plan advice strings in dynamically allocated shared memory.

When PostgreSQL plans a query, the extension checks whether the query’s identifier exists in the configured advice stash. If a matching entry is found, the associated plan advice is automatically applied to guide the planner.

An advice stash can be understood as a collection of saved instructions for specific queries

Query Identifier → Plan Advice String

For example:

Query ID: 123456789
        ↓
Plan Advice
        ↓
Guide the planner for this query

When PostgreSQL needs to plan a query, it obtains the query identifier. If that query ID exists in the configured advice stash, PostgreSQL automatically applies the associated plan advice.

How the process works

              SQL Query
                  |
                  v
             Query ID
                  |
                  v
          Advice Stash
                  |
          Query ID found?
             /       \
           No         Yes
           |           |
           v           v
     Normal Plan   Apply Advice
                       |
                       v
                  Query Plan

Creating an advice stash

CREATE EXTENSION pg_stash_advice;
SELECT pg_create_advice_stash('production_advice');

The first command makes the SQL functions available in the database. The second creates an empty advice stash.

Adding advice

The pg_set_stashed_advice() function associates a plan-advice string with a query ID:

SELECT pg_set_stashed_advice(
    'production_advice',
    123456789,
    '<plan advice>'
);

If the query ID already exists in the stash, its advice is replaced. If the advice string is NULL, the existing entry is removed.

Selecting the stash

The stash used during query planning is configured using:

SET pg_stash_advice.stash_name = 'production_advice';

It can also be configured using ALTER DATABASE or ALTER ROLE, depending on the required scope.

Query IDs

Because pg_stash_advice works using query identifiers, the query ID must be determined first.

One way to obtain it is:

EXPLAIN (VERBOSE, PLAN_ADVICE)
SELECT *
FROM employees
WHERE employee_id = 100;

The VERBOSE option displays the query ID, while PLAN_ADVICE displays plan advice. Query identifiers can also be obtained from pg_stat_statements or pg_stat_activity. The compute_query_id setting must be enabled for query IDs to be calculated.

Hands-On Example

Consider a complex analytics query that joins the orders, order_items, and customers tables to calculate total orders and revenue by customer region. Changes in data distribution and stale statistics can cause PostgreSQL to select a suboptimal execution plan, potentially increasing query execution time.

The objective is to use pg_plan_advice and pg_stash_advice to capture a known-good execution plan, store its plan advice in the nightly_analytics stash, and configure the PostgreSQL session to reuse that advice for matching queries.

The solution aims to maintain consistent query execution plans when planner estimates change, verify that stored advice is matched, and compare execution time and resource usage before and after applying the advice.

Expected outcome: PostgreSQL successfully retrieves and applies the stored plan advice to the matching analytics query, with performance improvement evaluated using EXPLAIN (ANALYZE, BUFFERS).

Step 1: Check the statistics and run the check counts

Check the rows:

pg_stash_advice=# SELECT 'customers' AS table_name, COUNT(*) AS row_count
FROM customers
UNION ALL
SELECT 'orders', COUNT(*) FROM orders
UNION ALL
SELECT 'order_items', COUNT(*) FROM order_items;
 table_name  | row_count
-------------+-----------
 customers   |   1000000
 orders      |  40000000
 order_items |  40000000
(3 rows)

Step 2: Run the nightly analytics query

This query calculates completed order revenue by customer region for the last 30 days in the generated dataset.

pg_stash_advice=# EXPLAIN (COSTS OFF)
SELECT
    c.region,
    COUNT(DISTINCT o.order_id) AS total_orders,
    SUM(i.quantity * i.unit_price) AS total_revenue
FROM orders o
JOIN customers c
    ON c.customer_id = o.customer_id
JOIN order_items i
    ON i.order_id = o.order_id
WHERE o.order_date >= DATE '2026-12-01'
  AND o.order_date <  DATE '2026-12-31'
  AND o.status = 'COMPLETED'
GROUP BY c.region
ORDER BY total_revenue DESC;                                                             QUERY PLAN                                                                       
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort
   Sort Key: (sum(((i.quantity)::numeric * i.unit_price))) DESC
   ->  GroupAggregate
         Group Key: c.region
         ->  Gather Merge
               Workers Planned: 2
               ->  Sort
                     Sort Key: c.region, o.order_id
                     ->  Parallel Hash Join
                           Hash Cond: (o.customer_id = c.customer_id)
                           ->  Parallel Hash Join
                                 Hash Cond: (i.order_id = o.order_id)
                                 ->  Parallel Seq Scan on order_items i
                                 ->  Parallel Hash
                                       ->  Parallel Seq Scan on orders o
                                             Filter: ((order_date >= '2026-12-01'::date) AND (order_date < '2026-12-31'::date) AND (status = 'COMPLETED'::text))
                           ->  Parallel Hash
                                 ->  Parallel Seq Scan on customers c
(18 rows)

(END)
  • Execution Plan: PostgreSQL uses Parallel Hash Join to join the three tables (orders, customers, and order_items).
  • Data Processing: It uses parallel sequential scans to read data, followed by sorting and aggregation to calculate total orders and revenue by region.
  • Purpose: Shows the planned execution steps, but not actual execution time or runtime performance.

Step 3: Run pg_plan_advice

pg_stash_advice=# EXPLAIN (VERBOSE, PLAN_ADVICE)
SELECT
    c.region,
    COUNT(DISTINCT o.order_id) AS total_orders,
    SUM(i.quantity * i.unit_price) AS total_revenue
FROM orders o
JOIN customers c
    ON c.customer_id = o.customer_id
JOIN order_items i
    ON i.order_id = o.order_id
WHERE o.order_date >= DATE '2026-12-01'
  AND o.order_date <  DATE '2026-12-31'
  AND o.status = 'COMPLETED'
GROUP BY c.region
ORDER BY total_revenue DESC;
                                                                             QUERY PLAN                                                                    
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=1598525.96..1598525.97 rows=4 width=45)
   Output: c.region, (count(DISTINCT o.order_id)), (sum(((i.quantity)::numeric * i.unit_price)))
   Sort Key: (sum(((i.quantity)::numeric * i.unit_price))) DESC
   ->  GroupAggregate  (cost=1430451.98..1598525.92 rows=4 width=45)
         Output: c.region, count(DISTINCT o.order_id), sum(((i.quantity)::numeric * i.unit_price))
         Group Key: c.region
         ->  Gather Merge  (cost=1430451.98..1582235.40 rows=1303237 width=23)
               Output: c.region, o.order_id, i.quantity, i.unit_price
               Workers Planned: 2
               ->  Sort  (cost=1429451.96..1430809.49 rows=543015 width=23)
                     Output: c.region, o.order_id, i.quantity, i.unit_price
                     Sort Key: c.region, o.order_id
                     ->  Parallel Hash Join  (cost=653203.97..1366591.06 rows=543015 width=23)
                           Output: c.region, o.order_id, i.quantity, i.unit_price
                           Inner Unique: true
                           Hash Cond: (o.customer_id = c.customer_id)
                           ->  Parallel Hash Join  (cost=634440.96..1336941.63 rows=543015 width=26)
                                 Output: o.order_id, o.customer_id, i.quantity, i.unit_price
                                 Inner Unique: true
                                 Hash Cond: (i.order_id = o.order_id)
                                 ->  Parallel Seq Scan on public.order_items i  (cost=0.00..460784.67 rows=16666667 width=18)
                                       Output: i.quantity, i.unit_price, i.order_id
                                 ->  Parallel Hash  (cost=625001.25..625001.25 rows=543017 width=16)
                                       Output: o.order_id, o.customer_id
                                       ->  Parallel Seq Scan on public.orders o  (cost=0.00..625001.25 rows=543017 width=16)
                                             Output: o.order_id, o.customer_id
                                             Filter: ((o.order_date >= '2026-12-01'::date) AND (o.order_date < '2026-12-31'::date) AND (o.status = 'COMPLETED'::text))
                           ->  Parallel Hash  (cost=11519.67..11519.67 rows=416667 width=13)
                                 Output: c.region, c.customer_id
                                 ->  Parallel Seq Scan on public.customers c  (cost=0.00..11519.67 rows=416667 width=13)
                                       Output: c.region, c.customer_id
 Generated Plan Advice:
   JOIN_ORDER(i o c)
   HASH_JOIN(o c)
   SEQ_SCAN(i o c)
:
  • Execution Plan: PostgreSQL uses two Parallel Hash Join operations to join order_items, orders, and customers.
  • Generated Plan Advice: JOIN_ORDER(i o c), HASH_JOIN(o c), SEQ_SCAN(i o c) — this describes the selected join order, hash join, and sequential scan strategy.
  • Purpose: PLAN_ADVICE displays the plan advice generated by PostgreSQL, which can be stored in pg_stash_advice and reused for matching queries.

Step 4: Store plan advice by using pg_stash_advice

pg_stash_advice=# CREATE EXTENSION IF NOT EXISTS pg_stash_advice;
CREATE EXTENSION
pg_stash_advice=# SELECT pg_create_advice_stash('nightly_analytics');
 pg_create_advice_stash
------------------------

(1 row)

Next, obtain the query ID for the exact analytics statement using the query ID exposed by your plan-advice tooling or query statistics. Then save the generated advice:

pg_stash_advice=# SELECT pg_set_stashed_advice(
    'nightly_analytics',
    -3826100787633862198,
    'JOIN_ORDER(i o c) HASH_JOIN(o c) SEQ_SCAN(i o c)'
);
 pg_set_stashed_advice
-----------------------

(1 row)

Verify the stored advice.

pg_stash_advice=# SET pg_stash_advice.stash_name = 'nightly_analytics';
SHOW pg_stash_advice.stash_name;
SET
 pg_stash_advice.stash_name
----------------------------
 nightly_analytics
(1 row)
pg_stash_advice=# EXPLAIN (VERBOSE, PLAN_ADVICE)
SELECT
    c.region,
    COUNT(DISTINCT o.order_id) AS total_orders,
    SUM(i.quantity * i.unit_price) AS total_revenue
FROM orders o
JOIN customers c
    ON c.customer_id = o.customer_id
JOIN order_items i
    ON i.order_id = o.order_id
WHERE o.order_date >= DATE '2026-12-01'
  AND o.order_date < DATE '2026-12-31'
  AND o.status = 'COMPLETED'
GROUP BY c.region
ORDER BY total_revenue DESC;
 QUERY PLAN                                                                    
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=1598525.96..1598525.97 rows=4 width=45)
   Output: c.region, (count(DISTINCT o.order_id)), (sum(((i.quantity)::numeric * i.unit_price)))
   Sort Key: (sum(((i.quantity)::numeric * i.unit_price))) DESC
   ->  GroupAggregate  (cost=1430451.98..1598525.92 rows=4 width=45)
         Output: c.region, count(DISTINCT o.order_id), sum(((i.quantity)::numeric * i.unit_price))
         Group Key: c.region
         ->  Gather Merge  (cost=1430451.98..1582235.40 rows=1303237 width=23)
               Output: c.region, o.order_id, i.quantity, i.unit_price
               Workers Planned: 2
               ->  Sort  (cost=1429451.96..1430809.49 rows=543015 width=23)
                     Output: c.region, o.order_id, i.quantity, i.unit_price
                     Sort Key: c.region, o.order_id
                     ->  Parallel Hash Join  (cost=653203.97..1366591.06 rows=543015 width=23)
                           Output: c.region, o.order_id, i.quantity, i.unit_price
                           Inner Unique: true
                           Hash Cond: (o.customer_id = c.customer_id)
                           ->  Parallel Hash Join  (cost=634440.96..1336941.63 rows=543015 width=26)
                                 Output: o.order_id, o.customer_id, i.quantity, i.unit_price
                                 Inner Unique: true
                                 Hash Cond: (i.order_id = o.order_id)
                                 ->  Parallel Seq Scan on public.order_items i  (cost=0.00..460784.67 rows=16666667 width=18)
                                       Output: i.quantity, i.unit_price, i.order_id
                                 ->  Parallel Hash  (cost=625001.25..625001.25 rows=543017 width=16)
                                       Output: o.order_id, o.customer_id
                                       ->  Parallel Seq Scan on public.orders o  (cost=0.00..625001.25 rows=543017 width=16)
                                             Output: o.order_id, o.customer_id
                                             Filter: ((o.order_date >= '2026-12-01'::date) AND (o.order_date < '2026-12-31'::date) AND (o.status = 'COMPLETED'::text))
                           ->  Parallel Hash  (cost=11519.67..11519.67 rows=416667 width=13)
                                 Output: c.region, c.customer_id
                                 ->  Parallel Seq Scan on public.customers c  (cost=0.00..11519.67 rows=416667 width=13)
                                       Output: c.region, c.customer_id
 Query Identifier: -3826100787633862198
 Supplied Plan Advice:
   SEQ_SCAN(i) /* matched */
   SEQ_SCAN(o) /* matched */

Step 5: Configure the session to use the stash

Now PostgreSQL consults this stash when planning queries in the session.

pg_stash_advice=# SET pg_stash_advice.stash_name = 'nightly_analytics';
SHOW pg_stash_advice.stash_name;
SET
 pg_stash_advice.stash_name
----------------------------
 nightly_analytics
(1 row)

pg_stash_advice=# SELECT *
FROM pg_get_advice_stash_contents('nightly_analytics');
    stash_name     |       query_id       |                  advice_string
-------------------+----------------------+--------------------------------------------------
 nightly_analytics | -3826100787633862198 | JOIN_ORDER(i o c) HASH_JOIN(o c) SEQ_SCAN(i o c)
(1 row)

pg_stash_advice=# EXPLAIN (VERBOSE, PLAN_ADVICE)
SELECT
    c.region,
    COUNT(DISTINCT o.order_id) AS total_orders,
    SUM(i.quantity * i.unit_price) AS total_revenue
FROM orders o
JOIN customers c
    ON c.customer_id = o.customer_id
JOIN order_items i
    ON i.order_id = o.order_id
WHERE o.order_date >= DATE '2026-12-01'
  AND o.order_date < DATE '2026-12-31'
  AND o.status = 'COMPLETED'
GROUP BY c.region
ORDER BY total_revenue DESC;                                                                        QUERY PLAN                                                                    
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=1598525.96..1598525.97 rows=4 width=45)
   Output: c.region, (count(DISTINCT o.order_id)), (sum(((i.quantity)::numeric * i.unit_price)))
   Sort Key: (sum(((i.quantity)::numeric * i.unit_price))) DESC
   ->  GroupAggregate  (cost=1430451.98..1598525.92 rows=4 width=45)
         Output: c.region, count(DISTINCT o.order_id), sum(((i.quantity)::numeric * i.unit_price))
         Group Key: c.region
         ->  Gather Merge  (cost=1430451.98..1582235.40 rows=1303237 width=23)
               Output: c.region, o.order_id, i.quantity, i.unit_price
               Workers Planned: 2
               ->  Sort  (cost=1429451.96..1430809.49 rows=543015 width=23)
                     Output: c.region, o.order_id, i.quantity, i.unit_price
                     Sort Key: c.region, o.order_id
                     ->  Parallel Hash Join  (cost=653203.97..1366591.06 rows=543015 width=23)
                           Output: c.region, o.order_id, i.quantity, i.unit_price
                           Inner Unique: true
                           Hash Cond: (o.customer_id = c.customer_id)
                           ->  Parallel Hash Join  (cost=634440.96..1336941.63 rows=543015 width=26)
                                 Output: o.order_id, o.customer_id, i.quantity, i.unit_price
                                 Inner Unique: true
                                 Hash Cond: (i.order_id = o.order_id)
                                 ->  Parallel Seq Scan on public.order_items i  (cost=0.00..460784.67 rows=16666667 width=18)
                                       Output: i.quantity, i.unit_price, i.order_id
                                 ->  Parallel Hash  (cost=625001.25..625001.25 rows=543017 width=16)
                                       Output: o.order_id, o.customer_id
                                       ->  Parallel Seq Scan on public.orders o  (cost=0.00..625001.25 rows=543017 width=16)
                                             Output: o.order_id, o.customer_id
                                             Filter: ((o.order_date >= '2026-12-01'::date) AND (o.order_date < '2026-12-31'::date) AND (o.status = 'COMPLETED'::text))
                           ->  Parallel Hash  (cost=11519.67..11519.67 rows=416667 width=13)
                                 Output: c.region, c.customer_id
                                 ->  Parallel Seq Scan on public.customers c  (cost=0.00..11519.67 rows=416667 width=13)
                                       Output: c.region, c.customer_id
 Query Identifier: -3826100787633862198
 Supplied Plan Advice:
   SEQ_SCAN(i) /* matched */
   SEQ_SCAN(o) /* matched */

the above /* matched */ show the pg_stash-advice is stored plan is used.

How long does pg_stash_advice apply the stored advice?

After creating an advice stash and saving the preferred plan advice, we configure the current session using:

SET pg_stash_advice.stash_name = 'nightly_analytics';

The setting remains active for the current session, so matching queries can use the advice stored in that stash. When the session disconnects, the session-level setting ends, but the stored advice is separate from that setting.

If the same stash should be selected automatically for future connections, PostgreSQL can be configured with a database-level or role-level default. The persistence of the stash contents across server restarts depends on the extension’s configuration and version.

This separation allows plan advice to be stored once and reused across matching queries without requiring the application SQL to be rewritten each time.

By using the above example we can domenstrate that the query uses Parallel Hash Joins to join order_items, orders, and customers, followed by aggregation and sorting to calculate total orders and revenue by region.

pg_stash_advice supplies the stored advice from analytics_stash: JOIN_ORDER(i o c) and HASH_JOIN(o). The /* matched */ markers confirm that both advice entries matched the query.

Why It Matters?

1. Targeted planner guidance

The feature allows advice to be associated with a specific query ID rather than attempting to influence every query in the database.

Query A → Normal planning
Query B → Normal planning
Query C → Stored plan advice
Query D → Normal planning

This makes the mechanism targeted.

2. Automatic application

Once the stash is configured, the DBA does not need to manually provide the advice every time the query is planned.

The process becomes:

Query
  ↓
Query ID
  ↓
Advice Stash
  ↓
Matching advice
  ↓
Planner

3. Useful for known planner issues

If a DBA identifies a query where a particular planner decision needs to be controlled, the advice can be associated with that query ID.

4. Persistent management

Advice stashes can optionally be persisted to disk.

The relevant configuration parameter is:

pg_stash_advice.persist

When persistence is enabled, stash information is stored in:

pg_stash_advice.tsv

A background worker handles loading and saving the information.

Caveats / Things to Watch Out For

1. Do not force advice unnecessarily

The PostgreSQL planner is designed to adapt to changes in data distribution. A plan that is efficient today may not be efficient after the data changes. Therefore, plan advice should be used only where there is a significant reason to control planner behavior.

2. Plan advice has a cost

Applying plan advice can introduce a noticeable performance cost even when it does not ultimately change the query plan. Therefore, avoid adding advice to every query.

3. Shared memory consumption

Advice stashes are stored in dynamically allocated shared memory. A large number of stored advice entries can increase memory consumption. DBAs should therefore consider the amount of advice being stored.

4. Query IDs are required

pg_stash_advice works using query identifiers.

Therefore:

No Query ID
     ↓
Cannot associate advice
     ↓
Cannot use the stash for that query

compute_query_id must be enabled.

5. Security considerations

The extension currently does not provide a sophisticated security model. Superusers, or users granted the required EXECUTE privileges, can create or modify advice stashes. However, users can set pg_stash_advice.stash_name for their own sessions. This means information contained in advice strings could potentially become visible to non-privileged users. Sensitive information should therefore not be embedded in plan advice.

Conclusion

PostgreSQL 19 introduces pg_stash_advice as a mechanism for storing and automatically applying plan advice based on query identifiers. PostgreSQL 18 did not provide this built-in capability.

The key idea is simple:

Query ID
   ↓
Stored Plan Advice
   ↓
Automatically Applied During Planning

pg_stash_advice does not replace PostgreSQL’s normal cost-based planning., it provides a targeted mechanism for cases where a DBA has identified a significant planner issue and needs to guide the planning process for specific queries.

References

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top