Summary of Chapter# 3 : "Query Processing" from the book "The Internals of PostgreSQL"
Tags: database, apacheage, bitnine, postgres
Published: 2023-06-29 20:33:31

This blog aims to assist you in understanding the concepts of Chapter:3 [Query Processing] from the book **_[The Internals of PostgreSQL](https://www.interdb.jp/pg/)._**

> _**Note: Ensure that you have a thorough understanding of [Chapter 1](https://dev.to/vkt1271/summary-of-chapter-1-database-cluster-databases-and-tables-from-the-book-the-internals-of-postgresql-5p7) and [Chapter 2](https://dev.to/vkt1271/summary-of-chapter-2-process-and-memory-architecture-from-the-book-the-internals-of-postgresql-52ag) before we proceed to Chapter 3, as it forms the foundation for our exploration.**_

So, Let's Start:

> An Example of **Query Processing** is depicted in figure below:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/65j8bymkl409a9xule2y.png)

## 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 a tree whose root node is the SelectStmt structure defined in parsenodes.h.

> An example of a **parse tree** is depicted in figure below:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gurhxbjs0dyzabyr7sg9.png)

- The SELECT query and the parse tree have corresponding elements with the same numbering.
- The parser's main role is to check the syntax of the query and generate a parse tree.
- It does not perform semantic checks on the input query.
- Syntax errors are reported by the parser, while semantic checks are handled by the analyzer/analyser.
- The parser does not verify the existence of table names or other semantic aspects of the query.
- Its focus is on the structure and grammar of the query rather than its meaning or validity.

---------------------------------------------------------------

## Analyzer/Analyser

- The analyzer/analyser runs a semantic analysis of a parse tree generated by the parser and generates a query tree.

> An example of a **query tree** is depicted in figure below:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pvkf2d86g159lbub94fw.png)

- The join tree stores the FROM clause and the WHERE clauses.

- The targetlist is a list of columns that are the result of this query.

- The range table is a list of relations that are used in this query.

- The sort clause is a list of SortGroupClause.

---------------------------------------------------------------

## Rewriter

- Views in PostgreSQL are implemented by using the rule system. When a view is defined by the **CREATE VIEW** command, the corresponding rule is automatically generated and stored in the catalog.

- At this stage, the rewriter processes the range table node to a parse tree of the subquery, which is the corresponding view, stored in **pg_rules**.

> An example of the **rewriter stage** is depicted in figure below:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m4npb1dj56vo12siixeo.png)

---------------------------------------------------------------

## Planner and Executor

- The planner receives a query tree from the rewriter and generates a (query) plan tree that can be processed by the executor most effectively.

- The planner in PostgreSQL is based on **pure cost-based optimization.**

> A **simple plan tree** and the relationship between the **plan tree** and the **result of the EXPLAIN** command is depicted in figure below:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/j7252hkods8xj6jh9kz1.png)

- A plan tree is composed of elements called plan nodes, and it is connected to the plantree list of the **PlannedStmt** structure.

- Each plan node has information that the executor requires for processing, and the executor processes from the **end** of the plan tree to the root in the case of a single-table query.

- The executor reads and writes tables and indexes in the database cluster via the **buffer manager**.

> The relationship among the executor, buffer manager and temporary files is depicted in figure below:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fsxgklcoftgpv87mwxxv.png)

-----------------------------------------------------------------

I hope, this blog has helped you in understanding the concepts of query processing in PostreSQL.

Check out summary of [Chapter : 4](https://dev.to/vkt1271/summary-of-chapter-4-foreign-data-wrappers-fdw-from-the-book-the-internals-of-postgresql-1570)

If you want to understand [PostgreSQL In-Depth](https://www.youtube.com/playlist?list=PLhr1KZpdzukeAZuX0pNZ1lLDkaZWBOhQT).