Understanding how PostgreSQL processes queries is essential for optimization and OSS contribution. This module traces the complete journey from SQL text to result set.
Estimated Time: 12-14 hours Difficulty: Advanced OSS Relevance: High — core source code areas Interview Value: Senior/Staff level deep dives
-- QuerySELECT u.name FROM users u WHERE u.id = 5;-- Analyzer resolves:-- • "users" → pg_class OID 16384-- • "u" → alias for users-- • "name" → column attnum 2 of users-- • "id" → column attnum 1 of users
Copy
-- Analyzer checks type compatibilitySELECT * FROM users WHERE name = 123;-- Error: operator does not exist: text = integer-- Or applies implicit castsSELECT * FROM users WHERE id = '5';-- Implicitly casts '5' to integer
/* The analyzed query representation */typedef struct Query { NodeTag type; CmdType commandType; /* SELECT, INSERT, UPDATE, DELETE */ List *rtable; /* Range table (list of RangeTblEntry) */ FromExpr *jointree; /* JOIN tree */ List *targetList; /* Target list (list of TargetEntry) */ Node *quals; /* WHERE clause */ List *groupClause; /* GROUP BY */ Node *havingClause; /* HAVING */ List *sortClause; /* ORDER BY */ /* ... many more fields */} Query;
-- Create a viewCREATE VIEW active_users AS SELECT * FROM users WHERE status = 'active';-- Query the viewSELECT name FROM active_users WHERE age > 21;-- Rewriter expands to:SELECT name FROM users WHERE status = 'active' AND age > 21;
-- Views are implemented as SELECT rules-- pg_rewrite stores:CREATE RULE "_RETURN" AS ON SELECT TO active_users DO INSTEAD SELECT * FROM users WHERE status = 'active';-- INSTEAD OF rules for updatable viewsCREATE RULE update_active AS ON UPDATE TO active_users DO INSTEAD UPDATE users SET name = NEW.name WHERE id = OLD.id;
Modern databases primarily use Cost-Based Optimization (CBO), but understanding Rule-Based Optimization (RBO) is essential for legacy systems and specific query rewrites.
Feature
Rule-Based (RBO)
Cost-Based (CBO)
Logic
Fixed hierarchy of rules (e.g., “Always use Index if available”)
Evaluates multiple paths based on statistics
Data Awareness
No knowledge of data distribution or size
Deeply aware of row counts, histograms, and I/O costs
Flexibility
Predictable but often suboptimal
Adaptive and finds complex optimizations
Performance
Fast planning, potentially slow execution
Slower planning (search space), faster execution
PostgreSQL is a pure CBO engine. It uses statistics (from ANALYZE) to calculate the “cost” of various execution strategies.
Subquery Flattening: Converting IN (SELECT ...) to semi-joins or inner joins when possible.
Predicate Pushdown: Moving filters as deep as possible into the plan tree.
Path Generation & Join Ordering:
The planner generates Paths (access methods like SeqScan, IndexScan, BitmapScan).
It uses Dynamic Programming to find the cheapest join order for up to geqo_threshold tables (default 12).
Genetic Query Optimizer (GEQO): For complex queries with many joins, PostgreSQL switches to a heuristic (genetic) algorithm to prevent the search space from exploding factorially (N!).
Costing & Selection:
Every path is assigned a cost based on:
seq_page_cost: Cost of a sequential page read.
random_page_cost: Cost of a random page read (critical for HDD vs SSD tuning).
/* Path = possible access strategy with cost */typedef struct Path { NodeTag type; RelOptInfo *parent; /* The relation this path is for */ Cost startup_cost; /* Cost before first tuple */ Cost total_cost; /* Total cost */ double rows; /* Estimated rows */ /* ... */} Path;/* Plan = actual execution instructions */typedef struct Plan { NodeTag type; Cost startup_cost; Cost total_cost; double plan_rows; int plan_width; /* Avg tuple width in bytes */ List *targetlist; /* Target list */ List *qual; /* Qual conditions */ struct Plan *lefttree; /* Left subtree */ struct Plan *righttree; /* Right subtree */ /* ... */} Plan;
PostgreSQL uses iterator (volcano) model — each node is an iterator.
Copy
/* Every plan node implements three functions */typedef struct PlanState { NodeTag type; Plan *plan; /* Associated Plan node */ /* Methods */ ExecInitNode /* Initialize node state */ ExecProcNode /* Get next tuple (iterator) */ ExecEndNode /* Clean up */ /* State */ TupleTableSlot *ps_ResultTupleSlot; /* ... */} PlanState;
-- See raw parse tree (requires debug build)-- Or use pg_stat_statements for query fingerprinting-- Extension for query tree visualizationCREATE EXTENSION pg_query;SELECT pg_query_tree('SELECT * FROM users WHERE id = 1');
