pg_stat_statements for slow queries in PostgreSQL - Mini Project: Build & Apply

Using pg_stat_statements to Identify Slow Queries in PostgreSQL

📖 Scenario: You are a database administrator for a small online store. You want to find out which queries are running slowly so you can improve the website's speed.

🎯 Goal: Learn how to enable and query the pg_stat_statements extension in PostgreSQL to find slow queries.

📋 What You'll Learn

Enable the pg_stat_statements extension

Create a configuration variable for minimum execution time

Write a query to select slow queries from pg_stat_statements

Order the results by average execution time descending

💡 Why This Matters

🌍 Real World

Database administrators use pg_stat_statements to monitor and improve query performance in real applications.

💼 Career

Knowing how to find slow queries helps optimize databases, a key skill for database administrators and backend developers.

1

Enable the pg_stat_statements extension

Write the SQL command to enable the pg_stat_statements extension in your current database.

PostgreSQL

-- Your code here

Need a hint?

Use CREATE EXTENSION IF NOT EXISTS pg_stat_statements; to enable the extension.

2

Set minimum execution time for slow queries

Create a variable called min_exec_time and set it to 100 to represent 100 milliseconds as the threshold for slow queries.

PostgreSQL

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- Your code here

Need a hint?

Use \set min_exec_time 100 to create the variable in psql.

3

Query slow queries from pg_stat_statements

Write a SQL query to select query, calls, and mean_exec_time from pg_stat_statements where mean_exec_time is greater than :min_exec_time.

PostgreSQL

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
\set min_exec_time 100
-- Your code here

Need a hint?

Use SELECT query, calls, mean_exec_time FROM pg_stat_statements WHERE mean_exec_time > :min_exec_time;

4

Order slow queries by average execution time

Modify the previous query to order the results by mean_exec_time in descending order.

PostgreSQL

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
\set min_exec_time 100
SELECT query, calls, mean_exec_time
FROM pg_stat_statements
WHERE mean_exec_time > :min_exec_time
-- Your code here

Need a hint?

Add ORDER BY mean_exec_time DESC to sort slow queries from slowest to fastest.