Pg_hint_plan: Force PostgreSQL to execute query plans the way you want

What pg_hint_plan Provides

• Extension lets users bias PostgreSQL’s planner toward specific plans (joins, indexes, etc.) without changing core PostgreSQL.
• Seen as especially valuable for:
  • Emergency production incidents when planner chooses a disastrous plan.
  • Research/experimentation where you want to fix some plan choices while letting the planner handle the rest.
  • Migrating from other databases (e.g., via Babelfish) where existing workloads rely on hints.

Practical Issues Using It

• Hint table interface is described as fragile:
  • Matching is whitespace-sensitive and can fail silently.
  • Parameter handling is awkward; earlier tooling made iterating on parameterized queries hard, though newer psql versions help.
• Extension changes plan costs to “strongly suggest” a plan; if a hinted plan is impossible, PostgreSQL still picks something else.

Debate: Are Hints Good or Bad?

• Concerns:
  • Hints can freeze suboptimal plans as data and PostgreSQL versions evolve.
  • Developers rarely revisit hinted queries; cruft accumulates.
  • Core dev stance (as described): better to improve statistics and optimizer, not add hints to SQL.
• Counterpoints:
  • Predictability in production is often valued more than theoretical optimality; avoiding sudden plan regressions is critical.
  • Statistics and planner configuration are complex, global, and risky; hints are localized and easier to reason about for a single query.
  • PostgreSQL’s statistics and optimizer have known weaknesses at large scale and with correlated or skewed data; some issues have remained unresolved for years.
  • Hints are framed as “inline assembly”: bad as a default, invaluable for rare edge cases.

Alternatives and Tuning Approaches

• Suggested non-hint remedies:
  • VACUUM ANALYZE, tuned autovacuum.
  • Raising per-column statistics targets or using extended/custom statistics.
  • Planner config tweaks (join_collapse_limit, cost parameters) scoped with SET LOCAL.
  • Schema/index changes (composite or partial indexes) to better support frequent queries.

Planner Pain Points and Examples

• Multiple reports of LIMIT + ORDER BY + WHERE queries picking very poor plans, especially with correlated or skewed columns.
• Disagreement on how often catastrophic plan flips occur, but consensus that they do happen, particularly on very large tables.
• Some argue this makes PostgreSQL “dangerous” in production without a reliable way to pin or override plans.