How PostgreSQL processes a query (parser, planner, executor) - Visual Walkthrough

Concept Flow - How PostgreSQL processes a query (parser, planner, executor)

Receive SQL Query

↓

Parser: Check syntax

↓

Planner: Create execution plan

↓

Executor: Run plan, fetch data

↓

Return results to client

PostgreSQL takes a SQL query, checks its syntax, plans how to get the data, then runs the plan and returns the results.

Execution Sample

PostgreSQL

SELECT name FROM employees WHERE age > 30;

This query asks PostgreSQL to get names of employees older than 30.

Execution Table

Step	Component	Action	Input	Output
1	Parser	Check syntax and build parse tree	SELECT name FROM employees WHERE age > 30;	Parse tree representing the query structure
2	Planner	Analyze parse tree and create plan	Parse tree	Execution plan (e.g., scan employees, filter age > 30, project name)
3	Executor	Run plan step by step	Execution plan	Rows with employee names where age > 30
4	Executor	Return results to client	Filtered rows	Result set sent to client

💡 Query fully processed and results returned to client

Variable Tracker

Variable	Start	After Parser	After Planner	After Executor	Final
query_text	SELECT name FROM employees WHERE age > 30;	Parse tree created	Execution plan created	Rows fetched	Results sent

Key Moments - 3 Insights

Why does PostgreSQL first create a parse tree before planning?

The parse tree shows the query structure clearly, so the planner knows exactly what to do next. See execution_table step 1 and 2.

What happens if the query has a syntax error?

The parser stops and returns an error before planning or executing. This is why parsing is the first step (execution_table step 1).

Does the executor run the original SQL text?

No, the executor runs the execution plan created by the planner, not the raw SQL text. See execution_table step 3.

Visual Quiz - 3 Questions

Look at the execution table, what does the planner produce after analyzing the parse tree?

A Raw SQL text

B An execution plan

C Final result set

D Syntax error message

💡 Hint

Check execution_table row 2 under Output column

At which step does PostgreSQL check if the SQL query is written correctly?

A Executor step

B Planner step

C Parser step

D Result return step

💡 Hint

Look at execution_table row 1 under Component and Action columns

If the query had a syntax error, what would happen in the execution table?

A Parser would stop and return an error

B Executor would run the plan partially

C Planner would create a plan anyway

D Results would be returned empty

💡 Hint

Refer to key_moments about syntax error and execution_table step 1

Concept Snapshot

PostgreSQL query processing steps:
1. Parser checks SQL syntax and builds parse tree.
2. Planner creates an execution plan from parse tree.
3. Executor runs the plan to fetch data.
4. Results are returned to the client.
Parsing stops on syntax errors before planning.

Full Transcript

When PostgreSQL receives a SQL query, it first sends it to the parser. The parser checks if the query is written correctly and builds a parse tree that represents the query's structure. If the syntax is wrong, it stops here and returns an error. If the query is valid, the planner takes the parse tree and creates an execution plan. This plan describes how to get the data efficiently. Then the executor runs this plan step by step, fetching the requested data. Finally, the results are sent back to the client. This process ensures queries run correctly and efficiently.

Practice

1. Which of the following is the correct order of steps PostgreSQL uses to process a query?

easy

A. Parsing, Planning, Executing

B. Executing, Planning, Parsing

C. Planning, Parsing, Executing

D. Executing, Parsing, Planning

Solution

1. Step 1: Understand PostgreSQL query processing stages

   PostgreSQL first reads and checks the query syntax in the parsing step.

2. Step 2: Identify the sequence of query processing

   After parsing, it plans the best way to execute the query, then finally executes it.

3. Final Answer:

   Parsing, Planning, Executing -> Option A

4. Quick Check:

   Query processing order = Parsing, Planning, Executing [OK]

Hint: Remember: parse first, plan second, execute last [OK]

Common Mistakes:

• Confusing the order of planning and parsing
• Thinking execution happens before planning
• Mixing up the steps as parallel instead of sequential

2. Which SQL command helps you see the query plan chosen by PostgreSQL's planner?

easy

A. SHOW PLAN

B. DESCRIBE

C. EXPLAIN

D. PLAN QUERY

Solution

1. Step 1: Identify the command to view query plans

   PostgreSQL uses the EXPLAIN command to show how it plans to execute a query.

2. Step 2: Confirm other options are incorrect

   DESCRIBE is not a PostgreSQL command; SHOW PLAN and PLAN QUERY do not exist in PostgreSQL.

3. Final Answer:

   EXPLAIN -> Option C

4. Quick Check:

   View query plan = EXPLAIN [OK]

Hint: Use EXPLAIN to check query plans quickly [OK]

Common Mistakes:

• Using DESCRIBE which is MySQL-specific
• Assuming SHOW PLAN or PLAN QUERY exist in PostgreSQL
• Confusing EXPLAIN with EXPLAIN ANALYZE

3. Given the query SELECT * FROM users WHERE age > 30;, what is the first step PostgreSQL performs internally?

medium

A. Execute the query and return rows

B. Parse the query to check syntax and build a parse tree

C. Plan the query execution strategy

D. Optimize the query by rewriting it

Solution

1. Step 1: Identify the initial internal step for query processing

   PostgreSQL first parses the query to check for syntax errors and create a parse tree.

2. Step 2: Understand subsequent steps happen after parsing

   Planning and optimization happen after parsing, and execution is last.

3. Final Answer:

   Parse the query to check syntax and build a parse tree -> Option B

4. Quick Check:

   First step = Parsing [OK]

Hint: Parsing always comes before planning or execution [OK]

Common Mistakes:

• Thinking execution happens first
• Confusing planning with parsing
• Assuming optimization is a separate step before parsing

4. You wrote a query but PostgreSQL returns a syntax error. Which step likely failed during query processing?

medium

A. Parsing step

B. Execution step

C. Planning step

D. Optimization step

Solution

1. Step 1: Identify which step checks query syntax

   The parsing step checks the query syntax and builds the parse tree.

2. Step 2: Understand syntax errors occur during parsing

   If syntax is invalid, parsing fails and PostgreSQL returns an error before planning or execution.

3. Final Answer:

   Parsing step -> Option A

4. Quick Check:

   Syntax errors = Parsing failure [OK]

Hint: Syntax errors happen during parsing, before planning [OK]

Common Mistakes:

• Blaming execution for syntax errors
• Confusing planning errors with syntax errors
• Thinking optimization detects syntax problems

5. You want to improve query performance by understanding how PostgreSQL chooses execution methods. Which step should you analyze and which tool helps you see it?

hard

A. Parsing step; use EXPLAIN

B. Execution step; use EXPLAIN ANALYZE

C. Optimization step; use DESCRIBE

D. Planning step; use EXPLAIN

Solution

1. Step 1: Identify which step chooses execution methods

   The planning step decides the best way to run the query, choosing indexes and join methods.

2. Step 2: Select the tool to view the planner's decisions

   EXPLAIN shows the plan chosen by the planner; EXPLAIN ANALYZE runs the query and shows actual times.

3. Final Answer:

   Planning step; use EXPLAIN -> Option D

4. Quick Check:

   Analyze planner with EXPLAIN during planning [OK]

Hint: Use EXPLAIN to see planning decisions, not parsing or execution [OK]

Common Mistakes:

• Confusing EXPLAIN with EXPLAIN ANALYZE for planning only
• Thinking parsing chooses execution methods
• Using DESCRIBE which is not a PostgreSQL tool