Planned lessonMID-013-L05

Finding slow queries with pg_stat_statements

Lesson 5 of 8
Mid-level

On This Lesson 4 topics

In this lesson
Planned topics
Draft outline details
Practice

This lesson is planned. The outline below lists the topics it will cover. The full lesson has not been published yet.

In this lesson

find the queries that cost the most overall.

Planned topics

Enable the extension via shared_preload_libraries and CREATE EXTENSION pg_stat_statements (restart required).
It aggregates by normalized query text: calls, total time, mean time, rows.
Sort by total time, not only mean: a fast query called a million times can dominate.
Reset with pg_stat_statements_reset() before a test to measure cleanly.
Column names changed in PostgreSQL 13 (total_exec_time); verify version before recording.

Draft outline details

Demonstration

sql

SELECT calls, round(total_exec_time) AS total_ms, round(mean_exec_time::numeric, 2) AS mean_ms, query
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

Check your understanding

Why sort by total time?

Show answer

Frequent cheap queries can cost more in total than rare slow ones.

Practice

Apply this lesson with the module's lab, exercises and quiz.

Lab and exercises Quiz

Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.

Continue to next lesson