plan advice

postgres has a famously good query planner. cost-based, statistics-driven, adaptive. it's been the gold standard for decades.

but "famously good" still isn't perfect. and anyone who's run postgres in production long enough knows the feeling: you stare at an explain output, see a nested loop where a hash join should be, and realize the planner's statistics are lying to it. the table grew. the distribution shifted. autovacuum hasn't caught up. and now your query that ran in 50ms is taking 12 seconds.

until pg 19, your options were ugly. you could disable entire plan types with enable_nestloop = off — a sledgehammer that affects every query in the session. you could rewrite the query to trick the planner. you could alter table ... set (autovacuum_analyze_scale_factor = ...) and pray. or you could migrate to a database that has query hints.

pg 19 changes this with two new extensions: pg_plan_advice and pg_stash_advice. this is a philosophical shift. postgres has resisted query hints for 30 years. the argument was always: fix your statistics, don't fight the planner. but the real world doesn't cooperate.

the 30-year tension

other databases have had hints forever. oracle has /*+ INDEX(t) */. mysql has /*+ HASH_JOIN(t1) */. sql server has OPTION (HASH JOIN). they're crude, they're fragile, and they work.

postgres' position was principled: hints create technical debt. you lock in a plan that made sense with last month's data distribution, and six months later it's worse than what the planner would've chosen on its own. the database should be smarter than the DBA.

the problem is that "should be" and "is" don't always align. statistics aren't perfect. correlated columns trick the planner. CTEs used to be optimization fences. partitioning adds complexity the planner doesn't always handle. postgres' principled stance was correct in theory and punishing in practice.

so pg 19 doesn't add hints. it adds something smarter.

pg_plan_advice

you don't write plans. you constrain the planner.

oracle hints say "do a hash join here." pg_plan_advice says "when you plan this query, only consider plans that use a hash join for this relation." the planner still evaluates costs, still checks semantics, still has veto power. you're narrowing its search space, not dictating the result.

first, ask postgres what advice it would generate:

explain (costs off, plan_advice)
select * from orders o join customers c on o.cust_id = c.id;

generated plan advice: join_order(o c) hash_join(c) seq_scan(o c) no_gather(o c)

that string is a recipe in a mini-language. join_order(o c) means drive from o, join to c. hash_join(c) means c appears on the inner side of a hash join. seq_scan(o c) means sequential scan both. no_gather(o c) means don't pull parallel workers into a gather node.

you can apply it wholesale:

set pg_plan_advice.advice = 'join_order(o c) hash_join(c)';

or trim it to just the part you care about. if the scan method is fine but the join order is wrong, only set join_order(o c). the planner figures out the rest.

the mini-language, in detail

scan methods — tell the planner which physical scan to use:

• seq_scan(target [...]) — sequential scan
• index_scan(target index_name [...]) — index scan using a named index
• index_only_scan(target index_name [...]) — index-only scan
• bitmap_heap_scan(target [...]) — bitmap heap scan
• do_not_scan(target [...]) — exclude the target from the plan entirely

targets are alias names from your query, with optional qualifiers for subqueries (@plan_name), repeated aliases (#occurrence_number), or specific partitions (/partition_name). if this sounds fiddly: use explain (plan_advice) to generate advice and it gives you the correct target syntax for free.

join order — this is where it gets interesting:

join_order(o c)

a plain list means outer-deep join. o is driving table, joined to c. extra relations at the end are fine.

join_order(o (c i) p)

parenthesized sublists mean: join c and i together first, then join that result to o, then join p. the parens create a join subtree.

join_order(o {c i} p)

curly braces: join c and i together first, but the order within the brace is free — either c-i or i-c. useful when you know which tables should be grouped but don't care about the inner order.

join methods:

• hash_join(target [...]) — force hash join
• merge_join(target [...]) — force merge join
• nested_loop(target [...]) — force nested loop
• foreign_join((target [...]) [...]) — push joins between foreign tables to the remote server

a single target in join method advice means "this target appears on the inner side of that join type." hash_join(c) means have c be the inner of a hash join.

parallelism:

• no_gather(target [...]) — don't gather parallel workers to a single node

what happens with bad advice

here's the safety valve. ask for something impossible — a plan the core planner thinks is semantically wrong or non-viable — and postgres doesn't error. it produces a plan with disabled: true nodes. the query still runs, just worse than if you'd said nothing.

this is deliberate. crashing on bad advice would make the feature too dangerous for production. silently degrading is the right failure mode. but it also means: always verify. explain your query after setting advice. make sure no nodes got disabled.

the planner may also reject advice on non-cost grounds — an index might be deemed useless for your query pattern, or a join order might be semantically invalid. the constraint model means the planner still reviews everything you suggest.

pg_stash_advice

pg_plan_advice is per-session. you set it, you run queries, session ends, advice gone. useful for testing. useless for a web app with a connection pool.

pg_stash_advice makes it persistent and automatic. it's a shared-memory hashmap from query_id to advice strings. when any session with an active stash plans a query, the module checks the stash. if the query's computed id matches an entry, the advice gets applied automatically.

setup:

-- once per database
create extension pg_stash_advice;
select pg_create_advice_stash('prod_fixes');

-- store advice for a specific query
select pg_set_stashed_advice('prod_fixes', -1234567890, 'hash_join(c)');

-- activate the stash
set pg_stash_advice.stash_name = 'prod_fixes';

you can set pg_stash_advice.stash_name globally in postgresql.conf, per-database with alter database, per-role with alter role, or per-session. the granularity is flexible.

to get the query_id: use explain verbose or query pg_stat_statements. the pg_plan_advice extension can generate the advice string for you.

it also persists. with pg_stash_advice.persist = on (default), a background worker writes the stash to pg_stash_advice.tsv in the data directory every 30 seconds, and reloads it on restart. your production query fixes survive a crash.

the catch

the docs are unusually direct about the risks. no marketing fluff:

• you're locking in a plan. the planner normally adapts to data distribution changes — table growth, index bloat, value skew. override it and your fix turns into the bottleneck.
• shared memory is finite. don't stash advice for every query in your application. this is for the 5 queries that actually matter.
• security is weak. any user who can set pg_stash_advice.stash_name can infer which queries have advice. the advice strings aren't encrypted. treat them as public within your database.
• there's overhead. even when an advice string matches and the plan doesn't change, there's still a lookup cost on every query plan.

the docs' advice on advice: "use this feature only when and to the extent needed." translate: don't get clever. fix the 3 queries that hurt. leave the rest alone.

the payoff

postgres gave up ground it held for 30 years. but it did it the postgres way — not by copying oracle's hint syntax, but by building a constraint system that respects the planner's intelligence while giving DBAs a controlled escape hatch.

the difference between "do this" and "only consider plans that do this" sounds academic. it's not. it means your advice can't produce semantically wrong results. it means the planner can reject your suggestion if data changes make it dangerous. it means the system degrades rather than fails.

pg 19 doesn't make postgres more like oracle. it makes it more practical without making it less principled.

the thought is mine. the words are written by janis, my hermes agent.