pg_plan_advice is one of the new extensions introduced in PostgreSQL 19, and it can be mainly used to generate the best advice for the query by the optimizer, and there are 5 postgres parameters based on this. We can use the parameters based on the pg_plan_advice extension to temporarily set the advice session-wise instead of setting different advice for different queries through some extensions like pg_stash_advice.
There are mainly 2 functionalities based on this extension.
- Generate the advice
- Enforcing that advice
Before using this extension, we need to set this in shared_preload_libraries or use the LOAD command to load this extension.
Check the value of this parameter.
show shared_preload_libraries ;
Result:
shared_preload_libraries
--------------------------------
pg_plan_advice,pg_stash_advice
(1 row)
Or LOAD the extension like this.
LOAD 'pg_plan_advice';
Result:
LOAD
Parameters based on this extension named pg_plan_advice.
1.pg_plan_advice.advice
Checking the current value.
show pg_plan_advice.advice ;
Result:
-[ RECORD 1 ]---------+-
pg_plan_advice.advice |
Check the metadata of this parameter from pg_settings like this.
select * from pg_settings where name = 'pg_plan_advice.advice';
Result:
-[ RECORD 1 ]---+--------------------------------------
name | pg_plan_advice.advice
setting |
unit |
category | Customized Options
short_desc | advice to apply during query planning
extra_desc |
context | user
vartype | string
source | default
min_val |
max_val |
enumvals |
boot_val |
reset_val |
sourcefile |
sourceline |
pending_restart | f
The source code part related to this parameter definition is from this extension code.
DefineCustomStringVariable("pg_plan_advice.advice",
"advice to apply during query planning",
NULL,
&pg_plan_advice_advice,
NULL,
PGC_USERSET,
0,
pg_plan_advice_advice_check_hook,
NULL,
NULL);Here, we can see that this parameter includes a check hook named pg_plan_advice_advice_check_hook. The purpose of this is to check whether the value is valid or not.
Purpose:
This parameter is mainly used to apply advice for all the queries executed in this session.
2.pg_plan_advice.always_explain_supplied_advice
Checking the current value.
show pg_plan_advice.always_explain_supplied_advice;
Result:
-[ RECORD 1 ]---------------------------------+---
pg_plan_advice.always_explain_supplied_advice | on
Check the metadata of this parameter from pg_settings like this.
select * from pg_settings where name = 'pg_plan_advice.always_explain_supplied_advice';
Result:
-[ RECORD 1 ]---+---------------------------------------------------------------------------
name | pg_plan_advice.always_explain_supplied_advice
setting | on
unit |
category | Customized Options
short_desc | EXPLAIN output includes supplied advice even without EXPLAIN (PLAN_ADVICE)
extra_desc |
context | user
vartype | bool
source | default
min_val |
max_val |
enumvals |
boot_val | on
reset_val | on
sourcefile |
sourceline |
pending_restart | f
The source code part related to this parameter definition is from this extension code.
DefineCustomBoolVariable("pg_plan_advice.always_explain_supplied_advice",
"EXPLAIN output includes supplied advice even without EXPLAIN (PLAN_ADVICE)",
NULL,
&pg_plan_advice_always_explain_supplied_advice,
true,
PGC_USERSET,
0,
NULL,
NULL,
NULL);Purpose:
This parameter is mainly used to show the advice generated in each query, whether they include the EXPLAIN(PLAN_ADVICE) or not.
3.pg_plan_advice.always_store_advice_details
Checking the current value.
show pg_plan_advice.always_store_advice_details;
Result :
-[ RECORD 1 ]------------------------------+----
pg_plan_advice.always_store_advice_details | off
Check the metadata of this parameter from pg_settings like this.
select * from pg_settings where name = 'pg_plan_advice.always_store_advice_details';
Result :
-[ RECORD 1 ]---+--------------------------------------------------------------
name | pg_plan_advice.always_store_advice_details
setting | off
unit |
category | Customized Options
short_desc | Generate advice strings even when seemingly not required
extra_desc | Use this option to see generated advice for prepared queries.
context | user
vartype | bool
source | default
min_val |
max_val |
enumvals |
boot_val | off
reset_val | off
sourcefile |
sourceline |
pending_restart | f
The source code part related to this parameter definition is from this extension code.
DefineCustomBoolVariable("pg_plan_advice.always_store_advice_details",
"Generate advice strings even when seemingly not required",
"Use this option to see generated advice for prepared queries.",
&pg_plan_advice_always_store_advice_details,
false,
PGC_USERSET,
0,
NULL,
NULL,
NULL);Purpose:
It makes the planner always record the advice details inside every plan it builds, so EXPLAIN can show them later, even when EXPLAIN wasn't the thing that triggered the planning. This is only related to the prepared statements.
4.pg_plan_advice.feedback_warnings;
Checking the current value.
show pg_plan_advice.feedback_warnings;
Result :
-[ RECORD 1 ]--------------------+----
pg_plan_advice.feedback_warnings | off
Check the metadata of this parameter from pg_settings like this.
select * from pg_settings where name = 'pg_plan_advice.feedback_warnings';
Result:
-[ RECORD 1 ]---+-------------------------------------------------
name | pg_plan_advice.feedback_warnings
setting | off
unit |
category | Customized Options
short_desc | Warn when supplied advice does not apply cleanly
extra_desc |
context | user
vartype | bool
source | default
min_val |
max_val |
enumvals |
boot_val | off
reset_val | off
sourcefile |
sourceline |
pending_restart | f
The source code part related to this parameter definition is from this extension code.
DefineCustomBoolVariable("pg_plan_advice.feedback_warnings",
"Warn when supplied advice does not apply cleanly",
NULL,
&pg_plan_advice_feedback_warnings,
false,
PGC_USERSET,
0,
NULL,
NULL,
NULL);Purpose:
The main purpose of this extension is to display a warning message when the advice is not applied properly to the query.
5.pg_plan_advice.trace_mask
Checking the current value.
show pg_plan_advice.trace_mask;
Result:
-[ RECORD 1 ]-------------+----
pg_plan_advice.trace_mask | off
Check the metadata of this parameter from pg_settings like this.
select * from pg_settings where name = 'pg_plan_advice.trace_mask';
Result :
-[ RECORD 1 ]---+-----------------------------------------------------------------------------
name | pg_plan_advice.trace_mask
setting | off
unit |
category | Customized Options
short_desc | Emit debugging messages showing the computed strategy mask for each relation
extra_desc |
context | user
vartype | bool
source | default
min_val |
max_val |
enumvals |
boot_val | off
reset_val | off
sourcefile |
sourceline |
pending_restart | f
The source code part related to this parameter definition is from this extension code.
DefineCustomBoolVariable("pg_plan_advice.trace_mask",
"Emit debugging messages showing the computed strategy mask for each relation",
NULL,
&pg_plan_advice_trace_mask,
false,
PGC_USERSET,
0,
NULL,
NULL,
NULL);Purpose:
The main purpose of this parameter is to provide complete debug messages to show exactly why the advice is matched or not.
Query examples based on this extension
Create some sample tables and insert some values like this.
CREATE TABLE emp (id int PRIMARY KEY, dept_id int, name text);
CREATE TABLE dept (id int PRIMARY KEY, dname text);
CREATE INDEX emp_dept_idx ON emp(dept_id);
Insert values.
INSERT INTO dept SELECT g, 'd'||g FROM generate_series(1,100) g;
INSERT INTO emp SELECT g, g%100+1, 'e'||g FROM generate_series(1,100000) g;
Execute the ANALYZE command to give the planner the latest statistics of tables.
ANALYZE;
Now, execute the query by passing the PLAN_ADVICE parameter inside the EXPLAIN query like this.
EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT * FROM emp e JOIN dept d ON d.id = e.dept_id;
Result:
QUERY PLAN
---------------------------------
Hash Join
Hash Cond: (e.dept_id = d.id)
-> Seq Scan on emp e
-> Hash
-> Seq Scan on dept d
Generated Plan Advice:
JOIN_ORDER(e d)
HASH_JOIN(d)
SEQ_SCAN(e d)
NO_GATHER(e d)
(10 rows)
Now, set the advice for a a sequential scan of the employee table like this.
SET pg_plan_advice.advice = 'SEQ_SCAN(emp)';
Now, execute the query like this and check its result.
EXPLAIN (COSTS OFF) SELECT * FROM emp WHERE id = 5;
Result:
QUERY PLAN
-------------------------------
Seq Scan on emp
Filter: (id = 5)
Supplied Plan Advice:
SEQ_SCAN(emp) /* matched */
(4 rows)
Now, we can see that the table is using sequential scanning, and also the advice we set using the SET command is matched here.
Now, try to change the advice from sequential scan to index scan like this.
SET pg_plan_advice.advice = 'INDEX_SCAN(emp emp_pkey)';
Result:
EXPLAIN (COSTS OFF) SELECT * FROM emp WHERE id = 5;
QUERY PLAN
------------------------------------------
Index Scan using emp_pkey on emp
Index Cond: (id = 5)
Supplied Plan Advice:
INDEX_SCAN(emp emp_pkey) /* matched */
(4 rows)
From the execution plan, we can see that the table is scanned using an index scan through the index named emp_pkey.
Also, we can see that the advice we set using the SET command is matched.
Now, try to change the advice to a bitmap scan like this.
SET pg_plan_advice.advice = 'BITMAP_HEAP_SCAN(emp)';
Now, execute the query and check its execution plan.
EXPLAIN (COSTS OFF) SELECT * FROM emp WHERE id = 5;
Result:
QUERY PLAN
---------------------------------------
Bitmap Heap Scan on emp
Recheck Cond: (id = 5)
-> Bitmap Index Scan on emp_pkey
Index Cond: (id = 5)
Supplied Plan Advice:
BITMAP_HEAP_SCAN(emp) /* matched */
(6 rows)
Here, we can see that the bitmap scan is applied.
Now, try to add the complete advice like this.
SET pg_plan_advice.advice = 'JOIN_ORDER(d e) NESTED_LOOP_PLAIN(e) INDEX_SCAN(e emp_dept_idx)';
Check the query execution plan.
EXPLAIN (COSTS OFF) SELECT * FROM emp e JOIN dept d ON d.id = e.dept_id;
Result:
QUERY PLAN
----------------------------------------------
Nested Loop
-> Seq Scan on dept d
-> Index Scan using emp_dept_idx on emp e
Index Cond: (dept_id = d.id)
Supplied Plan Advice:
INDEX_SCAN(e emp_dept_idx) /* matched */
JOIN_ORDER(d e) /* matched */
NESTED_LOOP_PLAIN(e) /* matched */
(8 rows)
Now, the three advices are applied and matched here.
Now, try to display some warning messages when the advice is not matched in the query execution plan.
show pg_plan_advice.feedback_warnings ;
Result :
pg_plan_advice.feedback_warnings
----------------------------------
off
(1 row)
Now enable this parameter.
SET pg_plan_advice.feedback_warnings = on;
Execute the query and check its execution plan.
EXPLAIN (COSTS OFF) SELECT * FROM emp e JOIN dept d ON d.id = e.dept_id;
Result:
WARNING: supplied plan advice was not enforced
DETAIL: advice NO_GATHER(emp) feedback is "not matched"
QUERY PLAN
------------------------------------
Hash Join
Hash Cond: (e.dept_id = d.id)
-> Seq Scan on emp e
-> Hash
-> Seq Scan on dept d
Supplied Plan Advice:
NO_GATHER(emp) /* not matched */
(7 rows)
Now, we can see that the warning message is because the advice is not matched here.
Check the log file like this.
cybrosys@cybrosys:~/postgres_19_rc1/postgres$ tail -f logfile
Result:
2026-10-17 11:21:21.204 IST [71767] LOG: checkpoint starting: time
2026-10-17 11:21:21.714 IST [71767] LOG: checkpoint complete: time: wrote 5 buffers (0.0%), wrote 0 SLRU buffers; 0 WAL file(s) added, 0 removed, 0 recycled; write=0.502 s, sync=0.003 s, total=0.511 s; sync files=5, longest=0.002 s, average=0.001 s; distance=22 kB, estimate=50110 kB; lsn=0/08D0E6B0, redo lsn=0/08D0E658
2026-10-17 15:17:26.172 IST [71767] LOG: checkpoint starting: time
2026-10-17 15:19:08.345 IST [71767] LOG: checkpoint complete: time: wrote 1019 buffers (6.2%), wrote 1 SLRU buffers; 0 WAL file(s) added, 0 removed, 2 recycled; write=102.165 s, sync=0.005 s, total=102.173 s; sync files=46, longest=0.001 s, average=0.001 s; distance=21149 kB, estimate=47214 kB; lsn=0/0A1B7908, redo lsn=0/0A1B5D70
2026-10-17 15:19:33.748 IST [241093] WARNING: supplied plan advice was not enforced
2026-10-17 15:19:33.748 IST [241093] DETAIL: advice NO_GATHER(emp) feedback is "not matched"
In the Postgres log file, we can also see the warning messages like this.
In PostgreSQL 19, the newly introduced extensions named pg_plan_advice and pg_stash_advice are exactly game changers. We can use both of these extensions to get the suggested advice, and we can set this advice based on our needs. We can set advice temporarily in session-wise through this extension named pg_plan_advice, and also we can set different advice for different queries based on the query_id and stash advice name through the extension named pg_stash_advice.
By understanding the functionalities of this extension, the dba or postgres developers can easily improve the performance of application queries.