Optimising Athena query to avoid "Query exhausted resources at this scale factor"

Question

I have the following query:
```
SELECT
a.deadline_id,
a.item_type,
a.item_id,
a.workflow_type,
a.version,
a.start_timestamp,
a.estimated_end_timestamp,
a.end_timestamp,
a.duration_minutes
FROM (
    SELECT *,
    IF (d.end_timestamp IS null,
        date_diff('minute', d.start_timestamp, now()),
        date_diff('minute', d.start_timestamp, d.end_timestamp))
    duration_minutes,
    ROW_NUMBER() OVER (PARTITION BY deadline_id ORDER BY version DESC) rank
    FROM "deadlines_glue_database_v2"."deadlines_glue_table_v2_024122188037" d
    WHERE start_timestamp between TIMESTAMP '2025-04-15 00:00:00.000' and TIMESTAMP '2025-07-15 00:00:00.000' AND 
datehour between '2025/04/15/00' and '2025/07/15/00'
    ) a
WHERE a.rank = 1
AND a.start_timestamp between TIMESTAMP '2025-04-15 00:00:00.000' and TIMESTAMP '2025-07-15 00:00:00.000' 
AND duration_minutes > 43800
LIMIT 1000
```

The goal is retrieve all deadlines from the 2025/04/15 to 2025/07/15 that completed with a duration of more than 43800 minutes (30 days). However, this (and variations thereof) all meet "Query exhausted resources at this scale factor". What are ways that I could optimise this query?

re:Post Agent · Answer

The "Query exhausted resources at this scale factor" error occurs when your query demands exceed the resources available to the Athena cluster. Here are several ways to optimize your query:

1. **Optimize your window function usage**: The `ROW_NUMBER() OVER (PARTITION BY deadline_id ORDER BY version DESC)` operation is memory-intensive. Consider if you can achieve the same result with a more efficient approach.

2. **Remove redundant filtering**: You're applying the same date filter twice - once in the subquery and once in the outer query. Remove the redundant filter in the outer query:
```
WHERE a.rank = 1
AND duration_minutes > 43800
```

3. **Improve partition pruning**: Your query uses both `start_timestamp` and `datehour` filters. Ensure your partitioning strategy is effective and that partition pruning is working correctly. Use EXPLAIN or EXPLAIN ANALYZE to verify partition pruning.

4. **Break down the query**: Consider splitting this into smaller queries if possible. For example, first query for the highest version per deadline_id, then join that result with the original table.

5. **Optimize data structure**: If possible, compress your data files and ensure optimal file sizes to reduce data transfer.

6. **Implement retry mechanism**: Use an ExponentialBackoff algorithm for longer wait times between query retries.

7. **Consider capacity reservations**: If this is a recurring workload, use Athena's capacity reservations to get dedicated processing capacity.

8. **Check for data skew**: Ensure your data is evenly distributed across partitions to avoid overloading specific workers.

9. **Remove the ORDER BY if possible**: If you're using an ORDER BY clause elsewhere in your query (not shown in the example), consider removing it as sorting is done on a single node and can cause resource exhaustion.

10. **Optimize the date range**: If possible, narrow down your date range to reduce the amount of data being processed.

If these optimizations don't resolve the issue, you might need to consider using Athena's Provisioned Capacity for consistent, predictable workloads.  
**Sources**  
[Resolve the Athena error "query exhausted resources" | AWS re:Post](https://repost.aws/knowledge-center/athena-query-exhausted)  
[Athena query error: Query exhausted resources at this scale factor. | AWS re:Post](https://repost.aws/questions/QUnqFbBkVwS0qICorhoXDfZQ/athena-query-error-query-exhausted-resources-at-this-scale-factor)  
[Optimize service use - Amazon Athena](https://docs.aws.amazon.com/athena/latest/ug/performance-tuning-service-level-considerations.html)  
[Using EXPLAIN and EXPLAIN ANALYZE in Athena - Amazon Athena](https://docs.aws.amazon.com/athena/latest/ug/athena-explain-statement.html)

Optimising Athena query to avoid "Query exhausted resources at this scale factor"

Add your answer

Relevant content