Query Processing In PostgreSQL

Query processing is the most complicated system in any database management system. Here we'll discuss about how PostgreSQL handles the queries.

All the queries issued by a client is handled by a backend process. This subsystem of backend process consists of five subsystems:

1. Parser: generates a parse tree from an SQL statement in plain text
2. Analyzer: performs semantic analysis of a parse tree and generates a query tree
3. Rewriter: transforms a query tree using rule system
4. Planner: generates a plan tree than can most effectively be executed from the query tree.
5. Executor: executes the query

Now we'll learn more about them in detail.

Parser

The parser generates a parse tree that can be read by subsequent subsystems from an SQL statement in plain text. A parse tree is tree whose root node is SelectStmt structure.
A parser only checks for syntax of a input so it only returns error if there a syntax error in the issued query. This means that all the semantic errors like, wrong table or column names, are skipped in this step.

Analyzer

Analyzer runs the semantic analysis of the parse tree generated in previous step and it them generates a query tree. The root of the query tree contains the metadata of the query such as the type of query and several leaves corresponding to clauses.

Rewriter

Rewriter enforces the rule system and transforms the query tree according to the rules stored in the pg_rules system. The input and output of the rewriter are query trees and it can be thought of as a form of macro expansion. Since version 10, PostgreSQL supports rewrite rules which allow you to change how queries are executed.

Planner

The planner/optimizer in PostgreSQL is responsible for creating an optimal execution plan for a given SQL query. A query can be executed in many different ways, each of which will produce the same set of results. The query optimizer examines each of these possible execution plans and selects the one that is expected to run the fastest. This is based on pure cost-based optimization.
A plan tree is attached to the plantree list of the PlannedStmt structure and is made up of components called plan nodes which contains information that executor needs for processing. Plannodes.h defines these components.

Executor

The executor in PostgreSQL takes the plan created by the planner/optimizer and processes it to extract the required set of rows from the table. It does this by recursively processing a tree of plan nodes, where each node is a single query processing operator (join, sort, etc.) that consumes relations and produces relations. The executor mechanism is used to evaluate all four basic SQL query types: SELECT, INSERT, UPDATE, and DELETE. It reads and writes tables via a buffer manager.