What happens when you type a SELECT * FROM? - rnakidi/dsa GitHub Wiki
SELECT * FROM employees WHERE department_id = 1;
What happens under the hood when you send this SQL query to Postgres Database
Let's dive into the details.
-
Connection Establishment: A connection from an application program to the PostgreSQL server has to be established. The application program transmits a query to the server and waits to receive the results sent back by the server.
-
Parser: The parser stage checks the query transmitted by the application program for correct syntax and creates a query tree.
-
Analyzer: Performs semantic analysis, generating a query tree rooted at Query structure from parsenodes.h,
-
Rewriter: The rewrite system takes the query tree created by the parser stage and looks for any rules (stored in the system catalogs) to apply to the query tree. It performs the transformations given in the rule bodies. One application of the rewrite system is in the realization of views, where it rewrites the user's query to a query that accesses the base tables given in the view definition instead.
-
Planner/Optimizer: The planner takes the (rewritten) query tree and creates a query plan that will be the input to the executor. It does so by first creating all possible paths leading to the same result. For example, if there is an index on a relation to be scanned, there are two paths for the scan: a simple sequential scan and using the index. Next, the cost for the execution of each path is estimated, and the cheapest path is chosen. The cheapest path is expanded into a complete plan that the executor can use.
-
Executor: The executor recursively steps through the plan tree and retrieves rows in the way represented by the plan. It makes use of the storage system while scanning relations, performs sorts and joins, evaluates qualifications, and finally hands back the rows derived.