For most of PostgreSQL’s history, the official community position on query hints has been a polite version of “no, and stop asking.”
The position isn’t subtle. The PostgreSQL wiki maintains a page titled Not Worth Doing, and “Oracle-style optimizer hints” is listed there, right above in-process embedded mode and obfuscated function source. The companion wiki page, OptimizerHintsDiscussion, states the position outright:
We are not interested in implementing hints in the exact ways they are commonly implemented on other databases. Proposals based on “because they’ve got them” will not be welcomed.
That is not the language of a debate still in progress. That is the language of a debate someone has decided is over.
Why hints are, officially, bad
The wiki lists six objections. They are worth understanding directly, because “PostgreSQL doesn’t like hints” gets caricatured into something less defensible than the actual argument:
- Maintainability. Hints embedded in application SQL require you to find and edit every query to change them. Ten thousand queries, ten thousand places to audit.
- Upgrade interference. Today’s helpful hint becomes tomorrow’s anti-performance decree. The planner improves; the hint stays.
- Enabling bad DBA habits. A hint is faster to apply than a statistics fix, a schema change, or a better-written query — so people reach for the hint instead of doing the harder work.
- Does not scale with data size. A hint that’s right for a 1-million-row table is often wrong for a 100-million-row table. The planner would adapt. The hint does not.
- Usually unnecessary. Most of the time the planner is right. Most users who complain about the planner are complaining about a statistics or configuration problem that hints would mask rather than fix.
- Interference with planner development. People who hint around a planner bug seldom report the bug. The project loses the signal.
These are not bad arguments. Whatever you think of the final conclusion, every single one of these objections is grounded in real operational experience. Anyone who has inherited a 15-year-old Oracle codebase decorated with thousands of /*+ INDEX */ comments placed by people who left the company a decade ago has lived every point on this list.
Tom Lane and the 2011 position statement
If you trace the wiki’s discussion archive back, the central document is a February 2011 message from Tom Lane titled “Re: Why we don’t want hints.” It’s short, it’s unequivocal, and it has been effectively the project’s policy ever since. The argument, boiled down: hints as they exist elsewhere are a symptom of a planner that can’t be trusted to improve, and building them into PostgreSQL would disincentivize fixing actual planner bugs. Better statistics, better estimation, better cost models — fix the planner, don’t work around it.
Lane was not, and is not, a lone voice on this. The position has had broad support across -hackers for two decades. It’s not one person’s obsession. It’s a design philosophy.
The hints we have, but don’t call hints
Except, of course, PostgreSQL does have hints. It just doesn’t use that word.
The enable_* GUCs are hints:
1 SET enable_seqscan = off; 2 SET enable_nestloop = off; 3 SET enable_hashjoin = off;
There are more than twenty of them, and setting any one to off coerces the planner away from a particular method. They’re session-scoped rather than query-scoped, but the mechanism is identical in spirit to what Oracle’s NO_INDEX does. The documentation cheerfully describes them as “for debugging and testing” while everyone knows perfectly well that people use them in production.
The planner cost GUCs — random_page_cost, seq_page_cost, cpu_tuple_cost, effective_cache_size — are also hints in the loose sense. They’re knobs that change the planner’s opinions about the world.
None of this is inline, per-query hints embedded in application SQL, which is the specific thing the community objected to. But the claim that PostgreSQL “has no hints” has always been pedantic. What PostgreSQL has avoided is inline hints. Hints in the general sense have always been there.
pg_hint_plan: the unofficial answer
While the core project held the line, NTT Open Source Software Center built pg_hint_plan and released it as an extension. It is not in core, and that distinction has mattered enormously to both its supporters and its critics.
pg_hint_plan implements inline hints in Oracle-style comments:
1 /*+ SeqScan(a) HashJoin(a b) */ 2 SELECT * FROM a JOIN b ON a.id = b.a_id;
It supports scan methods, join methods, join order, Set for inline GUC overrides, rows-estimation overrides, and parallel-query controls. It is shipped by Fujitsu Enterprise Postgres as a bundled feature. It is recommended by migration vendors as the standard answer to “how do we port Oracle hints to PostgreSQL.” It is mature, widely deployed, and works.
The existence of pg_hint_plan is what kept the official “no hints” position tenable. The core project could say “we don’t want hints in core” without having to say “we don’t want hints at all,” because an extension existed to serve the people who needed them. That was a sustainable equilibrium for about a decade.
What changed
Several things, over time, eroded the purity of the original position.
Extended statistics landed in PostgreSQL 10. That addressed the column-correlation problem, which had been one of the most common reasons people wanted hints in the first place.
pg_stat_statements became ubiquitous, which changed what “telling the planner what to do” looks like in practice: you can now identify the specific queries whose plans are unstable, which makes the targeted use case for hints far more tractable than the general one.
And the argument shifted. The debate stopped being “should we let users override the planner” and started being “should we let users stabilize plans across upgrades.” Plan stability — the Db2 and SQL Server model — is a narrower claim than Oracle-style hinting. It sidesteps most of the six objections. Plans that were good before an upgrade should stay good after it. That is not the same as “users know better than the planner.”
Which brings us to Robert Haas’s pg_plan_advice patch set, now proposed for PostgreSQL 19. It is not pg_hint_plan brought into core. It is a different thing, informed by twenty years of argument, and it is careful about which of the six objections it answers and which it declines to.