Welcome to the 11th blog post of PG19 Hacktober. In database performance, query planning is a critical piece of the puzzle. PostgreSQL’s planner decides how to execute your SQL by choosing an execution plan based on statistics and cost estimates. Most of the time, it chooses well. But sometimes it doesn’t, and a plan that was fast yesterday can become painfully slow after a statistics refresh or a shift in your data. That’s where pg_plan_advice comes in, a powerful PostgreSQL extension designed to help you understand, evaluate, and improve the query plans your database generates.
Until now, PostgreSQL gave you only indirect ways to deal with this: toggling enable_* settings, rewriting queries, or reaching for third-party extensions. PostgreSQL 19 introduces a new contrib module, pg_plan_advice, that takes a more direct and verifiable approach. It lets you:
- See the planner’s key decisions (join order, join methods, scan types, parallelism) as a compact advice string
- Reproduce a plan you trust by feeding that string back in
- Experiment with alternative plans the planner considers inferior
- Verify whether your advice was actually applied, through built-in feedback in EXPLAIN
Understanding the PostgreSQL Query Planner
Before exploring pg_plan_advice, let’s understand the problem it addresses.
Consider the following query:
SELECT
o.order_id,
o.amount,
c.customer_name
FROM orders o
JOIN customers c
ON o.customer_id = c.customer_id
WHERE c.city = 'Hyderabad';This query retrieves orders belonging to customers from Hyderabad.
PostgreSQL must decide how to execute it efficiently. Some of the important decisions include:
- Scan method: Should PostgreSQL read the entire table using a sequential scan or retrieve selected rows through an index?
- Join method: Should it use a nested-loop join, hash join, or merge join?
- Join order: Which relation should be processed first when multiple tables are involved?
- Parallel execution: Should the query use parallel workers?
Different decisions can produce different execution plans, even when the SQL statement remains unchanged.
For example, a hash join may be efficient when joining large datasets, while a nested-loop join may work well when one side contains very few rows and the other side can be accessed efficiently through an index.
The important point is that there is no universally optimal execution plan. The best choice depends on data distribution, table sizes, available indexes, system resources, and workload characteristics.
Inspecting the execution plan
PostgreSQL provides the EXPLAIN command to display the execution plan selected by the planner.
EXPLAIN ANALYZE executes the query and reports actual execution statistics, while BUFFERS provides information about database buffer activity.
These commands are essential for understanding the planner’s decisions. However, they do not, by themselves, provide a direct way to express targeted constraints on those decisions.
That is where pg_plan_advice becomes useful.
What is pg_plan_advice?
pg_plan_advice is a PostgreSQL module that allows important query planning decisions to be represented using a specialized advice language.
It has two central capabilities:
- Generate plan advice: Inspect a plan selected by PostgreSQL and obtain an advice string describing important planning decisions.
- Supply plan advice: Provide an advice string to influence subsequent query planning.
Instead of replacing the query planner, the module constrains the choices available to it. PostgreSQL still performs the planning process, but some decisions can be restricted according to the supplied advice.
For example, an advice string might look like this:
- JOIN_ORDER(f d)
- HASH_JOIN(d)
- SEQ_SCAN(f d)
- NO_GATHER(f d)
These entries describe decisions about join ordering, join methods, scan methods, and parallel execution.
The names f and d represent relation aliases in the SQL query.
The important distinction is that plan advice describes what the planner should do, but it does not implement a separate query execution engine. PostgreSQL continues to construct and execute the plan.
Advice That Narrows the Planner’s Options
Plan advice is written as instructions (“use this join order”, “hash join this table”), but under the hood the module works the other way around. It tells the core planner what not to do, removing options from the table until only plans matching your advice remain.
Three consequences follow, and they shape how you should think about the tool:
- You can only get plans the planner would have considered anyway. If a plan would return wrong results, or the planner discards it for reasons unrelated to cost, no advice can resurrect it. A classic example is using an index for a bare SELECT * FROM t.
- Planning never fails because of advice. Ask for something impossible and you’ll get a plan with some nodes marked Disabled: true, often worse than the unadvised plan.
- Verification is part of the job. Because bad advice degrades quietly, you need to confirm that your advice applied. The module’s feedback system exists for exactly this.
Advice Tags
Every line in a plan advice string is a tag. A tag is an instruction about one aspect of the plan: how tables are joined, in what order, how each table is scanned, or whether parallelism is used. Several different classes of advice tags exist, each controlling a different aspect of query planning.
| Category | Examples | Purpose |
| Scan methods | SEQ_SCAN, TID_SCAN, INDEX_SCAN, INDEX_ONLY_SCAN, BITMAP_HEAP_SCAN, DO_NOT_SCAN | Control or constrain how a relation is accessed. |
| Join order | JOIN_ORDER | Specify the order in which relations are joined. |
| Join methods | HASH_JOIN, MERGE_JOIN_PLAIN, MERGE_JOIN_MATERIALIZE, NESTED_LOOP_PLAIN, NESTED_LOOP_MATERIALIZE, NESTED_LOOP_MEMOIZE | Constrain the join method used for specified relations. |
| Foreign joins | FOREIGN_JOIN | Request that a join between foreign tables be pushed down to a remote server. |
| Partitionwise joins | PARTITIONWISE | Control whether specified relations participate in a partitionwise join. |
| Semijoin uniqueness | SEMIJOIN_UNIQUE, SEMIJOIN_NON_UNIQUE | Select between semijoin implementation strategies. |
| Parallel query | GATHER, GATHER_MERGE, NO_GATHER | Constrain where parallel-query gathering nodes appear. |
Reading and Writing the Advice Language
An advice string is a list of tags, each taking one or more targets.
Targets identify a specific relation in a specific query. For simple queries, that’s the alias (o, c). For subqueries, repeated aliases, and partitions, the full form is:
alias#occurrence/partition_schema.partition_name@plan_nameEverything after the alias is optional, so you include a component only when you need it. You don’t need to memorize this: the easiest way to find the right target is to ask EXPLAIN to generate advice and read the targets from it.
Hands-On
Setting Up the Environment
For this demonstration, we will create a simple sales database containing customers and orders.
The experiment will compare the original execution plan with a plan generated after applying selected advice.
Step 1: Verify the PostgreSQL version
Connect to your PostgreSQL server and run:
postgres=# SELECT version();
version
--------------------------------------------------------------------------------------------------------------
PostgreSQL 19beta4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-14), 64-bit
(1 row)The demonstration targets PostgreSQL 19 with the pg_plan_advice module available.
If you are using PostgreSQL 16 or 18, do not assume the module is installed or supported in that environment. Use a suitable PostgreSQL 19 build that includes the module.
Step 2: Load pg_plan_advice
The module must be available in the PostgreSQL server installation before it can be loaded.
For a single-session experiment, run:
LOAD 'pg_plan_advice';Alternatively, PostgreSQL supports loading the module through session_preload_libraries for new sessions or shared_preload_libraries at server startup.
For example, a persistent configuration may include:
ALTER SYSTEM SET shared_preload_libraries = 'pg_plan_advice';If the module cannot be loaded, verify that it was built and installed for the correct PostgreSQL server version.
Step 3: Verify the module
Run a simple planning test:
EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT 1;The PLAN_ADVICE option requests plan advice in the EXPLAIN output.
If PostgreSQL reports that the option is unrecognized, or that it cannot load the module, resolve the installation issue before continuing.
4. Creating the Sample Database
Step 1: Create a database
From the terminal, run:
create database plan_advice_demo;
psql -d plan_advice_demoUse the appropriate PostgreSQL role or connection options if your environment requires authentication.
Step 2: Create the tables
Create a customers & orders table:
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
customer_name TEXT NOT NULL,
city TEXT NOT NULL
);
----
CREATE TABLE orders (
order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id INTEGER NOT NULL
REFERENCES customers(customer_id),
order_date DATE NOT NULL,
amount NUMERIC(10, 2) NOT NULL
);The customers table stores customer information, while the orders table stores orders associated with each customer.
The primary key on customers.customer_id automatically creates a unique index. We will not initially create an index on customers. city, allowing us to observe the planner’s choices without that additional index.
Step 3: Insert customer data
We will insert 100,000 customers distributed across several cities.
INSERT INTO customers (
customer_id,
customer_name,
city
)
SELECT
g,
'Customer ' || g,
CASE
WHEN g % 20 = 0 THEN 'Hyderabad'
WHEN g % 20 = 1 THEN 'Bengaluru'
WHEN g % 20 = 2 THEN 'Chennai'
ELSE 'Other'
END
FROM generate_series(1, 100000) AS g;The query uses generate_series() to produce customer identifiers.
Every twentieth customer belongs to Hyderabad, so the dataset contains 5,000 Hyderabad customers.
Step 4: Insert order data
Next, insert one million orders:
INSERT INTO orders (
customer_id,
order_date,
amount
)
SELECT
(g % 100000) + 1,
DATE '2025-01-01' + (g % 365)::INTEGER,
((g % 50000) + 100)::NUMERIC(10, 2) / 100
FROM generate_series(1, 1000000) AS g;Each customer receives ten orders in this dataset.
Step 5: Update statistics
After loading the data, collect table statistics:
ANALYZE customers;
ANALYZE orders;The planner uses these statistics to estimate row counts and compare alternative execution plans.
Experiment 1: Inspect the Original Execution Plan
Now that the database is ready, run the following query without supplying any plan advice:
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT
o.order_id,
o.amount,
c.customer_name
FROM orders o
JOIN customers c
ON o.customer_id = c.customer_id
WHERE c.city = 'Hyderabad';
This gives us the baseline execution plan.
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=3038.25..19488.09 rows=48197 width=28) (actual time=14.027..88.142 rows=50000.00 loops=1)
Output: o.order_id, o.amount, c.customer_name
-> Hash Join (cost=2038.25..13668.39 rows=20082 width=28) (actual time=12.136..72.476 rows=16666.67 loops=3)
Output: o.order_id, o.amount, c.customer_name
Inner Unique: true
Hash Cond: (o.customer_id = c.customer_id)
Buffers: shared hit=8554
Worker 0: actual time=10.586..70.382 rows=17755.00 loops=1
Buffers: shared hit=2992
Worker 1: actual time=12.328..68.770 rows=15380.00 loops=1
Buffers: shared hit=2686
-> Parallel Seq Scan on public.orders o (cost=0.00..10536.41 rows=416641 width=18) (actual time=0.017..22.895 rows=333333.33 loops=3)
Output: o.order_id, o.customer_id, o.order_date, o.amount
Buffers: shared hit=6370
Worker 0: actual time=0.018..23.398 rows=355448.00 loops=1
Buffers: shared hit=2264
Worker 1: actual time=0.018..22.510 rows=307406.00 loops=1
Buffers: shared hit=1958
-> Hash (cost=1978.00..1978.00 rows=4820 width=18) (actual time=12.055..12.057 rows=5000.00 loops=3)
Output: c.customer_name, c.customer_id
Buckets: 8192 Batches: 1 Memory Usage: 318kB
Buffers: shared hit=2184
Worker 0: actual time=10.485..10.487 rows=5000.00 loops=1
Buffers: shared hit=728
Worker 1: actual time=12.236..12.238 rows=5000.00 loops=1
Buffers: shared hit=728
-> Seq Scan on public.customers c (cost=0.00..1978.00 rows=4820 width=18) (actual time=0.027..10.575 rows=5000.00 loops=3)
Output: c.customer_name, c.customer_id
Filter: (c.city = 'Hyderabad'::text)
Rows Removed by Filter: 95000
Buffers: shared hit=2184
Worker 0: actual time=0.025..8.961 rows=5000.00 loops=1
Buffers: shared hit=728
Worker 1: actual time=0.038..10.606 rows=5000.00 loops=1
Buffers: shared hit=728
Planning:
Buffers: shared hit=6
Planning Time: 0.468 ms
Experiment 2: Generate Plan Advice
This is the first major step in using pg_plan_advice.
Execute the same query with the PLAN_ADVICE option:
EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT
o.order_id,
o.amount,
c.customer_name
FROM orders o
JOIN customers c
ON o.customer_id = c.customer_id
WHERE c.city = 'Hyderabad';
The module displays the selected plan and a generated advice string.
QUERY PLAN
--------------------------------------------------------
Gather
Workers Planned: 2
-> Hash Join
Hash Cond: (o.customer_id = c.customer_id)
-> Parallel Seq Scan on orders o
-> Hash
-> Seq Scan on customers c
Filter: (city = 'Hyderabad'::text)
Generated Plan Advice:
JOIN_ORDER(o c)
HASH_JOIN(c)
SEQ_SCAN(o c)
GATHER((o c))
(13 rows)
Time: 0.905 msUnderstanding the advice tags
- JOIN_ORDER(o c): This specifies that o should be the driving relation and c should be the first relation joined to it.
- HASH_JOIN(c): This requests that c appear on the inner side of a hash join.
- SEQ_SCAN(o c): This requests sequential scans for the specified relations.
- GATHER(o c): Requests a `Gather` node above the join of `o` and `c`.
What have we learned?
The planner’s decisions can be represented in a compact, human-readable format. Instead of relying only on a visual inspection of the plan tree, we now have an explicit description of selected planning decisions. We can use that description to reproduce some decisions or selectively constrain them.
Experiment 3: Supply Plan Advice
Generating advice is only half of the workflow. We can now provide advice to influence how PostgreSQL plans the query.
Step 1: Supply a join-order constraint
Suppose we want to experiment with the join order while leaving other planning choices unrestricted.
SET pg_plan_advice.advice = 'JOIN_ORDER(o c)';The pg_plan_advice.advice parameter accepts an advice string used during query planning.
Step 2: Inspect the resulting plan
EXPLAIN (COSTS OFF)
SELECT
o.order_id,
o.amount,
c.customer_name
FROM orders o
JOIN customers c
ON o.customer_id = c.customer_id
WHERE c.city = 'Hyderabad';The output includes information about the supplied advice, resembling:
Supplied Plan Advice:
JOIN_ORDER(o c) /* matched */The matched status indicates that the advice targets were observed together during planning at a point where the advice could be enforced. It is not, by itself, proof that the resulting plan is faster.
Step 3: Apply more than one constraint
You can also experiment with a more restrictive advice string:
SET pg_plan_advice.advice = 'JOIN_ORDER(o c) HASH_JOIN(c)';Inspect the plan again:
EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT
o.order_id,
o.amount,
c.customer_name
FROM orders o
JOIN customers c
ON o.customer_id = c.customer_id
WHERE c.city = 'Hyderabad';This requests a particular join order and a hash join with c on the inner side.
However, the requested choices may be incompatible with each other or with the default plans available to PostgreSQL. Always inspect the resulting plan and provide feedback.
Experiment 4: Compare Performance
A changed execution plan is not necessarily a better execution plan.
The next step is to compare the baseline and advised query using actual execution statistics.
Step 1: Clear the advice
RESET pg_plan_advice.advice;Step 2: Measure the baseline
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT
o.order_id,
o.amount,
c.customer_name
FROM orders o
JOIN customers c
ON o.customer_id = c.customer_id
WHERE c.city = 'Hyderabad';Record the plan and execution time.
Step 3: Measure the advised query
SET pg_plan_advice.advice = 'JOIN_ORDER(o c)';
Then execute:
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT
o.order_id,
o.amount,
c.customer_name
FROM orders o
JOIN customers c
ON o.customer_id = c.customer_id
WHERE c.city = 'Hyderabad';The advised execution took 0.446 ms longer, approximately 0.61% slower in this single comparison. This difference is too small, and the sample too limited, to establish a performance regression. Normal runtime variability can explain it.
The plans used the same join method, scan methods, worker count, and buffer-hit count. This supports the observation that the advice did not materially change the execution strategy. The planner was already using a plan compatible with the requested join order.
For a stronger performance claim, run both versions repeatedly under comparable conditions and compare the results. A matched advice string is evidence about the planning constraint, not a performance guarantee.
Advice Feedback and Configuration
The EXPLAIN output reports feedback for supplied advice. Common statuses include:
| Feedback | Meaning |
| matched | The targets were observed together when the advice could be enforced. |
| partially matched | Some targets matched, or the requested combination was not observed together as required. |
| not matched | None of the specified targets was observed. |
| inapplicable | The requested operation could not be applied, such as requesting a nonexistent index. |
An entry can have multiple status labels, such as matched, inapplicable, failed. Therefore, read the complete feedback rather than checking only for matched.
Useful configuration parameters include:
Warn when supplied advice is not successfully enforced
SET pg_plan_advice.feedback_warnings = on; Inspect whether supplied advice is normally shown in EXPLAIN
SHOW pg_plan_advice.always_explain_supplied_advice;Inspect the current advice string
SHOW pg_plan_advice.advice;pg_plan_advice.feedback_warnings defaults to false. pg_plan_advice.always_explain_supplied_advice defaults to true; when disabled, supplied-advice details are shown only with EXPLAIN (PLAN_ADVICE).
The module also provides pg_plan_advice.always_store_advice_details for prepared-query inspection and pg_plan_advice.trace_mask for debugging. These settings can add overhead and are best enabled only when needed.
Limitations and Best Practices
Keep the following limitations in mind:
- Advice does not guarantee faster queries. A matched constraint can leave the plan unchanged or result in slower execution.
- It cannot force every possible plan. Advice only influences plans the core planner considers viable.
- Aggregation and set operations are not controllable through this module. This includes aggregation strategies and planning decisions for operations such as UNION and INTERSECT.
- Invalid or incompatible advice can produce disabled nodes. PostgreSQL can still produce a plan, but it may not comply with the advice and may be less efficient.
Best practices:
- Inspect the original plan before applying advice.
- Check statistics, indexes, and query structure before forcing a planning choice.
- Apply the minimum constraints necessary for the experiment.
- Review the full feedback and look for disabled nodes.
- Compare actual execution time and buffer activity over repeated runs.
- Revalidate advice when the data distribution, indexes, workload, or PostgreSQL version changes.
Conclusion
pg_plan_advice provides a practical way to inspect and influence PostgreSQL query-planning decisions through a dedicated advice language. It supports targeted experiments involving join order, join methods, scan methods, partitionwise joins, semijoin strategies, and parallel execution.
The key takeaway is to treat pg_plan_advice as a query-planning investigation tool, not an automatic performance optimizer. Inspect the plan, apply targeted advice, check feedback, and validate the result using representative workloads.