PostgreSQL 19 Introduces pg_plan_advice Module for Query Plan Control
Key Takeaways
- ▸PostgreSQL 19 adds pg_plan_advice contrib module enabling users to stabilize or override query planner decisions through textual plan advice
- ▸Plan advice can be generated from finished plans, manually edited by users, or written from scratch to control specific planner outcomes like scan type selection
- ▸The feature addresses a decades-long community request for query hints while maintaining limitations to prevent abuse, though users must exercise caution to avoid suboptimal query performance
Summary
PostgreSQL 19 introduces the pg_plan_advice contrib module, a new facility that allows database administrators and knowledgeable users to stabilize or override query planner decisions. Committed by Robert Haas on March 12, 2026, this module addresses a long-standing request from the PostgreSQL community for "hints" — a mechanism to influence how the query planner selects execution strategies.
The pg_plan_advice module works by analyzing completed query plans and generating textual advice that describes key planner decisions. When this advice is provided during future planning cycles, it forces the planner to make identical decisions. Users can also manually edit or create plan advice from scratch, enabling query optimization strategies that the planner would not naturally produce. The module currently focuses on specific outcomes like choice of sequential vs. index scans for particular relations, though it does not yet cover broader cost estimation or aggregation decisions.
The feature represents a significant shift in PostgreSQL's philosophy, as the development community historically resisted implementing hint systems. However, reviewers acknowledge that while the new capability could be misused to create suboptimal query plans, it provides valuable tools for solving complex performance problems that are otherwise difficult to address. The module has been extensively reviewed by experienced PostgreSQL contributors including Lukas Fittl, Jakub Wartak, and others.
- Currently scoped to specific planner outcomes with selectivity estimates and costing decisions deliberately excluded from this initial implementation
Editorial Opinion
The pg_plan_advice module represents a pragmatic evolution in PostgreSQL's approach to query optimization, balancing the community's historical skepticism about hints with the practical reality that real-world databases often require manual intervention due to imperfect statistics and edge cases. While the feature undoubtedly carries risks—as powerful tools often do—its careful design with explicit scope limitations and the requirement for deliberate user action suggests the PostgreSQL team has learned from decades of hint-system debates. This addition will likely prove invaluable for experienced database professionals managing complex workloads, though novice users should indeed heed the warnings about thinking "twice and perhaps three times" before constraining the planner.
