PostgreSQL SQL Processing
In this article, we look at how PostgreSQL processes SQL.
When the postgres process receives SQL (a query string) from a client, it processes the SQL through the 5 stages below and returns the result to the client. Each stage performs work such as syntax checking, semantic analysis, and optimization. Let's look at each in detail.
Parser
Parsing is the first stage of SQL processing. It analyzes the query syntax, checks for syntax errors, and creates a parse tree. Because this stage does not reference the system catalog, it cannot perform semantic analysis of individual elements and only does a simple syntax check.
📌 The system catalog stores metadata such as tables, rows, and schemas. Other RDBMSs call it the data dictionary.
Traffic Cop (Analyzer)
This stage analyzes the meaning of the parse tree created by the parser and creates a query tree. It performs semantic analysis to understand the tables, functions, and operators the SQL references. Simple queries that need no query rewrite or optimization go straight to the executor stage.
- Simple Query : control statements such as create, drop, alter, and vacuum
- Complex Query : all other queries, such as select and join
Rewriter
This stage applies predefined rules to the query tree created in the Traffic Cop stage to simplify the query. Both input and output are query trees.
Query Rewrite Techniques
- Subquery Collapse
- Merges a subquery into the main query. (Subquery Unnest)
- View Merging
- Unfolds a view or inline view into joins between tables.
- When view merging is possible, various join methods and join orders between tables can be chosen.
- Simple views always succeed in view merging, but complex views always fail.
- JPPD (Join Predicate Push-Down)
- When view merging fails, this method pushes the join predicate down into the view.
- The join predicate can be pushed down only if it is a constant condition.
- A lateral view must be used.
📌 Simple View vs. Complex View
A view that does not use aggregation such as GROUP BY or DISTINCT is called a simple view; otherwise it is a complex view.
Planner (Optimizer)
This is the stage that creates the optimal execution plan. It builds every possible plan path, calculates the cost of each path, and selects the plan with the lowest cost. The key decisions here are the scan and join methods.
- Path units include SCAN, JOIN, GROUP BY, SORTING, and AGGREGATION.
- Cost is based on the statistics stored in the system catalog. (pg_class, pg_statistics)
key Decisions
Scan Method
- Sequential Scan
- Reads records with a full scan of the table.
- Chosen when no index exists or none can be used.
- Bitmap Index Scan
- Designed to reduce the number of random table accesses.
- The ordering of table records relative to the index column is called
Correlation. IfCorrelationis good, Index Scan is chosen; if it is poor, Bitmap Index Scan is chosen. - Bitmap Index Scan sorts blocks by block number before accessing them. So rows are not returned in index key order. (Ordering is not guaranteed.)
- Index Scan
- Reads records using the keys stored in index leaf blocks.
- The number of block accesses varies greatly depending on how records are ordered.
- Rows are returned in index key order. (Ordering is guaranteed.)
Join Method
- Nested Loop Join
- Accesses the outer (driving) table, then repeatedly accesses the inner (driven) table to perform the join.
- Hash Join
- Builds a hash table from the join key of the inner table, then accesses the outer table and joins it with the hash table.
- Sort Merge Join
- Extracts the records that meet the conditions from the outer and inner tables, then sorts them by the join key.
- Performs the join using the sorted results; rows that join successfully are placed in the fetch buffer and then output.
📌 Correlation is the same concept as Oracle's Clustering Factor and indicates how closely the index and table order match.
Executor
This stage runs the query according to the execution plan and returns the result to the client. While processing the query, it uses preallocated memory areas such as temp_buffers and work_mem, and it can create temporary files as needed.
Related Articles
