Finding and fixing slow queries
A DBA does not wait for someone to say "the app is slow". You keep the numbers that show where the database spends its time, catch the plans of the slow runs, and fix one thing at a time with before and after numbers. This lesson turns that into a weekly routine for Mangrove Market's order database.
By the end you can
- Rank queries by total time and share of the total, from hourly pg_stat_statements snapshots
- Set up pg_stat_statements, auto_explain and the slow-query log without hurting the server
- Choose the right kind of fix: an index, a rewrite, better statistics, an app change or a setting
- Tune autovacuum per table for big, busy tables and size work_mem and the PgBouncer pool from evidence
- Prove a fix with before and after numbers from the same busy hour
The everyday picture
A store manager wants shorter checkout queues. She could stand at the tills and watch for the slowest customer: the one with three full trolleys who takes 15 minutes. That customer comes once a week. Fixing their visit saves almost nothing.
Instead she pulls the till report for the evening rush and adds up the minutes per kind of task. Weighing loose vegetables takes only 20 seconds, but it happens 5,000 times an evening: about 28 hours of cashier time. Pre-weighed bags fix the queue. Then she checks next week's report for the same hours, to prove it worked.
Slow queries work the same way. The slowest single query is rarely the problem. The problem is the query that costs the most time in total during the busy hours. You find it with numbers, change one thing, and measure the same hours again.
The DBA's loop: rank by total time, not by the slowest
Reading query plans and fixing slow queries taught you to read one plan and to sort pg_stat_statements by total time. A DBA turns that into a loop that runs every week and after every big release.
- Measure a time window, such as the evening peak from 18:00 to 21:00.
- Rank query shapes by total time in that window, with each one's share of the total, calls and mean time.
- Capture a real plan for the top query from auto_explain or the slow-query log.
- Explain on a copy with
EXPLAIN (ANALYZE, BUFFERS), on production-sized data. - Fix one thing, then measure the same window again and record both numbers.
Why total and not mean? Total time is what the server actually spent: CPU, disk reads, locks held, connections busy. A query with a mean of 1.8 ms that runs 1.2 million times an evening costs 2,160 seconds of server time. A report with a mean of 12 seconds that runs 30 times costs 360 seconds. The report feels slow to the one person who waits for it; the first query slows the database for everyone.
Share of the total tells you how much you can win. If the top query is 36% of all execution time, a 10 times faster version frees roughly a third of the server. If the top query is 3%, the load is spread out, and the answer is more likely capacity, caching or fewer calls from the app.
Quick check
In the evening peak, query A ran 40 times with a mean of 9 seconds. Query B ran 900,000 times with a mean of 2 ms. Query C ran 20,000 times with a mean of 40 ms. Which do you work on first?
Multiply calls by mean: A is 360 s, B is 1,800 s, C is 800 s. B uses the most server time, so a fix there frees the most capacity. A is the slowest per run, which matters to its one user, but it is the smallest total. One window is enough to start; a week of data confirms the pattern.