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

Original Article ↗ Hacker News Discussion ↗

PostgreSQL’s pg_hint_plan extension, which lets users nudge or effectively force specific query plans, is drawing interest from engineers who struggle with unpredictable planner choices in large or critical workloads. Supporters see hints as an essential “last resort” tool for stabilizing performance during production incidents or working around long‑standing optimizer limitations, while critics warn that hard‑coding plans can age badly as data and PostgreSQL versions evolve. The exchange also surfaces deeper issues in PostgreSQL’s statistics and planning model—especially around LIMIT and ORDER BY queries—and highlights how extensions, composite/partial indexes, and planner settings are used in practice to regain control.

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.