Slow Query Elimination Workflow

Rank slow queries by total time (frequency times duration), fix the top offenders with index or rewrite, and verify against production load shape.

intermediate~35 minperformance-tuningdatabasesql

Objective

Reduce database load and user-visible latency by eliminating the queries that consume the most total time, verified under real traffic.

Environment

Slow query log or pg_stat_statements enabled
A staging database with production-like data volume
Index and query-change deployment capability

Steps

1. Rank by total time, not duration

Aggregate the statement stats ranking by total_exec_time, since a 50ms query called 100k times outranks a 4s query called twice.

Expected state: A ranked list exists where the top rows own most database time.

2. Profile the top offender

Run EXPLAIN (ANALYZE) on the top query against production-scale data, identifying its plan defect: missing index, bad row estimate, sort spill.

Expected state: The plan defect is named with the measured row counts.

3. Fix the defect specifically

Apply the targeted fix: composite index matching the predicate shape, rewrite eliminating the function-wrapped column, or pagination replacing the offset scan.

Expected state: The re-run plan shows the defect gone with new timings.

4. Verify under production shape

Test the fix on staging with production-scale data and production traffic replay, confirming the plan holds at real cardinalities.

Expected state: The improved plan survives production-shaped load.

5. Deploy with regression guards

Roll out with the query timeout unchanged, watch total time for the statement drop, and add a plan-regression check for the touched query.

Expected state: Statement total time falls in production stats within a day.

Expected outcome

Database time concentrated in fewer, well-planned queries, with each elimination verified at production scale and guarded against plan regression.

Verification

pg_stat_statements (or equivalent) shows the eliminated queries's total time collapsed; the aggregate database load dropped measurably.

Total time is the ranking that pays

Sorting slow queries by duration fixes the dramatic outliers; sorting by total time (calls times average) fixes the load. A 50-millisecond query in a hot loop is a bigger problem than a 4-second report, and only the total-time view shows it.

-- ranked by total time
SELECT round(total_exec_time) AS total_ms,
       calls, round(mean_exec_time, 1) AS mean_ms,
       left(query, 60) AS q
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 5;

--  412,088   102,411   4.0   SELECT * FROM items
--                                  WHERE lower(name) = $1
--   88,214    14,310   6.2   ... offset pagination ...

-- fix #1: the lower() wraps the column -> index unusable
--   functional index: CREATE INDEX ON items (lower(name));
--   mean 4.0ms -> 0.11ms  total: 412s -> 11s per day
-- fix #2: keyset pagination replaces OFFSET deep scans

Function-wrapped columns defeat indexes silently

WHERE lower(name) = $1 cannot use an index on name: the wrap makes it a fresh expression. The query runs, returns correct data, and sits in the top of total time for months. Functional indexes or stored-normalized columns are the fix, and the pattern search usually finds more than one instance.

Offset pagination is the quiet tax

OFFSET 50000 reads and discards fifty thousand rows every request: cheap at page one, linearly worse ever after, and invisible in per-query duration until deep pages dominate. Keyset (cursor) pagination makes deep pages constant-cost, which is why it appears in every total-time list’s fixes.