Pick a depth. Each prompt opens in your AI pre-loaded with the lesson. Click a row to preview the prompt.
Once the instrumentation is on you will have a list of twenty candidate queries and no obvious order to attack them in. The instinct is to start with the slowest, which is almost always wrong, because the slowest query is frequently a nightly report whose latency nobody experiences. The correct ordering combines three things: how much total database time the query consumes, how much of that time you plausibly believe is recoverable, and whether a human is waiting on it. A query burning 38% of your database at 0.5ms a call is worth more than one taking 51 seconds twice a day, unless the 51-second one blocks a checkout page. Making this explicit as a small calculation, rather than an instinct, also gives you something to show a manager when you ask for three days, and it protects you from the most demoralising outcome in this work: spending a week making something four times faster and finding that nothing measurable changed.
The prioritisation calculation as an actual query, run against pg_stat_statements and against the Mongo profiler, ranking candidates by expected recovered database time.
-- ── Rank candidates by what a fix would actually buy you ────────
--
-- Three inputs:
-- share what fraction of total database time this query is
-- headroom the fraction you believe is recoverable, estimated from
-- the plan (a COLLSCAN-equivalent that returns 20 rows out
-- of 2M has ~99% headroom; an index scan returning 200k
-- rows out of 200k has ~0%)
-- blocking 1 if a user waits on it synchronously, 0.2 if it is a
-- background job. Latency you do not feel is worth less.
WITH totals AS (SELECT sum(total_exec_time) AS t FROM pg_stat_statements)
SELECT
left(regexp_replace(query, '\s+', ' ', 'g'), 55) AS query,
calls,
round(mean_exec_time::numeric, 2) AS mean_ms,
round((total_exec_time / 1000)::numeric, 0) AS total_sec,
round((100 * total_exec_time / t)::numeric, 1) AS pct_of_db,
-- rows returned per call: a crude headroom proxy. A query returning
-- one row per call that takes 70ms has huge headroom; one returning
-- 400k rows per call is probably doing necessary work.
round((rows::numeric / nullif(calls, 0)), 1) AS rows_per_call,
round((shared_blks_read::numeric / nullif(calls, 0)), 0) AS disk_blocks_per_call
FROM pg_stat_statements, totals
WHERE calls > 10
ORDER BY total_exec_time DESC
LIMIT 15;
-- query | calls | mean_ms | total_sec | pct | rows/call | blocks/call
-- ------------------------------+---------+---------+-----------+-----+-----------+------------
-- SELECT * FROM users WHERE... | 8412033 | 0.50 | 4210 |38.4 | 1.0 | 0.1
-- SELECT ... orders WHERE u... | 41022 | 72.65 | 2980 |27.2 | 0.6 | 3120.0
-- SELECT count(*) FROM even... | 12 | 50975.0 | 612 | 5.6 | 1.0 | 88400.0
--
-- Row 1: 38% of the database, 1 row per call, almost no disk. It is
-- already optimal per call. Headroom is ~0 IN THE DATABASE.
-- The fix is in the application: 8.4M calls is an N+1 or a
-- missing cache. Biggest win, and not a SQL problem.
--
-- Row 2: 27% of the database, 3,120 disk blocks to return 0.6 rows.
-- Headroom ~99%. THIS is the index. Highest-value SQL fix.
--
-- Row 3: 5.6% of the database, 12 calls. Even a perfect fix recovers
-- 5.6% of database time and no user ever waited. Do it last,
-- unless something synchronous depends on it.
-- ── The cheap sanity check before committing to a fix ────────────
-- If disk_blocks_per_call is near zero and rows_per_call is near 1,
-- the query is already doing the minimum. Stop looking at the query.