You're not wrong: if you know how much memory you need for a single query and can keep the data in attached SSD (so ephemeral SSD on AWS), scaling vertically will give you the fastest execution time.

However horizontal scaling has advantages in that it's easier to scale up and down incrementally. It gives you more aggregate network bandwidth for accessing disaggregated data. Disaggregation allows for independent scaling of compute and storage. Losing a single machine has a marginal impact on the cluster, which can enable you to use cheaper spot resources.

Basically, it's often easier to operate a system designed around horizontal scaling if you need resiliency.