The Internals of PostgreSQL: Query Processing - Part 2

We will be discussing rest of the three subsystems in detail in this article.

Rewriter
Planner
Executor

Rewriter

The rewriter is responsible for implementing the rule system and modifying a query tree based on the rules stored in the pg_rules system catalog, if required.

Planner

After receiving the query tree from the rewriter, the planner in PostgreSQL undertakes the task of generating a plan tree that can be executed by the executor in the most efficient manner.
It's important to note that the planner in PostgreSQL exclusively relies on cost-based optimization and does not support rule-based optimization or hints.
A plan tree is composed of elements called plan nodes, and it is connected to the plantree list of the PlannedStmt structure. These elements are defined in plannodes.h.

Executor

The executor in PostgreSQL relies on information provided by each plan node to carry out the processing tasks. In the case of a single-table query, the executor processes the plan tree from the end to the root.

For instance, consider the plan tree illustrated for a simple plan tree above, consisting of a sort node followed by a sequential scan node. In this scenario, the executor scans the table "tbl_a" using a sequential scan and subsequently sorts the obtained result.
To interact with tables and indexes within the database cluster, the executor utilizes the buffer manager. During query processing, the executor makes use of pre-allocated memory areas such as temp_buffers and work_mem, and creates temporary files as necessary.
Moreover, PostgreSQL employs a concurrency control mechanism to ensure consistency and isolation of running transactions when accessing tuples.