A query planner, or optimizer, evaluates possible execution plans for a SQL statement, estimates their costs from internal logic, statistics, and configuration, and chooses the lowest-cost plan. The written order of query components does not necessarily determine execution order. section 6.8 section 2.3
When readable SQL produces a better plan
A customer-email query for unpaid service invoices initially combines joins, filters, and subqueries. Rewriting it with CTEs for unpaid invoices and service orders makes the query and resulting plan simpler; PostgreSQL can inline those CTEs and reorder operations to apply useful filters earlier. The revised query has faster planning and execution times. section 2.3
Listing 2.3 Non-CTE query example
SELECT DISTINCT email
FROM erp.customer_contact_details ccd
JOIN erp.invoices i ON i.customer = ccd.id
JOIN erp.order_groups og ON i.order_group = og.id
JOIN erp.sent_emails se ON se.invoice = i.id
JOIN erp.orders o ON o.order_group = og.id
WHERE ccd.id IN (
SELECT customer
FROM erp.invoices
WHERE paid = false
)
AND i.order_group IN (
SELECT order_group
FROM erp.invoices
WHERE paid = false
)
AND se.email_type = 'Invoice reminder'
AND o.item IS NULL;
Inspect the original non-CTE SQL whose joins, filters, and subqueries create a more complicated planning problem. section 2.3
Use PostgreSQL’s diagnostic tools to understand the planner’s decision:
- Run EXPLAIN to inspect the chosen execution plan without executing the statement. section 6.8
- Run EXPLAIN ANALYZE to compare actual component timings with the selected plan, but remember that it executes the SQL. section 6.8
- Keep autovacuum and autoanalyze effective so the planner receives current statistics; use auto_explain cautiously because widespread plan logging can affect performance. section 6.8
