MySQL Optimizer Hints vs PostgreSQL's No-Hints Philosophy

The first culture shock for most MySQL engineers moving to PostgreSQL is not the syntax. It is discovering that the escape hatch they have relied on for years does not exist. In MySQL, when the optimizer picks a bad plan, you reach for a hint. In core PostgreSQL, there are no hints. That is not an oversight. It is a deliberate, documented, and occasionally infuriating design position.

I have operated both engines in production, and I want to be fair here: the MySQL hint system is genuinely useful, and the PostgreSQL position is genuinely defensible. The point of this article is practical. If your team has a hint-heavy MySQL codebase and a PostgreSQL migration on the calendar, you need to know what replaces that muscle memory before the first bad plan shows up in production.

Where the MySQL hint culture comes from

MySQL's hint culture is older than its modern optimizer. For years, the practical answer to a join-order mistake or a wrong index was STRAIGHT_JOIN or FORCE INDEX, and those spellings are still all over long-lived codebases. MySQL 8.x formalized this into a real system: comment-style optimizer hints written as /*+ ... */ right after the SELECT keyword, scoped to a query block, a table, or an index.

The modern toolbox is broad. JOIN_ORDER, JOIN_PREFIX, and JOIN_SUFFIX steer join sequencing. INDEX, NO_INDEX, JOIN_INDEX, GROUP_INDEX, and ORDER_INDEX steer index choice for specific purposes. BNL, NO_BNL, HASH_JOIN, and NO_HASH_JOIN influence join algorithms. SEMIJOIN, NO_SEMIJOIN, MERGE, and NO_MERGE control subquery and derived-table strategies. SET_VAR changes a session variable for one statement, and MAX_EXECUTION_TIME puts a per-query timeout inline.

SELECT /*+ JOIN_ORDER(o, c) INDEX(o idx_orders_created_at) MAX_EXECUTION_TIME(2000) */
       c.name,
       o.total_amount
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.created_at >= '2026-01-01'
  AND o.status = 'paid';

Used sparingly, this is a precision tool. The failure mode I saw repeatedly is cultural, not technical: hints written during one incident get pasted into ORM query builders, survive three schema changes and two version upgrades, and quietly pin the optimizer to a plan that stopped being right years ago. A hint is a bet that you understand the data distribution better than the optimizer, forever. Data changes. The hint does not.

Why PostgreSQL refuses to add hints

The PostgreSQL project has declined to add planner hints for decades, and the reasoning is consistent. Hints treat the symptom: this one query, this one plan, today. The project position is that a wrong plan means the planner had wrong information, and the durable fix is better information, better statistics machinery, or a better cost model, so that every query benefits instead of one. Hints also freeze plans across data growth and version upgrades, which is exactly when you want the planner free to reconsider.

You do not have to fully agree with this to work well within it. I have hit cases where I badly wanted a hint in PostgreSQL. But after years on both systems, I think the no-hints constraint pushed me toward fixes that aged better. Here is the actual playbook, in the order I use it.

First move: make the statistics tell the truth

Most bad PostgreSQL plans I have debugged were not planner bugs. They were estimation errors: the planner expected 12 rows and got 1.2 million. Before touching anything else, compare estimated and actual rows in EXPLAIN ANALYZE output. A query plan analyzer makes this comparison much faster than eyeballing nested text, but either way, find the node where the estimate first goes wrong.

If the misestimate is on a single column, the sample may be too small. ANALYZE more often, or raise the statistics target for that column so the sampled histogram and most-common-values list capture the skew.

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

If the misestimate involves multiple correlated columns, per-column statistics cannot see it, because the planner multiplies individual selectivities as if the columns were independent. That is what extended statistics are for.

CREATE STATISTICS orders_status_region (dependencies, ndistinct, mcv)
  ON status, region
  FROM orders;
ANALYZE orders;

In my experience this single feature eliminates a large share of the situations where a MySQL engineer would have reached for an index hint. The planner was not stubborn; it was misinformed, and now it is not.

Second move: rewrite the query to constrain the planner

PostgreSQL gives you sanctioned ways to shape a plan through SQL itself. The cleanest is the materialized common table expression. Since PostgreSQL 12, CTEs are inlined by default, but the MATERIALIZED keyword forces the CTE to be computed once as an optimization fence, which is effectively a declarative way to say "do this part first."

WITH recent_orders AS MATERIALIZED (
  SELECT id, customer_id, total_amount
  FROM orders
  WHERE created_at >= now() - interval '7 days'
)
SELECT c.name, sum(r.total_amount)
FROM recent_orders AS r
JOIN customers AS c ON c.id = r.customer_id
GROUP BY c.name;

Other legitimate rewrites include splitting an OR into a UNION ALL so each branch can use its own index, replacing NOT IN with NOT EXISTS so the planner can use an anti-join, and moving a filter into a LATERAL join to fix evaluation order. These read as query design rather than optimizer bribery, and they survive upgrades because they change what you asked for, not how the planner is allowed to think.

Third move: planner GUCs, tightly scoped

PostgreSQL exposes the planner's cost model as configuration. Settings like random_page_cost, effective_cache_size, and work_mem tell the planner what your hardware is like; if you run on modern SSDs with the default random_page_cost of 4, the planner will avoid index scans it should love, and lowering it toward 1.1 is standard practice. The enable_seqscan, enable_nestloop, and sibling settings can discourage whole plan node types, and join_collapse_limit constrains join reordering.

The discipline that matters: cost parameters like random_page_cost are honest global tuning, but enable_* settings are diagnostic tools, not production settings. Use them in a session to confirm a theory, then fix the underlying estimate. If you truly must ship one, scope it with SET LOCAL inside a transaction, or attach it to a single function with ALTER FUNCTION ... SET, so the blast radius is one code path instead of the whole database. The PostgreSQL query optimizer guide covers how these knobs interact with the cost model in more depth.

Last resort: pg_hint_plan exists, and that is fine

When a team absolutely needs MySQL-style control, the pg_hint_plan extension provides it: comment-based hints for scan methods, join methods, join order, and row estimates, and it is available on the major managed PostgreSQL services. I have used it twice in anger, both times as a bridge: once during a migration cutover where a plan regression appeared under load and there was no time for statistics work, and once to pin a plan while waiting for an upstream fix.

My honest advice is to treat pg_hint_plan the way you should have treated FORCE INDEX in MySQL: an incident tool with an expiry date, tracked in the same place you track TODOs, with the real fix filed. The difference in PostgreSQL is that the real fix usually exists and usually works.

After the migration: MonPG keeps the planner honest

The no-hints philosophy only feels safe when you can see plan behavior over time, because your protection against a bad plan is noticing the regression quickly, not pinning the plan forever. That is exactly the gap MonPG covers once your workload lands on PostgreSQL. MonPG is PostgreSQL-only by design: it keeps pg_stat_statements history so a query family that regresses after a deploy or an ANALYZE shows up immediately, and it keeps plan context alongside the timing so you can see whether the regression was a plan flip or a data change. Paired with the practices in this article, PostgreSQL monitoring with real query history turns "we lost our hints" from a fear into a non-event: the planner gets to adapt, and you get to watch it.