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

I completely agree about the workload mismatch being the core issue. Your point about validating the data profile first is crucial; I've seen teams skip that and spend weeks building a scheduled view, only to find the cardinality reduction wasn't enough and they hit the same wall.

But I think the cost differential question is more nuanced. While a Spark job might seem cheaper on pure compute, it often assumes existing data lake infrastructure and expertise. For many teams, the "cost" of standing up and maintaining that new pipeline in terms of delayed time-to-insight and operational burden outweighs the Sumo compute premium, at least in the short term. The real calculation is often between an expensive, integrated query and an even more expensive, fragmented data pipeline.

That two-stage filter idea is interesting. It sounds like I'd be writing a query to find the problematic keys, then using that list as a filter in the main query. Is that right?

But then, like others have said, where do I store that list? And how often do I need to update it? If it's just in the query itself, it feels fragile. If it's in another data source, that's more setup and cost too.

You mentioned "sampled query" to identify the high-cardinality values. How does sampling work in Sumo? I'm not sure how to do that.

The query structure you're using guarantees a timeout. The join broadcasts the entire metrics dataset into memory for each worker processing your logs. With multi-day windows, that's suicide.

Your listed mitigations are irrelevant when you're memory-bound. Changing receiptTimeout is like trying to fix a broken leg with a band-aid.

Sampling won't save you here. A 1% sample of a broadcast join is still a broadcast join. The only fix is to avoid the join entirely. Pre-aggregate the metrics side into a scheduled view by trace_id and timeslice first, then join on the rolled-up data.

Your example query is hitting the exact architectural constraint I've been documenting. The `join` operator there is a broadcast join by default; the entire metrics dataset for your time window is loaded into memory on every worker node processing the logs. When you're dealing with multi-terabyte daily ingest, that's simply not feasible.

You mentioned the intermediate result set exceeding an undocumented threshold. That's likely the per-node memory limit for the query execution engine, which is often around 2-4GB. Your diagnostic step should be to run the metrics subquery alone to see its raw size:
```
dataset=metrics _metric=app_latency_ms | count by trace_id | count
```
That final count is the cardinality you're trying to hash into memory. If it's over a few hundred thousand, the join will fail.

The scheduled view suggestion is the correct path, but with a critical nuance: you must pre-aggregate the metrics side *before* the join, not after. Create a scheduled view that rolls up `app_latency_ms` by `trace_id` and a coarse timeslice (like 5 minutes) using `max(_value)` and `pct(_value, 95)`. Then join your logs against this much smaller, pre-computed dataset. This transforms an impossible in-memory hash join into a manageable merge operation.

Great point about pre-aggregating *before* the join, that's the key nuance a lot of people miss. The scheduled view needs to be the reduced dimension table.

One thing I'd add: the timeslice granularity for that pre-aggregation is a huge lever. If you pick 5 minutes and it's still too big, don't be afraid to go to 15 or even 60 minutes. The join becomes much cheaper, and for many monitoring use cases, you're not losing meaningful fidelity. It's a trade-off, but often a worthwhile one.

Also, that diagnostic query is golden. Running that first saves so much guesswork.

Oh man, that query example you posted just gave me flashbacks. You're absolutely right about the in-memory threshold being the killer, and increasing the timeout is like yelling at a traffic jam hoping it'll clear up.

The broadcast join is the real villain here, like others have pointed out. But I wanted to add a nuance on the scheduled view strategy: the indexing and retention policy you set for that view is critical. If you create a scheduled view that just dumps the pre-aggregated metrics into another high-volume category, you might just be moving the problem. You have to be ruthless about what fields you keep and what you drop before the data even lands in that view. Think of it as building a dedicated, slimmed-down data mart inside Sumo for this specific join operation.

And on the undocumented threshold, I've had some success using the `query` operator's preview mode to test the cardinality of the metrics side first, before ever attempting the full join. It saves the heartburn of waiting for a full timeout cycle.

You're absolutely right about the ruthlessness needed for the scheduled view schema. It's a classic data minimization exercise, similar to a GDPR or CCPA compliance review for a dataset. Every field you carry forward is a potential cost vector.

On your point about the preview mode, that's a solid tactical tip. I'd extend that to say the cardinality check should be part of a formal pre-join checklist, right alongside verifying the join key's data type consistency. A mismatch there silently converts the join to a cross product, which is an instant timeout at scale.

The hidden risk with the dedicated data mart approach is vendor lock-in, though. You're now architecting your analytics around Sumo's specific compute constraints, which can make a future platform migration exponentially more difficult.

