What's new in the Postgres 16 query planner

Original Article ↗ Hacker News Discussion ↗

PostgreSQL 16’s query planner improvements are prompting renewed scrutiny of how the database chooses and executes query plans, especially around riskier choices like nested loop joins and the impact of incomplete statistics. Contributors weigh the long‑running debate over adding query hints or plan “freezing” as escape hatches when the optimizer picks pathological plans, and explore alternatives such as better statistics, planner feedback from execution, and tooling to visualize and tune EXPLAIN plans. They also highlight practical pain points—JIT compilation overhead, lack of shared plan caching, and differences from systems like SQL Server—while generally agreeing PostgreSQL can handle large workloads but still has room to become more predictable and self-correcting.

Query Hints vs. Planner Purism

  • Big recurring debate: should Postgres support query hints?
  • Arguments for hints:
    • Critical as an “escape hatch” when the planner chooses terrible plans in production.
    • Useful for quick mitigation, validation of better plans, and consistent performance across versions.
    • Desire for out-of-band hints (per queryid, stored outlines) and even “freeze this plan” capabilities.
  • Arguments against / concerns:
    • Hints can rot as data and versions change, locking in bad plans.
    • They encourage poor DBA habits and hurt maintainability.
    • Postgres culture prefers fixing the planner itself and using richer statistics over hard hints.
  • Compromise ideas: softer hints that influence selectivity estimates or risk tolerance rather than forcing specific join types.

Statistics, Selectivity, and Plan Risk

  • Many slow plans come from bad row-count/selectivity estimates (e.g., assuming 1 row, picking nested loops, then getting many rows).
  • Discussion of extended statistics, column correlations, and current heuristics (multiplying independent probabilities).
  • Desire for:
    • More “risk-averse” planning when estimates are uncertain.
    • Ability to express data-shape knowledge (monotonic time series, expected table size).
    • Planner feedback from execution and possibly “learning” from bad plans over time.
    • Long-running / multi-pass planning for heavy OLAP queries.

JIT Compilation

  • Several users report JIT making queries dramatically slower, especially many-join or partition-heavy queries.
  • Heuristics for when to enable JIT are seen as weak; some disable JIT globally, especially for OLTP.
  • Current JIT code isn’t cached; work is mentioned to enable caching and improve cost modeling.
  • General sentiment: parallel query is reliably helpful; JIT is powerful but risky as a default.

Plan Inspection & Tooling

  • Visual tools like explain visualizers (pev2, pgMustard, etc.) are appreciated.
  • However, understanding whether a plan is “bad” and how to fix it still requires deep knowledge of joins, indexes, and statistics.
  • EXPLAIN ANALYZE is emphasized as essential; large estimate-vs-actual row mismatches are key red flags.

Postgres vs. Other Databases

  • Some claim MSSQL/Oracle have more mature optimizers, plan caching, hints, and surrounding ecosystem (jobs, reporting, messaging).
  • Others note Postgres scales well in practice; differences are more about features and tooling than raw capability.