Neku's Blog

PostgreSQL : Query Execution Stages

Today I learned Chapter 16 : Query Exection Stages from book PostgreSQL Internals 14.

These are the stage query to be executed:

  1. Parsing -> it will create a tree that represents the syntactic structure of the SQL query. It's also a stage where the database check the parsed query, like if the table name is valid, if the user has access to do the query, etc.
  2. Transformation -> this is where database rewrite the query and update the parsed tree. For example, if the query call the name of view then it will be expanding the view into its underlying SQL query.
  3. Planning -> it will create a new tree, that contain the statement like sort, nestloop (for join), the scanning will be used (sequential or index). In this stage, database will also count the estimation cost of the query. This estimation cost is being calculated from using statistics: table size, data distribution.
  4. Execution -> it's represented as a tree that repeats the structure of plan tree. Some nodes can produce and send data to the parent node incrementally, such as Nested Loop Join, while other nodes need to process all input before producing output, such as the Sort operator. Also some node can immediately pass the data without store in the memory, but in sort, for example, it can store the data in the memory (it's also can be configured using work_mem).

Those are 4 stages of query to be executed in the PostgreSQL. But it not make sense if we have the same query and only change the parameter constant, since it will do those 4 stages again and again.

Here, we have Prepare to do this.

Prepare statement stores the parsed query and allow PostgreSQL to reuse the query structure instead of parsing it repeatedly.

Then in PostgreSQL also have Custom Plan and Generic Plan.