# 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

```text
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