Anyone else's queries timing out on large data sets?

Exactly. The maintenance hazard is a form of technical debt that accrues interest silently. That hidden exclusion list isn't just a query parameter, it's a business rule disguised as a performance fix. Its correctness depends entirely on the correlation between your performance filter (e.g., high cardinality trace IDs) and the business logic you're analyzing.

A concrete example: excluding high-cardinality `trace_id` values to speed up a latency join assumes those traces are uniformly distributed noise. But if a new service deployment suddenly creates high-cardinality traces that are also high-latency outliers, your filtered analysis becomes misleading precisely when you need it most. The verification queries to catch this drift become as complex as the original problem.

The scheduled view forces this cost into the design phase, where you explicitly model the data reduction. It's more expensive upfront, but its failure modes are contained within the pipeline's own SLA, not hidden inside an analytical query's semantics.

Yeah, the `receiptTimeout` increase is a classic trap. You're just moving the goalposts on a query that's fundamentally trying to process too much data in a single pass. The in-memory threshold isn't undocumented by accident; it's a hard system limit to prevent runaway queries from taking down the cluster.

Your real enemy is that initial parse operation on the entire 72-hour log window. You're extracting a high-cardinality key from every single log line before you even think about joining. A more surgical approach is to push the filtering and aggregation logic into a subsearch, forcing a reduction before the join. For example, pre-aggregate your metrics side by `trace_id` first, or use a lookup to map trace IDs to services you care about before the heavy join. It forces the planner to work with a smaller, summarized dataset.

Have you explored using the `lookup` operator with a scheduled lookup table as a join alternative? It can sometimes bypass the in-memory join issue entirely by acting as a distributed hash table, though the setup is more involved.

Pushing the parse and filtering into a subsearch is the right instinct, but I've found it often doesn't force the planner's hand like you'd hope. The optimizer can still decide to materialize the full, pre-parsed dataset before applying your filters if the cost model says so.

The lookup table idea can work, but you're trading one set of problems for another. You now have to manage the freshness and cardinality of that lookup table. If your trace_id space is huge and volatile, the lookup itself becomes a large, hot dataset that needs to be broadcast, reintroducing the memory pressure you were trying to avoid.

Sometimes the only fix is to bite the bullet and materialize an aggregated fact table on a schedule. It's more upfront work than a clever subquery, but it's predictable.

Exactly. The optimizer's cost model is a black box and it loves to "help" by undoing your careful subquery. I've seen it decide to fully materialize a 50GB intermediate dataset because its outdated stats said the filter selectivity was low, when the real data would have cut it down to 5GB.

That aggregated fact table is the only real fix. All the clever tricks just become a maintenance nightmare when the planner changes its mind after an engine upgrade.

The cost of a planner surprise after a minor engine patch can dwarf the scheduled aggregation job. It's not just about correctness, it's about operational stability.

Your 50GB to 5GB example is classic. I've had to add hard query hints as a stopgap, which is another form of technical debt that breaks on upgrade. The aggregated table is the only way to lock in the predictable cost.

Hilarious that you think they'd grant a custom capacity increase. They'd just suggest the scheduled search you're already describing, and charge you more for it.

That move out of the interactive layer is the real answer, but "pre-aggregate your metrics into a timeslice before the join" is optimistic. If your join key is high cardinality, your timesliced aggregation is still huge. You're just moving the memory wall, not removing it.

The batch job to a separate index is the only way. Everything else is hoping the black box behaves.

That parse operation on a 72-hour log window is the killer. You're forcing the engine to scan and extract `trace_id` from every single log line before it can even think about joining, which creates a massive intermediate dataset right out of the gate.

I hit this same wall with Datadog Logs. The fix for us was to invert the logic: pre-aggregate the metrics side first into a much smaller dataset, *then* join. Can you push the `timeslice` and aggregation into the metrics subquery? Something like:

```sql
dataset=metrics _metric=app_latency_ms
| timeslice 1h
| max(_value) as max_latency, pct(_value, 95) as p95_latency by _timeslice, trace_id
| fields _timeslice, trace_id, max_latency, p95_latency
```

Then join your logs to that. It reduces the join from a massive many-to-many to a (hopefully) smaller many-to-one. If the metrics dataset is still too big, you might need that aggregated fact table everyone's mentioning.