PostgreSQL query planner performs static rewrites before optimization decisions

PostgreSQL’s query planner performs two distinct phases: rewriting and optimization. The rewriting phase applies transformations that are statically knowable, such as simplifying expressions like `i+0` to `i`. These changes occur before the planner consults table statistics or other runtime data, meaning they are deterministic and independent of the actual data distribution. This separation ensures that some optimizations are always applied, regardless of the database’s state at execution time.

The rewriting phase handles operations like constant folding, predicate simplification, and other algebraic transformations. For example, the planner might rewrite a query to eliminate redundant conditions or simplify arithmetic expressions. These transformations are not tied to specific data characteristics, so they don’t require the planner to evaluate table sizes, index selectivity, or other dynamic factors. This makes the rewrites predictable but also means they may not account for real-world data patterns that could influence performance.

While PostgreSQL’s rewriting phase shares conceptual similarities with other database systems, the specific transformations and their implementation details vary. Engineers familiar with query planning in one system may find that their intuition doesn’t directly apply to PostgreSQL. For instance, a rewrite that is aggressive in one database might be conservative in PostgreSQL, or vice versa. This divergence underscores the importance of understanding PostgreSQL’s specific behavior when diagnosing query performance issues.

The static nature of rewrites means they can sometimes introduce or preserve inefficiencies. For example, a rewrite might simplify a query in a way that obscures a more optimal execution path that would have been chosen if the original query structure were preserved. Engineers need to be aware of these transformations to write queries that align with PostgreSQL’s planner behavior, particularly in cases where manual tuning or query restructuring is required.