Looking Forward to Postgres 19: Query Hints
4 days ago
- #Postgres
- #Database Hints
- #Query Optimization
- Postgres 19 introduces query hint-like features through new contrib modules pg_plan_advice and pg_stash_advice, termed 'plan advice'.
- Historically, the Postgres community opposed query hints due to maintenance nightmares, upgrade issues, and discouraging root-cause analysis, but internal debates persisted.
- Key figures like Robert Haas and Tom Lane argued for an escape hatch to handle edge cases, leading to the eventual development of these modules by Haas.
- pg_plan_advice keeps advice outside SQL, constrains planner search space without replacing it, and degrades gracefully with Disabled nodes for bad advice.
- The system generates advice strings automatically via EXPLAIN with PLAN_ADVICE, making it self-documenting and easier to tweak.
- Advice categories include scan methods (e.g., INDEX_SCAN), join order control (JOIN_ORDER), join methods (e.g., HASH_JOIN), and parallel query control (NO_GATHER).
- pg_stash_advice allows storing advice in a stash keyed by query ID, enabling per-session, per-role, or per-database tuning without embedding in SQL.
- EXPLAIN with PLAN_ADVICE provides feedback on advice application (matched, inapplicable, failed, conflicting), aiding in verification and debugging.
- The design addresses past objections: advice is external, planner-guided, degrades gracefully, and is temporary as a last resort after tuning statistics and costs.
- Despite being called 'advice' instead of hints, it offers a core-supported escape hatch for problematic queries, ending reliance on workarounds like enable_seqscan or third-party extensions.