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

Practice - 5 Tasks

1fill in blank

easy

Complete the code to parse a SQL query string in PostgreSQL.

PostgreSQL

SELECT * FROM pg_parse_query('[1]');

Drag options to blanks, or click blank then click option'

ASELECT * FROM users

BSELECT 1

CINSERT INTO table VALUES (1)

DUPDATE table SET col=2

Attempts:

3 left

💡 Hint

Common Mistakes

Using incomplete or invalid SQL statements.

Confusing parser input with planner or executor commands.

The parser takes the SQL query string and converts it into a parse tree. Here, 'SELECT * FROM users' is a valid query string to parse.

2fill in blank

medium

Complete the code to plan a parsed query in PostgreSQL.

PostgreSQL

EXPLAIN [1] * FROM users WHERE id = 1;

Drag options to blanks, or click blank then click option'

ASELECT

BINSERT

CUPDATE

DDELETE

Attempts:

3 left

💡 Hint

Common Mistakes

Using EXPLAIN with non-SELECT commands incorrectly.

Confusing query types.

The planner creates an execution plan for a SELECT query. EXPLAIN shows the plan.

3fill in blank

hard

Fix the error in the executor command to run a query in PostgreSQL.

PostgreSQL

EXECUTE [1];

Drag options to blanks, or click blank then click option'

AEXECUTE 'my_query'

B'my_query'

CEXECUTE my_query

Dmy_query

Attempts:

3 left

💡 Hint

Common Mistakes

Putting the prepared statement name in quotes.

Repeating the EXECUTE keyword inside the command.

The EXECUTE command runs a prepared statement by its name without quotes.

4fill in blank

hard

Fill both blanks to create a prepared statement and execute it in PostgreSQL.

PostgreSQL

PREPARE [1] AS SELECT * FROM users WHERE id = [2]; EXECUTE [1];

Drag options to blanks, or click blank then click option'

Amy_query

B1

Cuser_query

D42

Attempts:

3 left

💡 Hint

Common Mistakes

Using different names for PREPARE and EXECUTE.

Using invalid values for the condition.

We prepare a statement named 'my_query' and then execute it. Here, '1' is used in the WHERE clause.

5fill in blank

hard

Fill the blanks to parse, plan, and execute a simple SELECT query in PostgreSQL.

PostgreSQL

WITH parsed AS (SELECT pg_parse_query('[1]') AS tree), planned AS (SELECT pg_plan_query(tree) AS plan FROM parsed) SELECT pg_execute_plan(plan, [2]) AS result FROM planned;

Drag options to blanks, or click blank then click option'

ASELECT * FROM users

BARRAY[]::text[]

CARRAY['1']::text[]

DSELECT id FROM users

Attempts:

3 left

💡 Hint

Common Mistakes

Passing parameters in wrong format.

Using invalid SQL strings.

The query string is 'SELECT * FROM users'. Since there are no parameters, pass an empty text array 'ARRAY[]::text[]' to pg_execute_plan.

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