3. Query Processing

As described in the official document, PostgreSQL supports a very large number of features required by the SQL standard of 2011. Query processing is the most complicated subsystem in PostgreSQL, and it efficiently processes the supported SQL. This chapter outlines this query processing, in particular, it focuses on query optimization.

This chapter comprises the following four parts:

• Part 1: Section 3.1.
  This section provides an overview of query processing in PostgreSQL.

• Part 2: Sections 3.2. — 3.4.
  This part explains the steps followed to obtain the optimal plan of a single-table query. In Sections 3.2 and 3.3, the processes of estimating the cost and creating the plan tree are explained, respectively. Section 3.4 briefly describes the operation of the executor.

• Part 3: Sections 3.5. — 3.6.
  This part explains the process of obtaining the optimal plan of a multiple-table query. In Section 3.5, three join methods are described: nested loop, merge, and hash join. Section 3.6 explains the process of creating the plan tree of a multiple-table query.

• Part 4: Sections 3.7.
  This part briefly explains Parallel Query.

PostgreSQL supports two technically interesting and practical features: Foreign Data Wrappers (FDW) and JIT compilation which is supported from version 11. The FDW will be described in Chapter 4. The JIT compilation is out of scope of this document.