Postgres in depth · Chapter 53 of 65
Finding the slow queries
By the end of this chapter you can find which statements cost a database the most time with pg_stat_statements, tell a statement that is slow on average from one that is merely frequent, and explain connection limits and why apps use a connection pooler. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Inside Postgres
- 4 tasks
- PostgreSQL
- In the all labs plan
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.
Why you need this
LCO's server sits at 80% CPU every morning. Kabir asks which page is slow; nobody knows. You can EXPLAIN a query (chapter 50), but which of the thousands of queries the app sends deserves it? Priya wants numbers: for every statement, how often it runs, how long it takes in total and on average, and how many pages it reads. Postgres can keep exactly that, if you ask it to.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
top-total.sql: where the time goes
The Check builds lesson_events as the lesson does, then plays a morning of LCO traffic against it (pg_stat_statements is installed in its copy). Find the three SELECT statements of this database that took the most execution time in total.
Return two columns: query (the normalized text) and calls, ordered by total execution time, most first (3 rows).
Careful: pg_stat_statements covers every database on the server; keep this database's statements, and only SELECTs.
- Task 2
most-called.sql: the busiest statement
With the same morning of traffic: find the statement of this database that ran most often. Return its normalized text, its number of calls and its mean execution time in milliseconds, rounded to 3 decimals.
Return three columns: query, calls and mean_ms (1 row).
Careful: the same query with different constants is one statement here; its calls are added together.
- Task 3
slowest-mean.sql: the slowest repeated statement
One slow call can be bad luck; a statement that is slow on average is a problem. Among this database's statements that ran at least 3 times, find the one with the highest mean execution time.
Return two columns: query and calls (1 row).
Careful: the one-off setup statements ran once, so they do not count here.
- Task 4 · interview question
Interview: connections.sql, how many more connections?
"The app says the remaining connection slots are reserved: how close are we?" Return one row: the server's max_connections, the slots kept back (superuser_reserved_connections plus reserved_connections), the client connections in use on the whole server, and how many more an ordinary role can open (the limit minus the reserved slots minus those in use).
Return four columns: max_connections, reserved, in_use and available, all whole numbers.
Careful: pg_stat_activity also lists background processes, which use no client slot; count only client backends.
What you will learn
- pg_stat_statements: per-statement totals, normalized so that the same query with other constants counts once.
- Total, mean and calls: which number to sort by, and why.
- Slow query logging: log_min_duration_statement.
- Connections: max_connections, reserved slots, and connection poolers.
More in Inside Postgres
Build "Finding the slow queries" yourself
LCO, an edtech company, moves its platform onto a single Postgres server you run. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.