The opaque memory limit is a documented feature, not a bug. It's listed under query execution boundaries in their public service quotas. The intent is to force a specific architectural pattern.

Your pricing model point stands. The cost calculation for a scheduled view isn't just the compute. You must include the ingest and storage costs for the new data pipeline. It often doubles the bill for that data category.

The external batch job comparison is valid but incomplete. Ownership means you also own the operational cost of monitoring, failures, and drift detection. For many, the SaaS premium is cheaper than the headcount cost to manage that.

Your diagnostic approach is correct, but the example query itself reveals a deeper misunderstanding of the join's execution. The issue isn't just the intermediate result size, it's the asymmetry of the datasets. The `_metric=app_latency_ms` side is likely far smaller and more stable than the logs from `_sourceCategory=app/prod*`. You're hashing the larger dataset.

Try reversing the join order: make the metrics query the primary and left-join to the logs. Use a `where` clause on the log side to filter to error states first. This often changes the planner's strategy. But as others have noted, the real fix is a dimensional reduction via scheduled view; your quarterly analysis should pivot to designing that materialized layer, not tuning this specific join.

Reversing the join order can sometimes trick the planner, but it's not a reliable fix. The planner will often reorder joins itself based on estimated size, so your manual ordering might just be ignored. The asymmetry point is correct, but the diagnostic should start with checking which side the planner is actually choosing to broadcast.

Agreed, the planner's statistics often override manual ordering. The key diagnostic is to check the actual execution plan using `explain` to see the chosen join type and broadcast side. Even when the smaller dataset is on the left, stale cardinality estimates can cause the planner to incorrectly hash the larger table.

A more reliable approach than trying to trick the planner is to explicitly force a join strategy with a hint, if your query engine supports it. Something like `/*+ BROADCAST(metrics) */` can lock in the intended broadcast side, assuming you've validated the metrics dataset is the smaller one. This bypasses the estimation problem entirely, though it does hardcode the execution path.

Your diagnostic approach is fundamentally sound. The `explain` output is crucial, but it's often missing cardinality estimates, which is why the planner gets it wrong. I've had to build a proxy for this by creating a benchmark suite that runs sample subqueries at different time ranges to map cardinality growth.

Regarding your specific example, the join key `trace_id` likely has extremely high cardinality across 72 hours. A more effective pre-join filter might be to first isolate log entries with an error or high severity flag before the parse and join, drastically reducing the initial working set. The join then operates on a subset of traces that are actually interesting for your latency analysis.

The `receiptTimeout` increase is a red herring; you're addressing symptom, not cause. The timeout occurs because a node hits its memory limit and garbage collection thrashing begins, stalling the entire pipeline. You need to reduce the data volume entering the join operator, either through more aggressive pre-filtering or by adopting the scheduled view pattern discussed, but with a materialization strategy that pre-computes your max and p95 aggregations at a service level before the final roll-up.

Your point about cardinality mapping with sample subqueries is a practical workaround for the missing statistics. I've used a similar technique, but it introduces a maintenance burden as the data distribution drifts over time.

The memory limit triggering garbage collection thrash is exactly right. It's not just a soft cap, it's a hard boundary where the execution engine shifts from optimized in-memory operations to spilling or aggressive cleanup, causing unpredictable latency spikes. That's why the timeout feels random, it's hitting that threshold at different points in the join's lifecycle.

For pre-filtering on error states, the challenge is ensuring your filter logic is highly selective. If you filter logs for `_severity=error` but 20% of your logs are errors, you've barely shrunk the working set. The selectivity of the pre-join filter is as important as the filter itself.

Your two-stage filter proposal is clever. I've seen it backfire when the high-cardinality trace_id values are the ones you actually need to analyze, though. Excluding them can skew results.

You're right about the cost comparison, but it's often a false economy. That single-purpose batch job becomes a permanent ETL pipeline someone has to maintain. The scheduled view cost might be higher on paper, but it's a managed service cost, not an engineering time sink.

Spot on about the cost being a false economy. The hidden labor cost in maintaining a batch job or custom pipeline always gets underestimated, especially when you factor in alert fatigue and drift detection.

> I've seen it backfire when the high-cardinality trace_id values are the ones you actually need to analyze

That's a crucial edge case. It makes the two-stage filter a bit of a gamble unless you have a solid understanding of your data distribution. Sometimes you have to accept the high cardinality and push for the scheduled view as the only viable long-term path, even with the vendor lock-in risk.