EXPLAIN ANALYZE for query profiling in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to profile the execution of a query using EXPLAIN ANALYZE.

PostgreSQL

EXPLAIN [1] SELECT * FROM employees WHERE salary > 50000;

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

AANALYZE

BPLAN

CPROFILE

DEXECUTE

Attempts:

3 left

💡 Hint

Common Mistakes

Using EXPLAIN PLAN instead of EXPLAIN ANALYZE.

Using EXPLAIN EXECUTE which is not valid.

Using EXPLAIN ANALYZE runs the query and shows the actual execution details.

2fill in blank

medium

Complete the code to get a detailed query plan with timing and row counts.

PostgreSQL

EXPLAIN ([1] TRUE) SELECT * FROM orders WHERE order_date > '2023-01-01';

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

AVERBOSE

BBUFFERS

CANALYZE

DCOSTS

Attempts:

3 left

💡 Hint

Common Mistakes

Using BUFFERS instead of ANALYZE to get timing.

Using VERBOSE which only adds detail but does not run the query.

The ANALYZE TRUE option runs the query and shows timing and row counts.

3fill in blank

hard

Fix the error in the EXPLAIN statement to include actual execution statistics.

PostgreSQL

EXPLAIN ([1], FORMAT JSON) SELECT * FROM products;

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

ABUFFERS

BANALYZE

CVERBOSE

DCOSTS

Attempts:

3 left

💡 Hint

Common Mistakes

Using BUFFERS alone without ANALYZE.

Using VERBOSE which does not run the query.

Adding ANALYZE runs the query and includes execution stats in the output.

4fill in blank

hard

Fill both blanks to show the query plan with buffers and actual execution time.

PostgreSQL

EXPLAIN ([1] TRUE, [2] TRUE) SELECT * FROM customers WHERE city = 'Paris';

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

AANALYZE

BBUFFERS

CVERBOSE

DCOSTS

Attempts:

3 left

💡 Hint

Common Mistakes

Using VERBOSE instead of BUFFERS to see memory usage.

Omitting ANALYZE so query does not run.

Using ANALYZE TRUE runs the query and BUFFERS TRUE shows buffer usage.

5fill in blank

hard

Fill all three blanks to run the query, show buffers, and output in JSON format.

PostgreSQL

EXPLAIN ([1] TRUE, [2] TRUE, FORMAT [3]) SELECT * FROM sales WHERE amount > 1000;

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

AANALYZE

BBUFFERS

CJSON

DTEXT

Attempts:

3 left

💡 Hint

Common Mistakes

Using FORMAT TEXT instead of JSON for structured output.

Omitting ANALYZE so no execution stats are shown.

This runs the query (ANALYZE TRUE), shows buffer usage (BUFFERS TRUE), and outputs the plan in JSON format (FORMAT JSON).