pg_stat_statements for slow queries in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Identify the slowest query using pg_stat_statements

Given the pg_stat_statements view, which query will return the SQL statement with the highest average execution time?

PostgreSQL

SELECT query, mean_time FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 1;

A SELECT query, calls FROM pg_stat_statements ORDER BY calls DESC LIMIT 1;

B SELECT query, total_time FROM pg_stat_statements ORDER BY total_time ASC LIMIT 1;

C SELECT query, mean_time FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 1;

D SELECT query, mean_time FROM pg_stat_statements ORDER BY mean_time ASC LIMIT 1;

💡 Hint

Think about which column shows the average time per execution and how to get the highest value.

The mean_time column shows the average execution time of each query. Ordering by it descending and limiting to 1 returns the slowest average query.

🧠 Conceptual

intermediate

Understanding pg_stat_statements columns

Which column in pg_stat_statements helps identify how many times a query was executed?

A calls

B mean_time

C rows

D total_time

💡 Hint

Look for the column that counts executions.

The calls column shows how many times the query was executed.

📝 Syntax

advanced

Correct syntax to reset pg_stat_statements statistics

Which SQL command correctly resets all statistics collected by pg_stat_statements?

A SELECT pg_stat_statements_reset();

B RESET pg_stat_statements;

C TRUNCATE pg_stat_statements;

D DELETE FROM pg_stat_statements;

💡 Hint

Resetting statistics is done by a function call, not by deleting or truncating.

The function pg_stat_statements_reset() resets all statistics collected by the extension.

❓ optimization

advanced

Optimizing query to find slow queries with high calls

You want to find queries that are both slow on average and executed many times. Which query correctly selects queries with average time over 100ms and calls over 1000?

PostgreSQL

SELECT query, mean_time, calls FROM pg_stat_statements WHERE mean_time > 100 AND calls > 1000 ORDER BY mean_time DESC;

A SELECT query, mean_time, calls FROM pg_stat_statements WHERE mean_time > 100 AND calls < 1000 ORDER BY calls DESC;

B SELECT query, mean_time, calls FROM pg_stat_statements WHERE mean_time > 100 AND calls > 1000 ORDER BY mean_time DESC;

C SELECT query, mean_time, calls FROM pg_stat_statements WHERE mean_time < 100 AND calls > 1000 ORDER BY mean_time DESC;

D SELECT query, mean_time, calls FROM pg_stat_statements WHERE mean_time >= 100 OR calls >= 1000 ORDER BY calls DESC;

💡 Hint

Use AND to combine conditions that must both be true.

Option B correctly filters queries with mean_time greater than 100ms and calls greater than 1000, ordering by slowest first.

🔧 Debug

expert

Diagnose error when querying pg_stat_statements

You run this query but get an error: ERROR: relation "pg_stat_statements" does not exist. What is the most likely cause?

A You need to prefix pg_stat_statements with the schema name 'public'.

B The query syntax is incorrect; pg_stat_statements is a function, not a table.

C The user does not have SELECT permission on pg_stat_statements.

D The pg_stat_statements extension is not installed or enabled in the current database.

💡 Hint

Check if the extension is installed and enabled.

The error usually means the extension is not installed or enabled in the current database. You must run CREATE EXTENSION pg_stat_statements; first.