pg_plan_advice is expected to land in PostgreSQL 19. That makes this a good moment to look at query hints — what they are, what every other major database does with them, and how PostgreSQL ended up being the obvious outlier. Three parts. This is the first.
What a hint is
A query hint is an instruction embedded in your SQL that tells the planner how to execute the query — or at least, strongly suggests it. Use this index. Join these tables in this order. Run this query in parallel, or don’t. The syntax varies by database, but the idea is the same: the planner normally picks the plan, and a hint is how you override that choice.
Hints exist because cost-based query planners are imperfect. They estimate row counts from statistics, and statistics are samples, and samples lie. The planner picks the plan with the lowest estimated cost, and the estimated cost can be wildly wrong. When it is, you either fix the underlying problem (better statistics, better indexes, rewriting the query) or you tell the planner to knock it off and just do what you said.
Every major relational database except PostgreSQL supports hints in core. We’ll get to why PostgreSQL is the exception in Part 2.
Oracle
Oracle is the spiritual home of the query hint. Oracle hints live in a /*+ */ comment immediately after the SELECT, UPDATE, INSERT, or DELETE keyword:
1 SELECT /*+ INDEX(emp emp_dept_idx) */ * 2 FROM emp WHERE deptno = 10;
The hint vocabulary is enormous. You can force an index (INDEX), forbid one (NO_INDEX), specify a join method (USE_HASH, USE_MERGE, USE_NL), fix the join order (ORDERED, LEADING), force parallelism (PARALLEL), disable it (NO_PARALLEL), control query rewrite, control materialization of subqueries, and much more. There are well over a hundred documented hints. There are undocumented ones.
Oracle shops build elaborate cultures around hints. There are conventions about which hints to use, which to avoid, how to layer them. A senior Oracle DBA can look at a query and tell you which hints are missing.
This is the world Oracle migrations come from. When someone moves from Oracle to PostgreSQL, they arrive with tens of thousands of queries containing hint comments — and the expectation that their new database will honor them.
SQL Server
SQL Server has three distinct hint categories, and they are not interchangeable.
Query hints go at the end of the statement in an OPTION clause:
1 SELECT * FROM Orders 2 WHERE CustomerID = 123 3 OPTION (RECOMPILE, MAXDOP 1);
Join hints go in the JOIN clause:
1 SELECT * FROM a INNER HASH JOIN b ON a.id = b.id;
Table hints are attached to table references via WITH:
1 SELECT * FROM Orders WITH (NOLOCK, INDEX(idx_customer));
The WITH (NOLOCK) pattern is famous, widespread, and frequently misused — it permits dirty reads, which is almost never what the person typing it thinks it means. A useful measure of SQL Server codebase health is the ratio of NOLOCK hints to correct ones.
SQL Server also has plan guides and Query Store forced plans, which attach hints to queries without modifying the SQL text. This turns out to be the more interesting direction, and it’s the one PostgreSQL is now heading toward.
MySQL
MySQL has two generations of hint syntax, because of course it does.
The older syntax uses dedicated keywords inline in the query:
1 SELECT STRAIGHT_JOIN * FROM a, b WHERE a.id = b.a_id; 2 SELECT * FROM t USE INDEX (idx_name) WHERE ...; 3 SELECT * FROM t FORCE INDEX (idx_name) WHERE ...; 4 SELECT * FROM t IGNORE INDEX (idx_name) WHERE ...;
STRAIGHT_JOIN forces left-to-right join order. USE INDEX, FORCE INDEX, and IGNORE INDEX do what you’d expect.
Since MySQL 5.7.7, there’s also an Oracle-style comment syntax:
1 SELECT /*+ BKA(t1) NO_BKA(t2) */ * FROM t1, t2 WHERE ...;
The comment-style hints cover join order, join method, index choice, subquery strategies, and resource controls. They are additive to the older keyword syntax, not a replacement. Navigating which hint goes where is its own skill.
MariaDB and Db2
MariaDB inherited MySQL’s hint system and tracks it closely, with some divergence around optimizer switches.
IBM Db2 supports optimization profiles — XML documents that attach hints to specific statements without modifying the SQL. It’s closer in spirit to SQL Server’s plan guides than to Oracle’s inline hints, and it’s aimed at the same use case: stabilizing plans in production without touching application code.
The pattern
Step back from the syntax and two patterns emerge.
Inline hints in the query text. Oracle and MySQL. You modify the SQL. Pro: explicit, visible, diffable. Con: requires changing application code, which is often exactly what you cannot do.
Out-of-band plan guidance. SQL Server plan guides, Db2 optimization profiles. You attach hints to queries by matching on query text or query hash. Pro: no application changes. Con: less visible, requires tooling to manage.
Everyone supports the first pattern. The interesting ones also support the second.
pg_plan_advice is aiming squarely at the second.