Query Optimization

Oodle flags queries that use the $__range variable, both in the query editor and with a warning triangle on the panel header. This page explains what those queries make Oodle do, why that is slow, and how to rewrite them so they return the same number quickly.

Background: steps and lookback windows

Two things decide how much work a dashboard query does. Both are worth understanding before changing anything.

The step

A graph panel does not run your query once. It runs it once per step, and each run produces one point on the line.

The step is chosen from the time range and the width of the panel, so that the panel gets roughly one point per pixel:

• A 6 hour range on a wide panel gives a step of about 15 seconds, so the query runs about 1,440 times.
• The same panel on a 30 day range gives a step of about 30 minutes, so the query runs about 1,440 times again.

You can see the chosen step in the query editor, and override its lower bound with the Min step field. A stat panel that runs an Instant query is the special case: it has no steps, it runs the query once, at the end of the range.

The lookback window

The number in brackets after a metric is the lookback window. It tells Prometheus how far back from each step to look:

rate(http_requests_total[5m])

At every step, this reads the samples from the previous 5 minutes and computes a rate from them. Step 1 reads 11:00:00 back to 10:55:00, step 2 reads 11:00:15 back to 10:55:15, and so on.

So the total work of a range query is:

number of steps  x  samples in the lookback window  x  series

The step controls the first factor. The lookback window controls the second. $__range is a problem because of the second.

What $__range does

$__range expands to the full dashboard time range. On a dashboard set to the last 6 hours, this query:

sum(rate(http_requests_total[$__range]))

becomes:

sum(rate(http_requests_total[6h]))

The lookback window is now 6 hours, and it stays 6 hours at every step. Step 1 reads the 6 hours before 11:00:00, step 2 reads the 6 hours before 11:00:15, and the two overlap by 5 hours 59 minutes 45 seconds. Every step re-reads almost exactly the same data as the step before it.

The cost

Same 6 hour dashboard, 15 second step, 15 second scrape interval, and 200 series in http_requests_total:

	[$__range] (6h window)	[$__dd_interval] (2m window)
Steps	1,440	1,440
Samples per series, per step	1,440	8
Total samples read	about 415 million	about 2.3 million

The number of steps is identical. The lookback window is what differs, and it makes the query read 180 times more data, because 6 hours divided by 2 minutes is 180.

An Instant query has only one step, so it avoids the multiplication, but it still reads the whole range in that one step and does so again on every dashboard refresh.

The gap widens as the user zooms out. Going from a 1 day to a 30 day dashboard multiplies what every step reads by 30 when the window is $__range, and leaves it roughly flat when the window is an interval variable. That is exactly when the dashboard is most likely to time out.

Use an interval variable instead

The fix is a lookback window that scales with the time range instead of equalling it.

Variable	What it is
$__rate_interval	Grafana's built-in window for rate() and increase(), equal to max(step + scrape interval, 4 x scrape interval). Sized so a rate always has enough samples to be defined.
$__dd_interval	Oodle's default rollup window for the time range, matching Datadog's default rollup.
$__large_interval	A coarser rollup, sized for bar charts and long lookbacks.

All three shrink and grow with the dashboard time range, so the samples read per step stay roughly constant no matter how far the user zooms out. The Oodle rollups resolve like this:

Dashboard range	$__dd_interval	$__large_interval
1 hour	20s	1m
6 hours	2m	5m
24 hours	5m	20m
7 days	1h	4h
30 days	4h	12h

$__dd_interval is capped at 4h and $__large_interval at 12h, so the longest lookbacks stay bounded too.

Examples

Requests per second on a graph

# Slow
sum(rate(http_requests_total[$__range]))

# Fast
sum(rate(http_requests_total[$__rate_interval]))

These are not the same query, and the fast one is usually the one you wanted. The first draws a flat line at the average rate across the whole dashboard range, because every step looks back over the same 6 hours. The second draws the rate as it actually moved over time.

Error ratio on a graph

Both sides of the division need the same window:

# Slow
sum(rate(http_requests_total{status=~"5.."}[$__range]))
  / sum(rate(http_requests_total[$__range]))

# Fast
sum(rate(http_requests_total{status=~"5.."}[$__rate_interval]))
  / sum(rate(http_requests_total[$__rate_interval]))

Slowest endpoints on a bar chart

A bar chart wants fewer, wider bars than a line graph, which is what $__large_interval is for:

# Slow
topk(10, sum by (route) (rate(http_request_duration_seconds_sum[$__range])))

