Postgres 19 Adds Query Hints via pg_plan_advice and pg_stash_advice

The End of an Era

After years of resistance, PostgreSQL 19 is shipping query hints. The feature freeze includes two new contrib modules: pg_plan_advice and pg_stash_advice. This is a monumental shift for a community that had a wiki page titled "Query Hints: Why We Don't Want Them."

A Brief History of 'Never'

The official stance was firm: "We are not interested in implementing hints in the exact ways they are commonly implemented on other databases." The wiki listed six solid reasons:

• Maintenance nightmares
• Break on upgrades
• Discourage root-cause analysis
• Poor scalability
• Optimizer is usually smarter
• Impede planner improvements

But the debate never died. In late 2010, a legendary thread on pgsql-performance captured every sentiment. Robert Haas argued: "I think it's just dumb to say we don't want hints. We want hints, or at least many of us do." He wanted an escape hatch for the 0.1% of queries that can't be fixed by existing methods.

Tom Lane admitted: "I haven't seen a hinting scheme that didn't suck... I don't say that there can't be one." Kevin Grittner pointed out the paradox: toggling enable_seqscan off is effectively a hint, just undocumented.

Josh Berkus drew the line at embedding hints in SQL comments, but wasn't against all planner overrides. The third-party pg_hint_plan extension filled the gap for years, but now it's official.

How pg_plan_advice Works

Robert Haas, the author of both modules, addressed every historical objection. Advice lives outside the SQL, set via a GUC or stored in a stash. It constrains the planner's search space rather than replacing it, and can only produce plans the core planner considers viable. Bad advice degrades gracefully — the planner marks affected nodes as Disabled and falls back to its best plan.

Best of all, the system generates its own advice. Just add PLAN_ADVICE to EXPLAIN:

CREATE TABLE my_fact (
  id      BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  dim_id  BIGINT NOT NULL
);
CREATE TABLE my_dim (
  id     BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  stuff  TEXT
);
CREATE INDEX idx_fact_dim_id ON my_fact (dim_id);

EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT * FROM my_fact f
JOIN my_dim d ON f.dim_id = d.id;

Output:

Hash Join
  Hash Cond: (f.dim_id = d.id)
  ->  Seq Scan on my_fact f
  ->  Hash
        ->  Seq Scan on my_dim d
Generated Plan Advice:
JOIN_ORDER(f d)
HASH_JOIN(d)
SEQ_SCAN(f d)
NO_GATHER(f d)

The planner hands over the advice string that reproduces its current plan. You can then modify it.

Advice Language

The advice language covers scan methods, join order, join methods, and parallel query control.

Scan methods: INDEX_SCAN(f idx_fact_dim_id) forces an index scan. DO_NOT_SCAN excludes a table entirely.

Join order uses parentheses for strict ordering and braces for flexible grouping:

-- Strict: join b to c first, then a to that result, then d
SET pg_plan_advice.advice = 'JOIN_ORDER(a (b c) d)';
-- Flexible: b and c in any order between a and d
SET pg_plan_advice.advice = 'JOIN_ORDER(a {b c} d)';

Join methods: HASH_JOIN(d) builds the hash table from d. NESTED_LOOP_PLAIN(f) forces a plain nested loop. For multiple relations on the inner side, wrap in parentheses:

-- d1 and d2 each on inner side of separate hash joins
SET pg_plan_advice.advice = 'HASH_JOIN(d1 d2)';
-- d1 and d2 together on inner side of one hash join
SET pg_plan_advice.advice = 'HASH_JOIN((d1 d2))';

Parallel control: GATHER, GATHER_MERGE, NO_GATHER.

Combine them:

SET pg_plan_advice.advice =
'JOIN_ORDER(f d1 d2) HASH_JOIN(d1 d2) SEQ_SCAN(f) INDEX_SCAN(d1 idx_d1_pk)';

Stashing Advice for Production

pg_stash_advice stores advice persistently, keyed by query ID. First install the extension:

CREATE EXTENSION pg_stash_advice;

Create a stash, get the query ID, and set advice:

SELECT pg_create_advice_stash('production_tuning');

EXPLAIN (VERBOSE, PLAN_ADVICE)
SELECT * FROM my_fact f
JOIN my_dim d ON f.dim_id = d.id;

SELECT pg_set_stashed_advice(
  'production_tuning',
  5424487836266966148,
  'INDEX_SCAN(f idx_fact_dim_id) NESTED_LOOP_PLAIN(f)'
);

SET pg_stash_advice.stash_name = 'production_tuning';

Now every time the planner sees that query pattern, it applies the advice. Scope the stash per-session, per-role, or per-database:

ALTER DATABASE mydb SET pg_stash_advice.stash_name = 'production_tuning';
ALTER ROLE reporting_user SET pg_stash_advice.stash_name = 'reporting_tuning';

Stashes persist to disk by default (pg_stash_advice.persist = on) with a configurable write interval. They survive restarts, and DBAs can inspect, update, or drop entries without touching application code.

What This Means for DBAs

This is the escape hatch Robert Haas wanted. When a problematic query can't be fixed by updating statistics or rewriting SQL, you can redirect the planner from the database side. When the root cause is fixed, remove the advice. The application never changes.

The system addresses every historical objection: advice lives outside SQL, constrains rather than replaces the planner, degrades gracefully, and can be generated automatically. It's not Oracle-style hints — it's plan advice. And it's finally here.