How SQL Queries Are Processed by the Engine

When a SQL query is executed, the SQL engine (e.g., PostgreSQL, MySQL, etc.) follows a systematic process to transform the query into an efficient execution plan. The query execution process involves multiple steps, including parsing, optimization, and actual execution. Below is a breakdown of the sequence in which SQL clauses like SELECT, GROUP BY, ORDER BY, and others are processed by the SQL engine.

1. Parsing the Query

Query Parsing: The first step involves parsing the SQL query. The SQL engine takes the raw SQL statement and breaks it down into a syntax tree, checking whether the query is syntactically correct. This step ensures that the query is well-formed and follows the SQL language rules.

2. Query Planning (Optimization)

Logical Plan Generation: After parsing, the query is translated into a logical execution plan. This plan represents the logical steps needed to retrieve the requested data. The logical plan includes operators such as scans (table scan or index scan), filters (WHERE conditions), joins (if applicable), and aggregations (GROUP BY).
Optimization: The query planner then optimizes the logical plan to improve performance. The optimizer looks for the most efficient way to execute the query based on various factors, such as available indexes, the structure of the tables, and statistical information about the data (e.g., table sizes and cardinality). The goal is to minimize the query execution time.

3. Execution Plan Generation

Physical Plan Generation: After optimization, the query planner generates a physical execution plan. This plan specifies exactly how the query will be executed step by step, including the algorithms to use for joins, the method for scanning tables, and whether to use indexes.

4. Execution Process (Query Execution)

FROM Clause: The execution engine starts by reading data from the tables specified in the FROM clause. If there are multiple tables (for example, in JOIN operations), the engine processes them according to the plan, typically starting with the first table in the FROM clause.
WHERE Clause: After retrieving the data, the engine applies the WHERE conditions to filter the rows. It discards rows that don’t meet the conditions specified in the WHERE clause.
GROUP BY Clause: If a GROUP BY clause is present, the engine groups the filtered rows based on the specified columns. The grouping process happens after the WHERE filtering, and each group will typically have aggregation functions applied (such as COUNT(), SUM(), etc.).
HAVING Clause: After grouping, the engine applies any conditions specified in the HAVING clause. The HAVING clause filters groups that don’t meet the condition. Unlike WHERE, which filters rows before grouping, HAVING filters groups after they have been formed.
SELECT Clause: Once the rows have been grouped and filtered, the engine selects the columns specified in the SELECT clause. This is where the final projection (selection of columns) of the result set occurs.
ORDER BY Clause: If the query includes an ORDER BY clause, the result set is sorted according to the specified columns and order (ascending or descending). Sorting happens last in the query execution process, after all other operations.
LIMIT Clause: If there’s a LIMIT clause, it is applied after all other operations, limiting the number of rows returned in the result set.

5. Returning the Results

Once the query has been processed, the final result set is returned to the client. The client can then handle the results as needed, whether displaying them to the user, using them for further computation, or storing them.

6. Example of Query Execution Order

Consider the following query:

SELECT name, COUNT(*) 
FROM books 
WHERE author = 'J.K. Rowling' 
GROUP BY name 
HAVING COUNT(*) > 1 
ORDER BY name DESC 
LIMIT 10;

Execution Order:

FROM books: Data is retrieved from the books table.
WHERE author = 'J.K. Rowling': Rows where the author is not ‘J.K. Rowling’ are filtered out.
GROUP BY name: The remaining rows are grouped by the name column.
HAVING COUNT(*) > 1: Groups with fewer than 2 rows are discarded.
SELECT name, COUNT(*): The columns name and the count of rows in each group are selected.
ORDER BY name DESC: The result set is sorted in descending order by name.
LIMIT 10: Only the first 10 rows of the sorted result are returned.

Conclusion

The SQL engine processes queries step by step, starting with reading data from the table, filtering with `WHERE`, grouping with `GROUP BY`, applying `HAVING`, selecting the required columns, sorting with `ORDER BY`, and finally applying any limit to the result set. Understanding this order of execution is crucial for writing optimized and efficient queries.

Interview angle

“What happens between submitting SQL and getting rows?” - parse into a syntax tree, bind and validate against the catalogue, rewrite (views, rules), plan and optimise by estimated cost, then execute. The optimiser stage is where performance is decided.
“Why does the planner pick a bad plan?” - stale or insufficient statistics, so its row estimates are wrong. A sequential scan where you expected an index usually means it estimated the result set as large. Refresh statistics before rewriting the query.
“What is the logical order of evaluation?” - FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT - not the written order. That’s why a SELECT alias can’t be used in WHERE but can in ORDER BY.
“How do you read a plan?” - EXPLAIN ANALYZE and compare estimated versus actual rows. A large discrepancy points at statistics; the most expensive node and the join method tell you where the time went. See 10_explain_analyze.md.