# Fast
topk(10, sum by (route) (rate(http_request_duration_seconds_sum[$__large_interval])))

Total requests on a stat panel

A stat panel that shows one number for the whole time range is the case where $__range looks unavoidable:

sum(increase(http_requests_total[$__range]))

You do want every sample in the range here. You just do not need to read them all in a single step. Split the range into windows, compute the value per window, and add the windows back up:

1. Replace $__range with $__dd_interval in the query.
2. Set the panel's Min step to $__dd_interval as well, so the steps and the lookback windows are the same width and tile the range with no overlaps or gaps.
3. Switch the query from Instant to Range.
4. Add a Reduce transformation with the Total calculation, which sums the data points back into a single value.

sum(increase(http_requests_total[$__dd_interval]))

On a 6 hour dashboard $__dd_interval is 2 minutes, so the range query runs 180 steps, each reading its own 2 minutes with no overlap, and Reduce adds the 180 results into the single number the stat panel shows. Total samples read: the range exactly once.

The query editor offers an Optimize query button that applies all four steps for you when it can prove the rewrite returns the same value.

Errors in the time range on a stat panel

The same rewrite, with a label matcher and a sum by:

# Slow, instant query
sum by (service) (increase(http_requests_total{status=~"5.."}[$__range]))

# Fast, range query with Min step $__dd_interval, reduced by Total
sum by (service) (increase(http_requests_total{status=~"5.."}[$__dd_interval]))

sum by (service) keeps one series per service, and Reduce totals each of them separately, so the panel still shows one number per service.

Peak value in the time range

max_over_time does not add up across windows, so Total is the wrong reduction. Compute the maximum per window and reduce with Max instead:

# Slow, instant query
max(max_over_time(node_memory_working_set_bytes[$__range]))

# Fast, range query with Min step $__dd_interval, reduced by Max
max(max_over_time(node_memory_working_set_bytes[$__dd_interval]))

The maximum of the per-window maxima is the maximum over the range, so the number is identical. The same pattern works for min_over_time with Min.

Average over the time range

An average of averages is correct when the windows hold the same number of samples, which they do when the step and the window are equal:

# Slow, instant query
avg(avg_over_time(node_cpu_utilization[$__range]))

# Fast, range query with Min step $__dd_interval, reduced by Mean
avg(avg_over_time(node_cpu_utilization[$__dd_interval]))

If a series starts or ends partway through the dashboard range, its first and last windows hold fewer samples and the result drifts slightly from the single-window average.

$__range used as a number

$__range_s and $__range_ms expand to the length of the dashboard range in seconds and milliseconds, and are usually there to turn a total back into a rate:

# Slow
sum(increase(http_requests_total[$__range])) / $__range_s

# Fast
sum(rate(http_requests_total[$__rate_interval]))

rate() already divides by its window, so the division is not needed once the window is an interval variable.

Which functions can be split this way

Splitting a range into windows and reducing the results is only valid when the reduction matches the function:

Function	Reduction over the windows
sum_over_time	Total. Every sample falls in exactly one window.
count_over_time	Total. Same reason.
increase	Total, up to Prometheus' per-window extrapolation. Splitting also handles counter resets more accurately.
max_over_time	Max.
min_over_time	Min.
avg_over_time	Mean, exact when every window holds the same number of samples.
rate	Use $__rate_interval and plot it, or sum increase and divide once.
quantile_over_time	none. A quantile of per-window quantiles is not the quantile over the range.
changes, resets	none. Events that straddle a window boundary are lost.

The outer aggregation matters too. sum() commutes with summing over time, so sum(increase(m[w])) reduced by Total gives the original number. max() commutes with Max in the same way. Mixing them does not: the maximum of per-window maxima summed by Total is not the maximum over the range.

Best Practices

• Reach for $__rate_interval on any counter you plot over time, and for $__dd_interval or $__large_interval when you want a coarser rollup.
• Keep Min step equal to the lookback window when you split a total across windows. A step smaller than the window double counts samples, a larger one skips them.
• Match the Reduce calculation to the function, using the table above. Total is right for increase and sum_over_time, and wrong for max_over_time.
• Check the panel header after editing. The warning triangle disappears once no query on the panel uses $__range.

Related Pages

• PromQL Reference - Functions, operators, and common query patterns.
• Dashboard Variables - Query and ad hoc filter variables.
• Grafana global variables - Upstream reference for $__range and $__rate_interval.