I am going to share short notes on, how a query is internally processed in PostgreSQL?
There are different stages involved in the PostgreSQL query execution process and I will explain short notes on each and later I will try to write in details for each one of it.
Connection: The connection has to be established from an application program to the PostgreSQL Server.
Parser: It creates a query tree after checking of all the query syntax which is sent by an application.
Rewrite System: It takes the query tree from the parser and check for any other rules and modification. For example, if the query contains a VIEW, it has to modify the query to select a base table which is related to that view.
Planner or Optimizer: It creates all possible query paths and after that it chooses one of the cheapest path for query execution.
For example, It finds two paths, one path is the table scan without an index and second path is the index scan with the indexes. It is estimated and executes the best path.
Executor: It takes the plan from the Planner and extract the required set of the rows. Internally, it manages the pipeline mechanism and one by one it takes the Query-Plan and delivers the required rows.