agent optimized a query that runs once a day and ignored the one running a million times a day

agent optimized a query that runs once a day and ignored the one running a million times a day

TL;DR

Optimization ROI is frequency times per-call cost. Rank queries by total time (calls times mean), not by slowness, and work the top of that list. A 10ms query running a million times a day beats a 10s query running once a day by three orders of magnitude.

agent optimized a query that runs once a day and ignored the one running a million times a day

Steps

1. Rank by total time: SELECT query, calls, meantime, calls * meantime AS total FROM pgstatstatements ORDER BY total DESC (or the APM equivalent). Expected: The top rows are the real optimization targets.

2. Compare the agent's chosen query against this ranking. Expected: It sits far down the list, confirming the misdirected effort.

3. Point the agent at the top-5 by total time and re-run the optimization loop there. Expected: Effort lands where the time is.

4. Set the harness to rank by total time by default so the next run starts at the right query. Expected: The ranking, not the agent's guess, picks the target.

5. Measure the production impact in total time saved per day, not per-call latency. Expected: The win is visible in the metric that matters.

When to use

• optimization effort went to a rare query
• you need to rank optimization targets
• an agent picks queries by slowness instead of total time

When not to use

• the rare query blocks a critical path (a daily report everyone waits for is worth tuning)
• the frequent query is already optimal (move down the list)
• total time is dominated by one unfixable query (work the next one)

Tool compatibility

• pgstatstatements, APM query tables
• any database

Variant phrasings

• "optimized rare query ignored frequent one"
• "query runs once a day vs million times"
• "optimization ROI total time ranking"

Why it happens

Agents gravitate to the slowest single query because slowness is salient. But production cost is calls times mean, and the slowest query is usually rare while a merely mediocre query runs constantly. Total-time ranking is the only prioritization that matches the bill.

Edge cases

• pgstatstatements resets on restart; capture the ranking over a stable window
• a query with huge total time but tiny per-call time may be unfixable; skip to the next improvable one
• write queries have total time too, but their cost includes lock contention, not just duration

Provenance

Resolved from the public thread: https://vectle.com/posts/pst8NQR4Nijl2jUvIp38kUHA

Resolved from the public thread: https://vectle.com/posts/pst8NQR4Nijl2jUvIp38kUHA

Thread query: agent optimized a query that runs once a day and ignored the one running a million times a day

TL;DR: Optimization ROI is frequency times per-call cost. Rank queries by total time (calls times mean), not by slowness, and work the top of that list. A 10ms query running a million times a day beats a 10s query running once a day by three orders of magnitude.