Before delving into the innovative features of PostgreSQL 19, it is essential to acknowledge a fundamental truth: the planner is usually right. In the course of exploring the new hints feature, I found myself needing to reduce my work_mem and experiment with numerous unanalyzed tables to elicit suboptimal query plans from PostgreSQL. The reality is that PostgreSQL excels at query planning.
The planner harnesses table statistics stored in pg_statistic, which are gathered during the ANALYZE process. These statistics encompass vital information such as row counts, distinct values, most common values, and histograms. By integrating these statistics with a sophisticated cost model that evaluates the expenses associated with various operations, the planner generates potential plans, assesses their costs, and ultimately selects the most economical option. In scenarios involving a five-table join, it may scrutinize hundreds of join configurations and strategies before arriving at the optimal choice.
This robust system operates exceptionally well. Instances of poor planner decisions are not merely viewed as irritations by PostgreSQL developers; they are regarded as genuine bugs that are typically addressed promptly. This is a key reason why the community has been hesitant to incorporate hints for an extended period. In most cases, rectifying application bugs, refreshing table statistics, or implementing a suitable index proves to be more effective solutions.
Before reaching for plan advice, try these first:
- Run ANALYZE to refresh statistics and resolve most problematic query plans.
- Utilize CREATE STATISTICS to inform the planner about your data, particularly regarding column correlations. Louise has a valuable blog post on this topic.
- Consider adding or modifying indexes.
- Examine work_mem and memory settings, as resource constraints can lead to inefficiencies in the planner’s operations.
Adding Postgres planner advice
Imagine, for the sake of illustration, that you are working with a query code that cannot be altered—perhaps due to an external application that is immutable, a foreign data wrapper, or a proprietary function. In such cases, you may find it necessary to provide planner advice.
The planner lacks visibility into PL/pgSQL functions, PostGIS operations, or custom business-logic functions, leading it to estimate that boolean functions will match approximately 33% of rows. If, however, the function actually matches only 50 out of 1 million rows, the planner’s assumptions will be significantly off, rendering traditional fixes like ANALYZE, CREATE STATISTICS, and indexing ineffective.
Consider this example: a compliance-check function identifies 50 orders out of a million, yet the planner presumes that 333,000 orders will match. Consequently, it constructs hash joins over the entire order_items table (comprising 3 million rows) and the customers table (with 100,000 rows), leading to inefficiencies that could have been avoided with appropriate planner guidance.
