[Summary]Chapter#03 "The Internals of PostgreSQL" Query Processing (Part-01)

Query Processing

Querying processing is the most complicated subsystem in postgreSQL and it efficiently processes the supported SQL.
In PostgreSQL parallel query implemented in version 9.6 uses multiple background worker processes and backend queries of connected clients are handled by backend processes and it consists of five subsystems.

• Parser:
  The parser generates a parse tree that can be read by subsequent from an SQL statement from an SQL statement in plain text.
  The element of the SELECT query and the corresponding elements of the parse tree are numbered the same. Parse only checks the syntax of an input when generating a parse tree and returns an error only when syntax error.

• Analyser:
  It runs a semantic analysis of a parse tree generated by parser. The root of a query tree is the query structure defined in parsenodes.h, this structure contains metadata of the query such as type of command and several leaves. Each leaf forms a list of trees and holds data of the individual particular clause.

• Rewriter:
  According to rules which is stored in pg_rules system, rewriter realises the rule system and transform a query, Description of rule system and rewriter is omitted to prevent chapter to become too long.

• Planner and Executor:
  Planner is the most effective way to execute the query that planner receives from a rewriter. Planner in postgreSQL is based on cost-based optimization and it does not support rule based optimization, and this planner is the most complex subsystem in relational databases.
  Plan tree is composed of plan nodes and it is connected to plantree list of PlannedStmt structure and these are defined in plannodes.h.
  Plan node has information that the executor requires for processing, in case of single table query, the executor processes from the end of the plan tree to the root.
  Executor reads and writes tables and indexes in the database cluster via buffer manager , executor uses some memory areas for query processing, like temp buffer, work_mem.
  PostgreSQL uses the concurrency control mechanism to maintain consistency and isolation of the running transaction.

Reference