EXPLAIN output reading in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Understanding the output of a simple EXPLAIN query

Given the following SQL query and its EXPLAIN output, what is the estimated number of rows the query planner expects to process?

EXPLAIN SELECT * FROM employees WHERE department_id = 5;

Output:

Seq Scan on employees  (cost=0.00..35.50 rows=10 width=100)

A 10

B 35

C 100

D 5

💡 Hint

Look for the 'rows=' value in the EXPLAIN output.

The 'rows=10' means the planner estimates 10 rows will be processed.

❓ query_result

intermediate

Identifying the join type from EXPLAIN output

Look at this EXPLAIN output for a join query. What type of join is being used?

Nested Loop  (cost=0.00..50.00 rows=20 width=200)
  -> Seq Scan on table1  (cost=0.00..20.00 rows=100 width=100)
  -> Index Scan using idx_table2 on table2  (cost=0.00..0.30 rows=1 width=100)

A Cross Join

B Hash Join

C Merge Join

D Nested Loop Join

💡 Hint

The first line of the EXPLAIN output usually shows the join type.

The output starts with 'Nested Loop', indicating a Nested Loop Join is used.

📝 Syntax

advanced

Detecting syntax error in EXPLAIN query usage

Which of the following EXPLAIN queries will cause a syntax error in PostgreSQL?

A EXPLAIN SELECT * FROM employees WHERE salary > 50000;

B EXPLAIN SELECT FROM employees WHERE salary > 50000;

C EXPLAIN (FORMAT JSON) SELECT * FROM employees WHERE salary > 50000;

D EXPLAIN ANALYZE SELECT * FROM employees WHERE salary > 50000;

💡 Hint

Check the syntax of the SELECT statement inside EXPLAIN.

Option B is missing the '*' after SELECT, causing a syntax error.

❓ optimization

advanced

Interpreting cost estimates in EXPLAIN output

Given this EXPLAIN output, what does the 'cost=0.00..100.00' represent?

Seq Scan on orders  (cost=0.00..100.00 rows=500 width=50)

A The startup cost is 0.00 and total cost is 100.00

B The query will take exactly 100 seconds to run

C The cost is the number of rows scanned

D The cost is the number of columns scanned

💡 Hint

Cost values show estimated resource usage, not time or row counts.

The first number is the startup cost, the second is the total estimated cost to run the query.

🧠 Conceptual

expert

Understanding the effect of EXPLAIN ANALYZE on query execution

What is the main difference between running EXPLAIN and EXPLAIN ANALYZE on a query in PostgreSQL?

A Both run the query but EXPLAIN ANALYZE shows more details about indexes

B EXPLAIN runs the query; EXPLAIN ANALYZE only shows the plan

C EXPLAIN shows estimated plan only; EXPLAIN ANALYZE runs the query and shows actual run times

D EXPLAIN ANALYZE disables all indexes during execution

💡 Hint

Think about whether the query is executed or just planned.

EXPLAIN only shows the estimated plan without running the query; EXPLAIN ANALYZE runs the query and shows actual execution details.