pg_stat_statements for slow queries in PostgreSQL - Step-by-Step Execution

Concept Flow - pg_stat_statements for slow queries

Enable pg_stat_statements extension

↓

Collect query execution stats

↓

Query pg_stat_statements view

↓

Filter queries by high total_time or mean_time

↓

Identify slow queries for optimization

This flow shows how PostgreSQL collects query stats with pg_stat_statements and how to find slow queries by querying its view.

Execution Sample

PostgreSQL

SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

This query fetches the top 5 queries with the highest total execution time from pg_stat_statements.

Execution Table

Step	Action	Evaluation	Result
1	Enable pg_stat_statements extension	CREATE EXTENSION pg_stat_statements;	Extension enabled
2	Run queries in database	Various queries executed	pg_stat_statements collects stats
3	Query pg_stat_statements for top slow queries	SELECT query, calls, total_time, mean_time ...	Returns rows with query stats
4	Order by total_time DESC	Sort queries by total execution time	Top slow queries appear first
5	Limit to 5 rows	Restrict output to 5 queries	5 slowest queries shown
6	Exit	No more rows to fetch	Query ends

💡 Query ends after returning top 5 slow queries by total_time

Variable Tracker

Variable	Start	After Step 3	After Step 4	Final
pg_stat_statements rows	Empty (before queries run)	Collected stats for all queries	Sorted by total_time DESC	Top 5 slow queries

Key Moments - 3 Insights

Why do we order by total_time instead of mean_time?

Ordering by total_time shows queries that consume the most total time overall, which helps find queries that impact performance the most, as seen in execution_table step 4.

What does enabling the extension do?

Enabling pg_stat_statements starts collecting query stats automatically, as shown in execution_table step 1.

Why limit the output to 5 rows?

Limiting to 5 rows focuses on the top slowest queries, making it easier to analyze, as shown in execution_table step 5.

Visual Quiz - 3 Questions

Look at the execution_table, at which step do we sort queries by total execution time?

A Step 5

B Step 4

C Step 3

D Step 2

💡 Hint

Refer to the 'Action' and 'Evaluation' columns in execution_table row for Step 4.

According to variable_tracker, what is the state of pg_stat_statements rows after Step 3?

A Collected stats for all queries

B Empty before queries run

C Sorted by total_time DESC

D Top 5 slow queries

💡 Hint

Check the 'After Step 3' column for 'pg_stat_statements rows' in variable_tracker.

If we remove the LIMIT clause, how would the execution_table change at Step 5?

A It would show queries unordered

B It would still show only 5 rows

C It would show all queries sorted by total_time

D It would cause an error

💡 Hint

Look at Step 5 in execution_table where LIMIT restricts output to 5 rows.

Concept Snapshot

pg_stat_statements tracks query execution stats.
Enable it with CREATE EXTENSION pg_stat_statements;
Query pg_stat_statements view to see queries and their times.
Order by total_time DESC to find slow queries.
Use LIMIT to focus on top slow queries.

Full Transcript

To find slow queries in PostgreSQL, first enable the pg_stat_statements extension. This collects statistics about all queries run. Then, query the pg_stat_statements view to get details like query text, number of calls, total execution time, and average execution time. Sort the results by total_time in descending order to see which queries consume the most time overall. Limiting the output to a few rows helps focus on the slowest queries. This process helps identify queries that need optimization to improve database performance.