Understanding PostgreSQL’s Subquery Planner: How Query Optimization Works

Optimization Overview

PostgreSQL first performs transform and rewrite on a query tree, but the resulting tree is not guaranteed to be the cheapest execution plan. The optimizer must further refine the tree by addressing sublinks, subqueries, expressions, join strategies, cost estimation, and plan materialization.

For utility statements such as DML and DDL, PostgreSQL does not apply additional optimization. For non‑utility statements, the entry point is pg_plan_queries, which invokes the planner infrastructure.

Developers can replace the default optimizer with a custom planner_hook or use the standard planner ( standard_planner).

Tuple Fraction and Its Influence

The planner sets a tuple_fraction value for cursor statements. This parameter describes the expected proportion of result tuples to be retrieved:

0 means all tuples are required.

Values in (0,1) indicate that only a fraction of qualifying tuples will be fetched.

Values ≥1 represent a fixed number of tuples, similar to a LIMIT clause.

After configuring tuple_fraction, the optimizer proceeds with the remaining steps, starting with the subquery_planner function.

Why subquery_planner Is the Primary Entry Point

Although its name suggests handling only subqueries, subquery_planner processes the entire query because subqueries share structural similarities with their parent queries. Consequently, the same optimization mechanisms apply, making it the logical entry point for full‑query optimization.

Logical Optimization Steps Inside subquery_planner

The function performs a series of transformations, each targeting a specific part of the query tree. The main steps are:

Process CTE expressions – ss_process_ctes Pull up sublinks – pull_up_sublinks Inline set‑returning functions – inline_set_returning_functions Pull up subqueries – pull_up_subqueries Flatten simple UNION ALL statements – flatten_simple_union_all Preprocess row‑level locks – preprocess_rowmarks Expand inherited tables – expand_inherited_tables Preprocess target list and expressions – preprocess_expression Handle WITH CHECK OPTION – preprocess_expression Process RETURN expressions – preprocess_expression Preprocess qualification conditions – preprocess_qual_conditions (covers WHERE, HAVING, WINDOW, LIMIT/OFF)

Merge WHERE and HAVING clauses when possible

Reduce redundant outer joins – reduce_outer_joins Generate the final query plan –

grouping_planner

These steps collectively transform the initial parse tree into an optimized execution plan that balances cost, join order, and resource usage.