Oracle Hints vs PostgreSQL: How to Influence the Planner

Your Oracle Hints Don't Exist in PostgreSQL — On Purpose

Oracle developers reach for hints — /*+ INDEX(t idx) */, /*+ LEADING */, /*+ USE_NL */ — to override the optimizer when it picks a bad plan. The first surprise in PostgreSQL: there are no query hints. Not hidden, not different syntax — they don't exist, by deliberate design. PostgreSQL's philosophy is that a hint is a patch over a planner that was given bad information, so you fix the information, not the plan.

What Oracle Hints Map To

First Lever: Give the Planner Better Statistics

Most "the planner is wrong" cases are really "the planner's estimate is wrong." ANALYZE refreshes stats; raising the statistics target sharpens them on skewed columns; and CREATE STATISTICS teaches the planner about correlations between columns it otherwise assumes are independent (the classic cause of a 1000× row mis-estimate).

-- Sharper histograms on a skewed column
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;

-- Teach the planner that city and state are correlated (not independent)
CREATE STATISTICS orders_city_state (dependencies) ON city, state FROM orders;
ANALYZE orders;

Second Lever: Session Flags (for Diagnosis, Not Production)

PostgreSQL exposes enable_seqscan, enable_nestloop, etc. These are blunt instruments — they don't target one query, they reshape all planning for the session. They're invaluable to diagnose ("force the index and see if it's actually faster"), but leaving them set in production is the anti-pattern. If the forced plan is faster, that's a signal to fix stats or add an index — not to ship the flag.

-- Diagnose: would the index plan be faster?
SET enable_seqscan = off;
EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;   -- compare
RESET enable_seqscan;                      -- always reset

When You Genuinely Need a Hint: pg_hint_plan

For the rare query that resists every proper fix, the pg_hint_plan extension adds Oracle-style hints back to PostgreSQL via a comment syntax. It's a real escape hatch used in production by large shops — but treat it as the last resort it's meant to be. Reach for stats, indexes, and query rewrites first; they fix the cause, the hint freezes a symptom.