EXPLAIN output reading in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to show the query plan for selecting all rows from the table named employees.

PostgreSQL

EXPLAIN [1] * FROM employees;

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

ASELECT

BINSERT

CUPDATE

DDELETE

Attempts:

3 left

💡 Hint

Common Mistakes

Using INSERT, UPDATE, or DELETE after EXPLAIN instead of SELECT.

Forgetting to write SELECT after EXPLAIN.

The EXPLAIN command shows the execution plan of a query. To see the plan for selecting rows, use EXPLAIN SELECT.

2fill in blank

medium

Complete the code to get a detailed query plan with actual run times for the query selecting all rows from employees.

PostgreSQL

EXPLAIN ([1]) SELECT * FROM employees;

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

AANALYZE

BVERBOSE

CBUFFERS

DCOSTS

Attempts:

3 left

💡 Hint

Common Mistakes

Using VERBOSE or COSTS instead of ANALYZE to get actual run times.

Not including any option and missing timing details.

Adding ANALYZE runs the query and shows actual run times in the plan.

3fill in blank

hard

Fix the error in the EXPLAIN command to show the query plan with buffers information for a SELECT query.

PostgreSQL

EXPLAIN (ANALYZE, [1]) SELECT * FROM employees;

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

ATIMING

BANALYZE

CBUFFERS

DVERBOSE

Attempts:

3 left

💡 Hint

Common Mistakes

Repeating ANALYZE option twice.

Using TIMING which is deprecated in newer PostgreSQL versions.

Using VERBOSE which shows more details but not buffer info.

The BUFFERS option shows buffer usage in the query plan. It must be used with ANALYZE to see actual buffer reads and hits.

4fill in blank

hard

Fill both blanks to create a query that shows the query plan with costs and without actual execution for a SELECT from employees.

PostgreSQL

EXPLAIN ([1], [2]) SELECT * FROM employees;

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

ACOSTS

BANALYZE

CBUFFERS

DVERBOSE

Attempts:

3 left

💡 Hint

Common Mistakes

Including ANALYZE which runs the query and shows actual times.

Using BUFFERS without ANALYZE which does not show buffer info.

COSTS shows estimated costs in the plan. VERBOSE adds extra details. Without ANALYZE, the query is not run, so no actual times appear.

5fill in blank

hard

Fill all three blanks to create a query that shows the query plan with actual execution, buffers info, and verbose output for a SELECT from employees.

PostgreSQL

EXPLAIN ([1], [2], [3]) SELECT * FROM employees;

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

AANALYZE

BBUFFERS

CVERBOSE

DCOSTS

Attempts:

3 left

💡 Hint

Common Mistakes

Omitting ANALYZE which means no actual run times.

Using COSTS instead of VERBOSE for detailed output.

Not including BUFFERS to see memory usage.

ANALYZE runs the query and shows actual times. BUFFERS shows buffer usage. VERBOSE adds detailed info about the plan nodes.