Sep 30, 2026/2 min readCore Platform
Query Plan Hints in PostgreSQL 19: A New Postgres Feature You Will Probably Never Need
Query hints: You asked for it, Postgres 19 gave it to you. Now, they are just waiting to see how you use it.
For years the Postgres community was against adding support for hints. They felt if you had correctly analyzed tables, the Postgres query planner would make the right choice. And they're not wrong, for the most part. Yet many DBAs and developers will tell you of a time when a plan suddenly changed or something was saved by a hint. We can debate the good and bad elements of hints, but the cool thing is that you will soon have a choice in Postgres 19+.
So what are query hints? I came from the SQL Server world like many folks in the Postgres world and they were just there. Oracle, too. They are explicit instructions you give with SQL to change the default execution plan. Note: While industry standard refers to these as "hints," Postgres 19 uses the term "advice," so use both when searching.
This feature, expected later this fall, is coming to Postgres 19 via two new contrib modules (extensions packaged with Postgres that have to be explicitly turned on, like pg_stat_statements, everyone's favorite extension).
- pg_plan_advice - which lets you set query plan advice, like "join order" and "scan type." This also adds a new EXPLAIN option to get the plan advice for any query.
- pg_stash_advice - this lets you save plan advice strings per query id, so if you run the same query over and over (or a parameterized query), it will default to your advice instead of the planner's.
Here's a quick look at what it looks like to turn on plan advice and use it:
-- Load extension
LOAD 'pg_plan_advice';
-- Set specific join order, scan type, and index using table alias'
SET pg_plan_advice.advice = 'JOIN_ORDER(c o) NESTED_LOOP_MEMOIZE(o) INDEX_SCAN(o idx_orders_customer)';
-- Run any query, the plan advice will be followed
SELECT c.name, count(*) FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.tier = 'VIP'
GROUP BY c.name;
-- Reset to let the planner decide again:
RESET pg_plan_advice.advice;You will probably never need hints
Before we get into PostgreSQL 19's new features, a word of caution: the planner is usually right. Seriously, to write this blog and test the new hints feature, I had to lower my work_mem and test a lot of unanalyzed tables to even get bad query plans from Postgres. Postgres is VERY, VERY good at query planning.
The planner uses table statistics in pg_statistic collected as part of the ANALYZE process to get information like row count, distinct values, most common values and histograms.
The planner combines these statistics with a cost model that looks at the expenses of different operations. It generates candidate plans, costs them, and picks the cheapest one. For a five-table join, it might evaluate hundreds of join orderings and strategies before settling on the winner.
This system works remarkably well. Poor planner choices are not just considered an annoyance by Postgres developers; they are considered genuine bugs and are often quickly fixed. That's why the community has resisted adding hints for so long. In most cases, fixing application bugs, updating table statistics or adding an appropriate index is a better solution.
Before reaching for plan advice, try these first:
- Run ANALYZE, this will refresh statistics and fix most bad query plans
- CREATE STATISTICS to tell the planner about your data, especially column correlations. Louise has a great blog post on this.
- Add or adjust indexes
- Check work_mem and memory settings: resource constraints can force different inefficiencies on the planner
Adding Postgres planner advice
Let's say for the sake of demonstration here, you have some kind of query code you cannot modify. An external app that can't be changed, a foreign data wrapper, or maybe some kind of opaque function, you may need to add planner advice.
The planner can't see inside PL/pgSQL functions, PostGIS operations, or custom business-logic functions so it guesses that boolean functions match 33% of rows. If the function actually matches 50 out of 1 million rows, the plan will be way off and the go-to fixes like ANALYZE, CREATE STATISTICS and indexes can't help.
Here's an example. A compliance-check function flags 50 orders out of 1 million, but the planner assumes 333K match and builds hash joins over the full order_items (3 million rows) and customers (100,000 rows) tables:
CREATE FUNCTION is_flagged_order(oid bigint) RETURNS boolean
LANGUAGE plpgsql STABLE AS $$
BEGIN
RETURN oid <= 50; -- only 50 out of 1M match
END;
$$;
EXPLAIN (ANALYZE, COSTS OFF, PLAN_ADVICE)
SELECT c.name, c.region, o.order_date, o.amount, oi.product, oi.qty
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
WHERE is_flagged_order(o.id);
QUERY PLAN
---------------------------------------------------------------------------------------------
Hash Join (actual time=428..1063 rows=150 loops=1)
Hash Cond: (o.customer_id = c.id)
-> Hash Join (actual time=409..1043 rows=150 loops=1)
Hash Cond: (oi.order_id = o.id)
-> Seq Scan on order_items oi (actual time=0..148 rows=3000000 loops=1)
-> Hash (actual time=408..408 rows=50 loops=1)
-> Seq Scan on orders o (actual time=378..407 rows=50 loops=1)
Filter: is_flagged_order(id)
Rows Removed by Filter: 999950
-> Hash (actual time=19..19 rows=100000 loops=1)
-> Seq Scan on customers c (actual time=0..6 rows=100000 loops=1)
Generated Plan Advice:
JOIN_ORDER(oi o c)
HASH_JOIN(o c)
SEQ_SCAN(oi o c)
NO_GATHER(o c oi)
Execution Time: 1063 ms
With plan advice, we can switch to nested loops that do 50 targeted index lookups instead of full table scans. This does end up running ~2x faster.
SET pg_plan_advice.advice =
'JOIN_ORDER(o c oi) NESTED_LOOP_PLAIN(c oi) SEQ_SCAN(o) INDEX_SCAN(c customers_pkey oi idx_items_order)';
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Nested Loop (actual time=355..387 rows=150 loops=1)
-> Nested Loop (actual time=355..387 rows=50 loops=1)
-> Seq Scan on orders o (actual time=355..387 rows=50 loops=1)
Filter: is_flagged_order(id)
Rows Removed by Filter: 999950
-> Index Scan using customers_pkey on customers c (actual rows=1 loops=50)
Index Cond: (id = o.customer_id)
-> Index Scan using idx_items_order on order_items oi (actual rows=3 loops=50)
Index Cond: (order_id = o.id)
Supplied Plan Advice:
SEQ_SCAN(o) /* matched */
INDEX_SCAN(c customers_pkey) /* matched */
INDEX_SCAN(oi idx_items_order) /* matched */
JOIN_ORDER(o c oi) /* matched */
NESTED_LOOP_PLAIN(c) /* matched */
NESTED_LOOP_PLAIN(oi) /* matched */
Execution Time: 387 msAdvice tags
Hints allow you to feed data about a different query plan areas, with its own special language.
Scan method
- Tags: SEQ_SCAN, INDEX_SCAN, INDEX_ONLY_SCAN, BITMAP_SCAN, DO_NOT_SCAN
- Example: INDEX_SCAN(o idx_orders_customer)
Join order
- Tags: JOIN_ORDER
- Example: JOIN_ORDER(c o oi)
Join method
- Tags: HASH_JOIN, MERGE_JOIN, NESTED_LOOP_PLAIN, NESTED_LOOP_MEMOIZE, NESTED_LOOP_MATERIALIZE
- Example: HASH_JOIN(o)
Parallel
- Tags: GATHER, GATHER_MERGE, NO_GATHER
- Example: NO_GATHER(c o)
Getting advice strings from any EXPLAIN plan
A handy feature of this new feature is that Postgres EXPLAIN will generate advice strings. In my opinion, this is actually the best part of this new feature. EXPLAIN plans are a pain to read and I love this little plan summary we get now with EXPLAIN (PLAN_ADVICE).
EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT c.name, c.tier, count(*) AS order_count, sum(o.amount) AS total
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.tier = 'VIP'
GROUP BY c.name, c.tier;
QUERY PLAN
--------------------------------------------------------------------
Finalize GroupAggregate
Group Key: c.name
-> Gather Merge
Workers Planned: 2
-> Sort
Sort Key: c.name
-> Partial HashAggregate
Group Key: c.name
-> Hash Join
Hash Cond: (o.customer_id = c.id)
-> Parallel Seq Scan on orders o
-> Hash
-> Seq Scan on customers c
Filter: (tier = 'VIP'::text)
Generated Plan Advice:
JOIN_ORDER(o c)
HASH_JOIN(c)
SEQ_SCAN(o c)
GATHER_MERGE((c o))
That Generated Plan Advice string is also copy-pasteable. Alter it and feed it back to lock the plan:
SET pg_plan_advice.advice = 'JOIN_ORDER(o c) HASH_JOIN(c) SEQ_SCAN(o c) GATHER_MERGE((c o))';
What happens when advice is broken? Instead of the query failing completely, pg_plan_advice degrades gracefully and gives detailed feedback in the logs if you have that set up (SET pg_plan_advice.trace_mask=true;).
What happens if the advice is bad? Nothing. You overrode Postgres' planner with advice. So be careful and run EXPLAIN often and check on plans to make sure you're not making things worse.
pg_stash_advice: Saving hints and advice
Now that we know how to get plan advice and test it, let's look at saving it for future use.
- SET pg_plan_advice.advice this is how you set plans for a single session
- RESET pg_plan_advice.advice ;This is how you unset it
- SELECT pg_create_advice_stash('production_fixes'); This is how you create a stash of advice, and you can have many advice sets in here
- SET pg_stash_advice.stash_name = 'query_fixes'; This is how you call an advice set in a db, session, or with a specific query.
Advice can be scoped at different levels, so you can target advice precisely:
- Per-database: ALTER DATABASE mydb SET pg_stash_advice.stash_name = 'production_fixes'; Don't do this. It would apply very generally to a lot of queries.
- Per-role: ALTER ROLE reporting_user SET pg_stash_advice.stash_name = 'reporting_tuning'; Again, maybe good for one role running one specific query
- By query ID: SELECT pg_set_stashed_advice('my_stash', <query_id>, '...');
I suspect that by query ID is the only real option for production — that way you limit the scope to just things you know are broken and don't risk altering queries you haven't run across yet.
Query IDs in Postgres have the same ID if they have the same shape, same tables, same joins and same clauses. Literal values don't matter, so WHERE id = 1 and WHERE id = 99999 produce the same query ID. Parameterized queries have the same query ID. This is the same identifier that pg_stat_statements uses to group query statistics, so if you're already using pg_stat_statements to find slow queries, you can take that query ID and stash advice for it directly.
Here's a sample of how to stash advice for a specific query. You need a named piece of advice and then you set advice with the named advice string and the query ID.
CREATE EXTENSION pg_stash_advice;
-- Create a named collection of advice
SELECT pg_create_advice_stash('production_fixes');
-- Get the query ID from EXPLAIN VERBOSE
EXPLAIN (VERBOSE, COSTS OFF, PLAN_ADVICE)
SELECT c.name, c.tier, count(*) AS order_count, sum(o.amount) AS total
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.tier = 'VIP'
GROUP BY c.name, c.tier;
-- Output includes: Query Identifier: 9122549731181782750
-- Stash advice that forces nested loop (different from default hash join)
SELECT pg_set_stashed_advice(
'production_fixes',
9122549731181782750,
'JOIN_ORDER(c o) NESTED_LOOP_MEMOIZE(o) INDEX_SCAN(o idx_orders_customer) NO_GATHER(c o)'
);Conclusion
Postgres has hints now, and you can feed it query advice. But before you get too excited — you probably don't need this. In fact, I suspect adding this could do more harm than good in the vast majority of cases. The Postgres planner is pretty amazing.
Before reaching for hints, make sure you have run ANALYZE and tuned queries and indexes, which are much more likely to produce better results. If you do use the query advice, scope it to query ID only, and be diligent about checking EXPLAIN plans to make sure you're making things better.
But I think there's something to be said for features that Postgres releases like this. We have it and we're not completely sure how it's going to be used. Let's see where this takes us. Postgres will always improve on additional features beyond this if they're useful to the community.
