From their FAQ [1]:
Q: What is the difference between Amazon Athena, Amazon EMR, and Amazon Redshift?
[...] Amazon Redshift provides the fastest query performance for enterprise reporting and business intelligence workloads, particularly those involving extremely complex SQL with multiple joins and sub-queries.
[...] Amazon Athena provides the easiest way to run ad-hoc queries for data in S3 without the need to setup or manage any servers.
Redshift is a sorted relational database and as such it provides the performance necessary to handle Big Data when and only operated correctly, which is to say, when the sorting orders of the tables are correct for the queries being issued on those tables.
If you start talking as the quotes here do about "extremely complex SQL" it is going to be utterly impractical to operate Redshift correctly; it's impossibly complicated.
When operated incorrectly, Redshift has the same performance profile as good old Postgres, except you have the cluster rather than a single node.
The cluster is an expensive way to get 10x or 100x performance, but if you have Big Data, you need a LOT more performance than this - you need sorting, with the staggering efficiency gains it brings - but sorting only works when operated correctly.
The docs from AWS here are, in my view, utterly misleading and are dangerous to readers.
Still, Athena is for S3 and is not a relational database engine, with all that this means. Redshift is one. That was my point.