How PostgreSQL processes a query (parser, planner, executor) - Practice Exercises

Challenge - 5 Problems

🧠 Conceptual

intermediate

What is the first step PostgreSQL takes when processing a SQL query?

When you send a SQL query to PostgreSQL, it goes through several steps. Which step happens first?

A The parser checks the query syntax and builds a parse tree.

B The planner creates a plan to execute the query efficiently.

C The executor runs the query and returns results.

D The optimizer chooses the best index to use.

💡 Hint

Think about what must happen before PostgreSQL understands what the query means.

PostgreSQL first parses the query to check syntax and build a parse tree. This happens before planning or execution.

❓ query_result

intermediate

What does the planner produce after parsing a query?

After PostgreSQL parses a query, it passes it to the planner. What does the planner produce?

A A parse tree representing the query syntax.

B An error message if the query is invalid.

C The final query results as rows of data.

D A query execution plan detailing how to run the query.

💡 Hint

The planner decides the best way to get the data, but does not run the query yet.

The planner creates a query execution plan that the executor will follow to run the query efficiently.

📝 Syntax

advanced

Which SQL statement will cause a syntax error detected by PostgreSQL's parser?

Identify the SQL statement that PostgreSQL's parser will reject due to syntax error.

A SELECT * FROM users WHERE id = 5;

B DELETE FROM customers WHERE;

C UPDATE products SET price = price * 1.1 WHERE category = 'books';

D INSERT INTO orders (id, amount) VALUES (1, 100);

💡 Hint

Look for incomplete or incorrect SQL syntax.

Option B has an incomplete WHERE clause with no condition, causing a syntax error at parsing.

❓ optimization

advanced

How does PostgreSQL's planner optimize query execution?

Which of the following best describes how PostgreSQL's planner optimizes a query?

A It executes the query multiple times to find the fastest result.

B It always uses the first index it finds on a table.

C It chooses the cheapest plan based on estimated costs of operations.

D It skips the planning phase if the query is simple.

💡 Hint

Think about how the planner decides between different ways to run a query.

The planner estimates costs for different plans and picks the cheapest one to optimize performance.

🔧 Debug

expert

Why might a query run slower even though PostgreSQL's planner chose a plan?

You notice a query is slow even though PostgreSQL has planned it. Which reason below explains this best?

A The planner's cost estimates were inaccurate due to outdated statistics.

B The executor is skipping the execution phase.

C The parser failed to build a parse tree.

D The query was not sent to the planner at all.

💡 Hint

Consider what affects the planner's decisions and how real data changes might impact it.

If table statistics are outdated, the planner may pick a suboptimal plan causing slow execution.

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