EXPLAIN ANALYZE for query profiling in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What does EXPLAIN ANALYZE show for a simple SELECT?

Consider the query SELECT * FROM employees WHERE department_id = 5; run with EXPLAIN ANALYZE. What kind of information will the output include?

PostgreSQL

EXPLAIN ANALYZE SELECT * FROM employees WHERE department_id = 5;

A It shows the query plan with actual execution times and row counts for each step.

B It shows the query plan with estimated costs only, no actual execution times.

C It only shows the total execution time of the query without details.

D It returns the query result rows along with execution statistics.

💡 Hint

EXPLAIN ANALYZE runs the query and reports detailed timing and row info.

EXPLAIN ANALYZE executes the query and shows the real execution plan with actual times and rows processed at each step, unlike plain EXPLAIN which only shows estimates.

🧠 Conceptual

intermediate

Why use EXPLAIN ANALYZE instead of EXPLAIN alone?

Which reason best explains why EXPLAIN ANALYZE is more useful for query optimization than EXPLAIN?

A Because EXPLAIN ANALYZE shows actual run times and row counts, revealing real performance.

B Because EXPLAIN ANALYZE runs faster than EXPLAIN and saves time.

C Because EXPLAIN ANALYZE shows the query result data, helping verify correctness.

D Because EXPLAIN ANALYZE automatically fixes slow queries.

💡 Hint

Think about what real execution info helps you find bottlenecks.

EXPLAIN shows only estimated costs, which may differ from reality. EXPLAIN ANALYZE runs the query and shows actual timing and row counts, which helps identify slow parts.

📝 Syntax

advanced

Which EXPLAIN ANALYZE option is valid to include buffers info?

You want to see buffer usage statistics in the EXPLAIN ANALYZE output. Which of these commands is syntactically correct in PostgreSQL?

A EXPLAIN ANALYZE BUFFERS SELECT * FROM orders WHERE order_date > '2023-01-01';

B EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE order_date > '2023-01-01';

C EXPLAIN ANALYZE SELECT * FROM orders WHERE order_date > '2023-01-01' WITH BUFFERS;

D EXPLAIN ANALYZE SELECT * FROM orders WHERE order_date > '2023-01-01' (BUFFERS);

💡 Hint

Options go inside parentheses after EXPLAIN.

In PostgreSQL, options like ANALYZE and BUFFERS are specified inside parentheses after EXPLAIN, e.g., EXPLAIN (ANALYZE, BUFFERS) ....

🔧 Debug

advanced

What error occurs with incorrect EXPLAIN ANALYZE syntax?

What error will PostgreSQL raise if you run EXPLAIN ANALYZE BUFFERS SELECT * FROM products; (missing parentheses around BUFFERS)?

A Error: EXPLAIN ANALYZE does not support options.

B Runtime error: relation 'BUFFERS' does not exist.

C Syntax error near 'BUFFERS' keyword.

D No error, query runs and shows buffer info.

💡 Hint

Check how options must be formatted in EXPLAIN ANALYZE.

Options like BUFFERS must be inside parentheses. Omitting them causes a syntax error near the option keyword.

❓ optimization

expert

How to interpret high actual time but low estimated time in EXPLAIN ANALYZE?

You run EXPLAIN ANALYZE on a query and see a step with estimated time 1 ms but actual time 100 ms. What does this difference usually indicate?

A The actual time includes network latency, so it is not comparable.

B The query ran faster than expected; no action needed.

C The EXPLAIN ANALYZE output is corrupted and unreliable.

D The planner underestimated the cost; statistics may be outdated or inaccurate.

💡 Hint

Think about what causes the planner to misestimate costs.

A large gap between estimated and actual times usually means the planner's statistics are stale or incomplete, causing it to pick a suboptimal plan.