In postgresql 19, there are newly 2 contrib extensions were introduced, and they work together. The names of those extensions are pg_plan_advice and pg_stash_advice. The main purpose of the extension named pg_plan_advice is to generate plan advice based on the query we pass, and we can use the extension named pg_stash_advice to automatically apply the advice to the query based on the query_id dynamically. This can be much more beneficial for the most frequently executing application queries.
So in this article, we actually look at more details from its source code also.
If you have the source code of postgres 19, go to the folder named contrib, and you can see the folders named pg_plan_advice and pg_stash_advice.
Source code structure of the pg_stash_advice extension from postgres 19.
cybrosys@cybrosys:~/postgres_19_rc1/postgres/contrib$ ls pg_stash_advice/
Result:
expected meson.build pg_stash_advice.c pg_stash_advice.h pg_stash_advice.so stashfuncs.c stashpersist.c t
Makefile pg_stash_advice--1.0.sql pg_stash_advice.control pg_stash_advice.o sql stashfuncs.o stashpersist.o
We can see the Makefile of this extension, which includes the regression test-related file also.
cat Makefile
You can see this line in the Makefile.
REGRESS = pg_stash_advice pg_stash_advice_utf8
Now, execute the regression test on the pg_stash_advice extension like this.
make check
Result:
# +++ regress check in contrib/pg_stash_advice +++
# initializing database system by copying initdb template
# using temp instance on port 58928 with PID 47848
ok 1 - pg_stash_advice 670 ms
ok 2 - pg_stash_advice_utf8 6 ms
1..2
# All 2 tests passed.
TAP tests not enabled. Try configuring with --enable-tap-tests
Now, we can see that the check only executes the regression test, and the tap test is not enabled in this build.
So, during the configuration of the source code of postgres 19, configure with the option named --enable-tap-tests to enable the tap test.
./configure --prefix=$(pwd) --enable-cassert --enable-debug --with-lz4 --enable-tap-tests
Now, execute this command to see the result of the tap test and regression test like this.
make check
Result:
# +++ regress check in contrib/pg_stash_advice +++
# initializing database system by copying initdb template
# using temp instance on port 58928 with PID 54801
ok 1 - pg_stash_advice 694 ms
ok 2 - pg_stash_advice_utf8 7 ms
1..2
# All 2 tests passed.
echo "# +++ tap check in contrib/pg_stash_advice +++" && rm -rf '/home/cybrosys/postgres_19_rc1/postgres/contrib/pg_stash_advice'/tmp_check && /usr/bin/mkdir -p '/home/cybrosys/postgres_19_rc1/postgres/contrib/pg_stash_advice'/tmp_check && cd . && TESTLOGDIR='/home/cybrosys/postgres_19_rc1/postgres/contrib/pg_stash_advice/tmp_check/log' TESTDATADIR='/home/cybrosys/postgres_19_rc1/postgres/contrib/pg_stash_advice/tmp_check' PATH="/home/cybrosys/postgres_19_rc1/postgres/tmp_install/home/cybrosys/postgres_19_rc1/postgres/bin:/home/cybrosys/postgres_19_rc1/postgres/contrib/pg_stash_advice:$PATH" LD_LIBRARY_PATH="/home/cybrosys/postgres_19_rc1/postgres/tmp_install/home/cybrosys/postgres_19_rc1/postgres/lib" INITDB_TEMPLATE='/home/cybrosys/postgres_19_rc1/postgres'/tmp_install/initdb-template PGPORT='65432' top_builddir='/home/cybrosys/postgres_19_rc1/postgres/contrib/pg_stash_advice/../..' PG_REGRESS='/home/cybrosys/postgres_19_rc1/postgres/contrib/pg_stash_advice/../../src/test/regress/pg_regress' share_contrib_dir='/home/cybrosys/postgres_19_rc1/postgres/tmp_install/home/cybrosys/postgres_19_rc1/postgres/share/extension' /usr/bin/prove -I ../../src/test/perl/ -I . t/*.pl
# +++ tap check in contrib/pg_stash_advice +++
t/001_persist.pl .. ok
All tests successful.
Files=1, Tests=6, 1 wallclock secs ( 0.01 usr 0.00 sys + 0.08 cusr 0.15 csys = 0.24 CPU)
Result: PASS
Now, the tap test and regression test are executed successfully without any errors.
Now, the logs during the tap test are saved separately in the log folder like this.
cybrosys@cybrosys:~/postgres_19_rc1/postgres/contrib/pg_stash_advice$ ls log/
Result:
initdb.log postmaster.log
Check the contents of the results folder like this.
cybrosys@cybrosys:~/postgres_19_rc1/postgres/contrib/pg_stash_advice$ ls results/
Result:
pg_stash_advice.out pg_stash_advice_utf8.out
Now, check whether this extension is available for creation in postgres like this.
select * from pg_available_extensions where name = 'pg_stash_advice';
Result:
name | default_version | installed_version | location | comment
-----------------+-----------------+-------------------+----------+-------------------------------------------
pg_stash_advice | 1.0 | | $system | store and automatically apply plan advice
(1 row)
Now create the extension.
create extension pg_stash_advice ;
Now, check the version of this created extension like this.
select * from pg_available_extensions where name = 'pg_stash_advice';
Result:
name | default_version | installed_version | location | comment
-----------------+-----------------+-------------------+----------+-------------------------------------------
pg_stash_advice | 1.0 | 1.0 | $system | store and automatically apply plan advice
(1 row)
Now, check the functionalities provided by this extension.
\dx+ pg_stash_advice
Result:
Objects in extension "pg_stash_advice"
Object description
--------------------------------------------------
function pg_create_advice_stash(text)
function pg_drop_advice_stash(text)
function pg_get_advice_stash_contents(text)
function pg_get_advice_stashes()
function pg_set_stashed_advice(text,bigint,text)
function pg_start_stash_advice_worker()
(6 rows)
Before using this extension, we need to set this extension name in the parameter named shared_preload_libraries like this.
So, before that, check the value of this parameter.
show shared_preload_libraries ;
Result:
shared_preload_libraries
--------------------------
(1 row)
Check the path of the postgres config file.
show config_file ;
Result:
config_file
-----------------------------------------------------------------
/home/cybrosys/postgres_19_rc1/postgres/pg_data/postgresql.conf
(1 row)
Set the value for this parameter like this.
Add these two extensions named like this because the plan advice generated from pg_plan_advice will be automatically applied by the planner through the extension named pg_stash_advice.
shared_preload_libraries = 'pg_stash_advice,pg_plan_advice'
Now, restart postgres to reflect the new values.
If you are using postgres source code, you can restart like this from the Postgres folder.
bin/pg_ctl -D pg_data/ -l logfile -o "-p 5440" restart
If you are using this as a system-installed postgres, the normal postgres server restart command is fine.
sudo systemctl restart postgresql
Now, check the parameters related to the pg_stash_advice extension like this.
show pg_stash_advice.persist;
Result:
pg_stash_advice.persist
-------------------------
on
(1 row)
Now, check this parameter-related metadata from pg_settings like this.
select * from pg_settings where name = 'pg_stash_advice.persist';
Result:
-[ RECORD 1 ]---+--------------------------------------------------------
name | pg_stash_advice.persist
setting | on
unit |
category | Customized Options
short_desc | Save and restore advice stash contents across restarts.
extra_desc |
context | postmaster
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 is from the file named pg_stash_advice.c.
/* Define our GUCs. */
if (process_shared_preload_libraries_in_progress)
DefineCustomBoolVariable("pg_stash_advice.persist",
"Save and restore advice stash contents across restarts.",
NULL,
&pg_stash_advice_persist,
true,
PGC_POSTMASTER,
0,
NULL,
NULL,
NULL);
else
pg_stash_advice_persist = false;
Check another parameter named pg_stash_advice.persist_interval.
show pg_stash_advice.persist_interval ;
Result:
pg_stash_advice.persist_interval
----------------------------------
30s
(1 row)
Now, check this parameter-related metadata from pg_settings like this.
select * from pg_settings where name = 'pg_stash_advice.persist_interval';
Result:
-[ RECORD 1 ]---+-------------------------------------------------
name | pg_stash_advice.persist_interval
setting | 30
unit | s
category | Customized Options
short_desc | Interval between advice stash saves, in seconds.
extra_desc |
context | sighup
vartype | integer
source | default
min_val | 0
max_val | 3600
enumvals |
boot_val | 30
reset_val | 30
sourcefile |
sourceline |
pending_restart | f
The source code part related to this parameter is from the file named pg_stash_advice.c.
DefineCustomIntVariable("pg_stash_advice.persist_interval",
"Interval between advice stash saves, in seconds.",
NULL,
&pg_stash_advice_persist_interval,
30,
0,
3600,
PGC_SIGHUP,
GUC_UNIT_S,
NULL,
NULL,
NULL);Now, check the last parameter named pg_stash_advice.stash_name.
show pg_stash_advice.stash_name;
Result:
select * from pg_settings where name = 'pg_stash_advice.stash_name';
Now, check this parameter-related metadata from pg_settings like this.
select * from pg_settings where name = 'pg_stash_advice.stash_name';
Result:
-[ RECORD 1 ]---+-----------------------------------------------------
name | pg_stash_advice.stash_name
setting |
unit |
category | Customized Options
short_desc | Name of the advice stash to be used in this session.
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 is from the file named pg_stash_advice.c
DefineCustomStringVariable("pg_stash_advice.stash_name",
"Name of the advice stash to be used in this session.",
NULL,
&pg_stash_advice_stash_name,
"",
PGC_USERSET,
0,
pgsa_check_stash_name_guc,
NULL,
NULL);Now, try some query examples to easily understand the exact purpose of this extension.
Set the max_parallel_workers_per_gather parameter value to 0 in this session.
SET max_parallel_workers_per_gather = 0;
Now, create some sample tables and some sample values.
CREATE TABLE customers (id int PRIMARY KEY, name text, city text);
CREATE TABLE orders (id int PRIMARY KEY, customer_id int REFERENCES customers(id),
amount numeric, status text);
CREATE INDEX orders_customer_idx ON orders(customer_id);
CREATE INDEX orders_status_idx ON orders(status);
INSERT INTO customers SELECT g, 'cust_'||g, (ARRAY['Kochi','Delhi','Mumbai'])[g%3+1]
FROM generate_series(1,10000) g;
INSERT INTO orders SELECT g, (g%10000)+1, g%500,
(ARRAY['new','paid','shipped','cancelled'])[g%4+1]
FROM generate_series(1,200000) g;
Now, execute vaccum analyze for removing dead tuples and get the planner about the latest statistics about these tables.
VACUUM ANALYZE customers, orders;
Now, create a function to get the query id from the EXPLAIN (VERBOSE, COSTS OFF) like this.
CREATE FUNCTION get_query_id(q text) RETURNS bigint LANGUAGE plpgsql AS $$
DECLARE l text;
BEGIN
FOR l IN EXECUTE 'EXPLAIN (VERBOSE, COSTS OFF) ' || q LOOP
IF l ~ 'Query Identifier:' THEN
RETURN substring(l from 'Query Identifier:\s*(-?\d+)')::bigint;
END IF;
END LOOP;
RAISE EXCEPTION 'no Query Identifier (is compute_query_id on?)';
END $$;
Now, create another function to get the plan advice generated by the planner.
CREATE FUNCTION get_plan_advice(q text) RETURNS text LANGUAGE plpgsql AS $$
DECLARE l text; grab bool := false; adv text := '';
BEGIN
FOR l IN EXECUTE 'EXPLAIN (COSTS OFF, PLAN_ADVICE) ' || q LOOP
IF grab THEN adv := adv || ' ' || btrim(l); END IF;
IF l ~ '^Generated Plan Advice:' THEN grab := true; END IF;
END LOOP;
RETURN btrim(adv);
END $$;
Now, execute the below query with the EXPLAIN command with options named VERBOSE, COSTS OFF, and PLAN_ADVICE
EXPLAIN (VERBOSE,COSTS OFF, PLAN_ADVICE)
SELECT c.name, o.amount FROM customers c JOIN orders o ON o.customer_id = c.id
WHERE c.city = 'Kochi' AND o.status = 'paid';
Result:
QUERY PLAN
---------------------------------------------------------
Hash Join
Output: c.name, o.amount
Inner Unique: true
Hash Cond: (o.customer_id = c.id)
-> Bitmap Heap Scan on public.orders o
Output: o.id, o.customer_id, o.amount, o.status
Recheck Cond: (o.status = 'paid'::text)
-> Bitmap Index Scan on orders_status_idx
Index Cond: (o.status = 'paid'::text)
-> Hash
Output: c.name, c.id
-> Seq Scan on public.customers c
Output: c.name, c.id
Filter: (c.city = 'Kochi'::text)
Query Identifier: 7514040773558589065
Generated Plan Advice:
JOIN_ORDER(o c)
HASH_JOIN(c)
SEQ_SCAN(c)
BITMAP_HEAP_SCAN(o)
NO_GATHER(c o)
(21 rows)
Now, in this result, we can see the query identifier and also the suggested plan advice.
Now, use our function to get the query id of the query below
SELECT get_query_id(q
SELECT c.name, o.amount FROM customers c JOIN orders o ON o.customer_id = c.id
WHERE c.city = 'Kochi' AND o.status = 'paid'
q
);
Result:
get_query_id
---------------------
7514040773558589065
(1 row)
Now, create an advice name like this.
SELECT pg_create_advice_stash('prod');Result:
pg_create_advice_stash
------------------------
(1 row)
Now, set the query identifier and the advice for this advice named prod like this.
SELECT pg_set_stashed_advice('prod', 7514040773558589065,
'JOIN_ORDER(c o) NESTED_LOOP_PLAIN(o) INDEX_SCAN(o orders_customer_idx)');Result:
pg_set_stashed_advice
-----------------------
(1 row)
Now, execute this query and check the generated plan advice like this.
EXPLAIN (VERBOSE,COSTS OFF, PLAN_ADVICE)
SELECT c.name, o.amount FROM customers c JOIN orders o ON o.customer_id = c.id
WHERE c.city = 'Kochi' AND o.status = 'paid';
Result:
QUERY PLAN
---------------------------------------------------------
Hash Join
Output: c.name, o.amount
Inner Unique: true
Hash Cond: (o.customer_id = c.id)
-> Bitmap Heap Scan on public.orders o
Output: o.id, o.customer_id, o.amount, o.status
Recheck Cond: (o.status = 'paid'::text)
-> Bitmap Index Scan on orders_status_idx
Index Cond: (o.status = 'paid'::text)
-> Hash
Output: c.name, c.id
-> Seq Scan on public.customers c
Output: c.name, c.id
Filter: (c.city = 'Kochi'::text)
Query Identifier: 7514040773558589065
Generated Plan Advice:
JOIN_ORDER(o c)
HASH_JOIN(c)
SEQ_SCAN(c)
BITMAP_HEAP_SCAN(o)
NO_GATHER(c o)
(21 rows)
Now, set this advice to the parameter named pg_stash_advice.stash_name.
SET pg_stash_advice.stash_name = 'prod';
Now, execute the query below to check whether the advice is applied automatically or not.
EXPLAIN (COSTS OFF)
SELECT c.name, o.amount FROM customers c JOIN orders o ON o.customer_id = c.id
WHERE c.city = 'Kochi' AND o.status = 'paid';
Result:
QUERY PLAN
--------------------------------------------------------
Nested Loop
-> Seq Scan on customers c
Filter: (city = 'Kochi'::text)
-> Index Scan using orders_customer_idx on orders o
Index Cond: (customer_id = c.id)
Filter: (status = 'paid'::text)
Supplied Plan Advice:
INDEX_SCAN(o orders_customer_idx) /* matched */
JOIN_ORDER(c o) /* matched */
NESTED_LOOP_PLAIN(o) /* matched */
(10 rows)
Now, set the client_min_messages to debug2 to see the applied advice in the log like this.
SET client_min_messages = debug2;
Now, execute this query to check this.
SELECT c.name, o.amount FROM customers c JOIN orders o ON o.customer_id = c.id
WHERE c.city = 'Mumbai' AND o.status = 'new';
Result:
DEBUG: supplying automatic advice for stash "prod", query ID 7514040773558589065: JOIN_ORDER(c o) NESTED_LOOP_PLAIN(o) INDEX_SCAN(o orders_customer_idx)
name | amount
-----------+--------
cust_5 | 4
cust_5 | 4
cust_5 | 4
.....................
.....................
Here, this debug shows that the advice is automatically applied for the query id 7514040773558589065.
We can list the total number of stashed advices by using this function named pg_get_advice_stashes.
SELECT * FROM pg_get_advice_stashes();
Result:
stash_name | num_entries
------------+-------------
prod | 1
(1 row)
We can also check more details about this stashed advice by passing the advice name to the function named pg_get_advice_stash_contents() like this.
SELECT * FROM pg_get_advice_stash_contents('prod');Result:
stash_name | query_id | advice_string
------------+---------------------+------------------------------------------------------------------------
prod | 7514040773558589065 | JOIN_ORDER(c o) NESTED_LOOP_PLAIN(o) INDEX_SCAN(o orders_customer_idx)
(1 row)
Here, we can see the stashed advice name, query_id, and the advice string we set earlier.
We can also easily replace the advice stored on the query_id by using the function named pg_set_stashed_advice.
SELECT pg_set_stashed_advice('prod', 7514040773558589065, 'HASH_JOIN(o)');Result:
pg_set_stashed_advice
-----------------------
(1 row)
Now, check the metadata of this stashed advice, and you can see that the advice string is replaced.
SELECT * FROM pg_get_advice_stash_contents('prod');Result:
stash_name | query_id | advice_string
------------+---------------------+---------------
prod | 7514040773558589065 | HASH_JOIN(o)
(1 row)
We can also drop the advice that was stashed by using the function named pg_drop_advice_stash() like this.
SELECT pg_drop_advice_stash('prod');Result:
pg_drop_advice_stash
----------------------
(1 row)
Now, list all the stashed advice, and you can see it is empty.
SELECT * FROM pg_get_advice_stashes();
Result:
stash_name | num_entries
------------+-------------
(0 rows)
By using this extension from postgres 19, we can improve the performance of most frequently executed application queries. Use the extension named pg_plan_advice to get advice for that query and stash that advice using the functionalities of the extension named pg_stash_advice to store and apply the advice to that query based on the query id. Understanding the purpose of these two extensions is necessary for dba and database engineers to maximise the performance of high-execution-time queries.