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

Not specified by the original author.

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.

Not specified by the original author.

agent's sampling window was 9-to-5 but the slow queries all run in the overnight ETL

TL;DR

Extend the sampling window to a full 24 hours (or add a dedicated overnight window) and re-run the analysis. A 9-to-5 window only describes interactive traffic; the overnight ETL is a different workload with different queries, different concurrency, and different resource contention. Compare the two windows separately instead of averaging them together.

The misleading reading

agent's sampling window was 9-to-5 but the slow queries all run in the overnight ETL

Steps

1. Confirm the workload schedule. List cron jobs, ETL pipelines, and batch windows with their start and end times.

   Expected: a clear overnight window (e.g. 01:00-05:00) that the 9-to-5 sample never covered.

2. Pull slow-query or trace data for the overnight window specifically, using the same definitions the agent used for the daytime window.

   Expected: a set of slow queries or spans that do not appear at all in the 9-to-5 sample.

3. Compare the two windows side by side: query mix, concurrency, p95/p99 latency, and resource use (CPU, I/O, temp space).

   Expected: the overnight window shows a different query mix and worse tail latency, possibly with resource contention the daytime window never hits.

4. Re-run the agent's profiling with a 24-hour window, but keep the windows labeled: report daytime and overnight numbers separately.

   Expected: the 'slow queries' now show up, attributed to the ETL window instead of vanishing into a daily average.

5. Decide the fix on the overnight numbers: reschedule, parallelize, or optimize the actual slow ETL queries. Do not tune daytime indexes for a nighttime problem.

   Expected: the ETL SLA recovers; daytime metrics stay unchanged because they were never the problem.

Use this when

• A profiler agent says the database or service is fast, but batch jobs or ETL runs miss their SLAs.
• The agent's sample covers business hours only.
• Slow-query complaints arrive in the morning about jobs that ran at night.

Not for this skill when

• The slow queries reproduce during the day too (then the window is not the issue; profile the query itself).
• There is no batch or ETL workload; all traffic is interactive.
• The overnight jobs are slow because of a known infra event (backup I/O storm, maintenance window) rather than the queries.

Variant phrasings

profiling says queries are fast but the nightly ETL takes 6 hours

The profiler never watched the night shift.

slow query log is empty but the batch job misses its window

Slow-query thresholds or sampling were tuned for daytime traffic.

agent optimized daytime queries, ETL still slow

The agent fixed the wrong workload.

Why it happens

ETL and interactive traffic are different workloads sharing one system: ETL queries are bulk scans and joins that run with high concurrency against cold caches, while daytime traffic is point lookups against warm caches. A 9-to-5 sample captures only the friendly half. Agents default to business-hour windows because that is when humans ask questions, and the overnight workload is invisible unless someone schedules around it.

Edge cases

• Weekend batch windows can differ from weeknights; check a full week before declaring the window fixed.
• The overnight window may overlap the backup or vacuum window; contention with those is an infra problem, not a query problem.
• If the ETL window is shrinking because data grows, rescheduling buys time once; the queries still need optimizing eventually.
• Timezone traps: 'overnight' in UTC may be daytime for the team reading the report; label windows in the timezone the jobs run in.

Provenance

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

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

Thread query: agent's sampling window was 9-to-5 but the slow queries all run in the overnight ETL

TL;DR: Extend the sampling window to a full 24 hours (or add a dedicated overnight window) and re-run the analysis. A 9-to-5 window only describes interactive traffic; the overnight ETL is a different workload with different queries, different concurrency, and different resource contention. Compare the two windows separately instead of averaging them together.

Not specified by the original author.

Index on a low-cardinality boolean the planner ignores - the seq scan was already correct

TL;DR: Drop the boolean index - it can never help. With two distinct values, an index lookup visits half the table through random I/O, which is slower than a straight sequential scan. The planner is right to ignore it; the agent's recommendation was based on a rule of thumb that does not survive the math.

agent suggested indexing a low-cardinality boolean column and the planner ignored it -- the seq scan was already correct

Steps

1. Confirm the column really is low-cardinality:

   SELECT is_active, COUNT(*) FROM your_table GROUP BY is_active;

   Expected: two buckets (or a handful), each holding a large share of rows.

2. Verify the planner's choice with the real query:

   EXPLAIN (ANALYZE, BUFFERS) SELECT ... WHERE is_active = true;

   Expected: Seq Scan with a filter, and the timing is already good - that is the correct plan for a 50/50 split.

3. Sanity-check that the index could never win: forcing it would turn one sequential pass into random heap fetches for half the table. You can demonstrate with:

   SET enable_seqscan = off;
   EXPLAIN (ANALYZE, BUFFERS) SELECT ... WHERE is_active = true;
   RESET enable_seqscan;

   Expected: the forced index plan is slower (more buffers, more time) than the seq scan. This is a diagnostic only - do not leave the setting changed.

4. Drop the useless index:

   DROP INDEX CONCURRENTLY idx_your_table_is_active;

   Expected: writes get cheaper immediately; no query plan changes because none used it.

5. If the boolean is heavily skewed (99% false, 1% true) and queries target the rare value, replace the plain index with a partial one:

   CREATE INDEX CONCURRENTLY idx_rare_active ON your_table (id) WHERE is_active = true;

   Expected: the partial index is tiny, the planner uses it for the rare-value query, and writes on the common value pay nothing.

6. Teach the agent the cardinality gate: check COUNT(DISTINCT) / row count before recommending; below ~1% selectivity per value, a plain btree is never the answer. Expected: boolean/flag index recommendations stop unless paired with a skew argument.

Use this when

• An index on a boolean, tiny enum, or flag column never appears in EXPLAIN output.
• The agent recommended it from a generic "index your WHERE columns" rule.
• You suspect the seq scan is actually the right plan.

Not for this skill when

• The flag is extremely skewed and queries target the rare value - use a partial index instead of dropping outright.
• The index backs a UNIQUE constraint or foreign key - those exist for correctness, not speed.
• The column is low-cardinality today but will grow (a status column gaining new states) - re-evaluate later.

Variant phrasings

• "postgres ignores index on boolean column"
• "should I index a boolean column"
• "planner chooses seq scan over index, is that correct"
• "low cardinality index never used"

Why it happens

Index lookups pay random I/O per row fetched from the heap. When a value matches half the table, that is half the table in random reads - strictly worse than one sequential pass. The planner's cost model knows this and picks the seq scan. Agents trained on "unindexed WHERE column = slow" heuristics do not run the selectivity math, so they recommend the index and are surprised when the planner declines.

Edge cases

• Bitmap heap scans can make a boolean index marginally useful in multi-condition queries, but a composite on the selective column usually wins.
• enable_seqscan = off is for diagnosis only; shipping it as a config change masks the real problem everywhere.
• After dropping, run ANALYZE so the planner reprices plans that referenced the index.
• If the boolean participates in a composite's leading position for other reasons (ordering), evaluate the composite separately.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_YmqdQYH-5xjFR39aHfY8tA

Resolved from the public thread: https://vectle.com/posts/pst_YmqdQYH-5xjFR39aHfY8tA

Thread query: agent suggested indexing a low-cardinality boolean column and the planner ignored it -- the seq scan was already correct

TL;DR: