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.

Practice

1. What is the primary purpose of the pg_stat_statements extension in PostgreSQL?

easy

A. To manage user permissions and roles

B. To backup the database automatically

C. To optimize disk storage usage

D. To track and report query execution statistics including slow queries

Solution

1. Step 1: Understand the role of pg_stat_statements

   The extension collects statistics about query execution times and counts, helping identify slow queries.

2. Step 2: Compare with other options

   Options A, B, and D describe unrelated database functions like backup, permissions, and storage optimization.

3. Final Answer:

   To track and report query execution statistics including slow queries -> Option D

4. Quick Check:

   pg_stat_statements = track slow queries [OK]

Hint: Remember: pg_stat_statements tracks query performance stats [OK]

Common Mistakes:

• Confusing pg_stat_statements with backup tools
• Thinking it manages user roles
• Assuming it optimizes disk space

2. Which SQL command correctly enables the pg_stat_statements extension in PostgreSQL?

easy

A. CREATE EXTENSION pg_stat_statements;

B. ENABLE EXTENSION pg_stat_statements;

C. LOAD EXTENSION pg_stat_statements;

D. START EXTENSION pg_stat_statements;

Solution

1. Step 1: Recall the syntax to enable extensions

   PostgreSQL uses CREATE EXTENSION extension_name; to enable extensions.

2. Step 2: Check other options

   Commands like ENABLE, LOAD, or START are not valid for enabling extensions in PostgreSQL.

3. Final Answer:

   CREATE EXTENSION pg_stat_statements; -> Option A

4. Quick Check:

   Enable extension = CREATE EXTENSION [OK]

Hint: Use CREATE EXTENSION to enable PostgreSQL extensions [OK]

Common Mistakes:

• Using ENABLE or LOAD instead of CREATE EXTENSION
• Forgetting the semicolon at the end
• Trying to enable without superuser rights

3. Given the query:

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

What does this query return?

medium

A. The query with the fewest calls

B. The most recent query executed

C. The query with the highest total execution time and its stats

D. All queries sorted by average time

Solution

1. Step 1: Analyze the ORDER BY clause

   The query orders results by total_time in descending order, so the highest total execution time is first.

2. Step 2: Understand the LIMIT 1

   LIMIT 1 returns only the top row, which is the slowest query by total execution time.

3. Final Answer:

   The query with the highest total execution time and its stats -> Option C

4. Quick Check:

   ORDER BY total_time DESC LIMIT 1 = slowest query [OK]

Hint: ORDER BY total_time DESC LIMIT 1 shows slowest query [OK]

Common Mistakes:

• Thinking it returns the most recent query
• Confusing total_time with average time
• Assuming it returns all queries

4. You run this query:

SELECT * FROM pg_stat_statements WHERE query = 'SELECT * FROM users';

But it returns no rows. What could be the problem?

medium

A. The exact query text does not match due to whitespace or formatting differences

B. pg_stat_statements is disabled and not collecting data

C. The users table does not exist

D. The query is too fast to be recorded

Solution

1. Step 1: Understand how pg_stat_statements stores queries

   It stores normalized query texts, so exact string matches may fail if whitespace or formatting differs.

2. Step 2: Consider other options

   While B is possible, the question implies pg_stat_statements is enabled. C and D do not explain no rows for that query text.

3. Final Answer:

   The exact query text does not match due to whitespace or formatting differences -> Option A

4. Quick Check:

   Exact query text match may fail due to formatting [OK]

Hint: Query text must match exactly including spaces [OK]

Common Mistakes:

• Assuming any query text matches regardless of formatting
• Ignoring that extension might be disabled
• Thinking table existence affects pg_stat_statements output

5. You want to reset all collected statistics in pg_stat_statements to start fresh after fixing slow queries. Which command should you run?

hard

A. TRUNCATE pg_stat_statements;

B. SELECT pg_stat_statements_reset();

C. DROP EXTENSION pg_stat_statements;

D. RESET pg_stat_statements;

Solution

1. Step 1: Identify the correct function to reset stats

   PostgreSQL provides the function pg_stat_statements_reset() to clear collected statistics.

2. Step 2: Evaluate other options

   RESET is not valid syntax here, DROP EXTENSION removes the extension, and TRUNCATE is not allowed on this view.

3. Final Answer:

   SELECT pg_stat_statements_reset(); -> Option B

4. Quick Check:

   Reset stats = pg_stat_statements_reset() function [OK]

Hint: Use SELECT pg_stat_statements_reset() to clear stats [OK]

Common Mistakes:

• Trying to DROP the extension to reset stats
• Using RESET command incorrectly
• Attempting to TRUNCATE the stats view