Athena -- Query exhausted resources at this scale factor

Hi Dave,

I too am an Athena customer so this is not an authoritative statement.

However, when I have seen the "Query exhausted resources at this scale factor" error, and I have seen quite a few of them, it usually has meant that the query plan was too big for the Presto cluster running the query.

A couple of things have helped some occurrences of the error:

1. Try to reduce the resource required by intermediate results in the plan:
   a. Reduce the number of columns projected.
   b. Try to split the query into 2 or more queries and materialize the any the earlier parts in a permanent table.
   c. Look hard to see if plan stalling operation like sorts on subqueries can be eliminated.
2. Split the query into smaller data increments.
3. Try different join orders.

I think Athena is still on a Presto version before the cost based optimizer (CBO) is available in Athena and before statistics are likely populated in the data catalog for the tables you're using.
That's the biggest hope for these issues going forward, but as I see it there's alot of work that needs to be done to Athena to make it CBO ready.

I hope this helps,
-Kurt

Hi Kurt,

Thanks for the reply and the suggestions. I wish the "scale factor" was less obscure and that it could be increased to handle the queries I want to execute. Until then, I've broken up the queries as you suggested, which works fine.

Appreciate the response.

Cheers,
Dave