pg_stat_statements for slow queries in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select the query text from pg_stat_statements.

PostgreSQL

SELECT [1] FROM pg_stat_statements;

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

Aquery

Buserid

Ccalls

Dtotal_time

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting 'userid' instead of 'query' will not show the SQL text.

Selecting 'calls' or 'total_time' shows statistics, not the query text.

The query column contains the text of the SQL statements tracked by pg_stat_statements.

2fill in blank

medium

Complete the code to order queries by total execution time descending.

PostgreSQL

SELECT query, total_time FROM pg_stat_statements ORDER BY [1] DESC;

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

Acalls

Bmean_time

Cuserid

Dtotal_time

Attempts:

3 left

💡 Hint

Common Mistakes

Ordering by 'calls' orders by number of executions, not time.

Ordering by 'mean_time' orders by average time, not total.

Ordering by total_time descending shows the slowest queries first by total time spent.

3fill in blank

hard

Fix the error in the code to filter queries with total_time greater than 1000 ms.

PostgreSQL

SELECT query, total_time FROM pg_stat_statements WHERE total_time [1] 1000;

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

A>

B=

C<

D<=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=' will only find queries exactly 1000 ms, which is unlikely.

Using '<' or '<=' finds faster queries, not slow ones.

To find slow queries, filter where total_time is greater than 1000 milliseconds.

4fill in blank

hard

Fill both blanks to select query and calls where calls are more than 50 and order by calls descending.

PostgreSQL

SELECT [1], calls FROM pg_stat_statements WHERE calls [2] 50 ORDER BY calls DESC;

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

Aquery

B>

C<

Dtotal_time

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting 'total_time' instead of 'query' will not show the SQL text.

Using '<' instead of '>' filters for fewer calls, not more.

Select query and filter where calls is greater than 50 to find frequently run queries, then order by calls descending.

5fill in blank

hard

Fill all three blanks to select query, calls, and mean_time for queries with mean_time over 200 ms, ordered by mean_time descending.

PostgreSQL

SELECT [1], [2], mean_time FROM pg_stat_statements WHERE mean_time [3] 200 ORDER BY mean_time DESC;

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

Aquery

Bcalls

C>

Dtotal_time

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting 'total_time' instead of 'calls' changes the meaning.

Using '<' instead of '>' filters for faster queries, not slower.

Select query and calls, filter where mean_time is greater than 200 ms, and order by mean_time descending to find slow queries by average time.