Evaluate different EXPLAINs on Redshift
Fabio has given a great answer. 

However for Redshift, it is worth adding that how your data is physically laid out has a dramatic impact on EXPLAIN plan cost. 

Create some dummy data: 
(inspired by https://stackoverflow.com/questions/38667215/redshift-how-can-i-generate-a-series-of-numbers-without-creating-a-table-called)
 
drop table if exists #my_table;
create table #my_table as 
select
    (row_number() over (order by 1)) - 1 as user_id
    ,(current_date - user_id::int)::timestamp created_at
from
    stl_load_errors
limit 24

Reproduce similar EXPLAIN plans to the question:

explain 
SELECT user_id,
       created_at
FROM
  ( SELECT user_id,
           created_at,
           row_number() OVER (PARTITION BY user_id
                              ORDER BY created_at) AS rownum
   FROM #my_table) x
WHERE rownum = 1

EXPLAIN:
XN Subquery Scan x  (cost=1000000000000.79..1000000000001.39 rows=1 width=16)
  Filter: (rownum = 1)
  -&gt;  XN Window  (cost=1000000000000.79..1000000000001.09 rows=24 width=16)
        Partition: user_id
        Order: created_at
        -&gt;  XN Sort  (cost=1000000000000.79..1000000000000.85 rows=24 width=16)
              Sort Key: user_id, created_at
              -&gt;  XN Network  (cost=0.00..0.24 rows=24 width=16)
                    Distribute
                    -&gt;  XN Seq Scan on "#my_table"  (cost=0.00..0.24 rows=24 width=16)


Next:

explain 
SELECT ac1.user_id, ac1.created_at FROM #my_table ac1
JOIN 
(
   SELECT user_id, MAX(created_at) AS MAXDATE
   FROM #my_table
   GROUP BY user_id
) ac2
ON ac1.user_id = ac2.user_id
AND ac1.created_at = ac2.MAXDATE


EXPLAIN:
XN Hash Join DS_DIST_INNER  (cost=0.72..822858.77 rows=13 width=16)
  Inner Dist Key: ac1.user_id
  Hash Cond: (("outer".maxdate = "inner".created_at) AND ("outer".user_id = "inner".user_id))
  -&gt;  XN Subquery Scan ac2  (cost=0.36..0.66 rows=24 width=16)
        -&gt;  XN HashAggregate  (cost=0.36..0.42 rows=24 width=16)
              -&gt;  XN Seq Scan on "#my_table"  (cost=0.00..0.24 rows=24 width=16)
  -&gt;  XN Hash  (cost=0.24..0.24 rows=24 width=16)
        -&gt;  XN Seq Scan on "#my_table" ac1  (cost=0.00..0.24 rows=24 width=16)

This is indeed much faster but data is distributed between nodes (DB_DIST_INNER).



Now try:


drop table #my_table_dist;
create table #my_table_dist 
distkey(user_id) sortkey(user_id,created_at) as 
select
    (row_number() over (order by 1)) - 1 as user_id
    ,(current_date - user_id::int)::timestamp created_at
from
    stl_load_errors
limit 24


Now run EXPLAIN:

explain 
SELECT user_id,
       created_at
FROM
  ( SELECT user_id,
           created_at,
           row_number() OVER (PARTITION BY user_id
                              ORDER BY created_at) AS rownum
   FROM #my_table_dist) x
WHERE rownum = 1


EXPLAIN:
XN Subquery Scan x  (cost=0.00..0.78 rows=1 width=16)
  Filter: (rownum = 1)
  -&gt;  XN Window  (cost=0.00..0.48 rows=24 width=16)
        Partition: user_id
        Order: created_at
        -&gt;  XN Seq Scan on "#my_table_dist"  (cost=0.00..0.24 rows=24 width=16)
 The data is already sorted and distributed so Redshift simply reads off the answer.

Whilst:

explain 
SELECT ac1.user_id, ac1.created_at FROM #my_table ac1
JOIN 
(
   SELECT user_id, MAX(created_at) AS MAXDATE
   FROM #my_table_dist
   GROUP BY user_id
) ac2
ON ac1.user_id = ac2.user_id
AND ac1.created_at = ac2.MAXDATE

EXPLAIN: 
XN Hash Join DS_DIST_INNER  (cost=0.36..822858.77 rows=13 width=16)
  Inner Dist Key: ac1.user_id
  Hash Cond: (("outer".maxdate = "inner".created_at) AND ("outer".user_id = "inner".user_id))
  -&gt;  XN Subquery Scan ac2  (cost=0.00..0.66 rows=24 width=16)
        -&gt;  XN GroupAggregate  (cost=0.00..0.42 rows=24 width=16)
              -&gt;  XN Seq Scan on "#my_table_dist"  (cost=0.00..0.24 rows=24 width=16)
  -&gt;  XN Hash  (cost=0.24..0.24 rows=24 width=16)
        -&gt;  XN Seq Scan on "#my_table" ac1  (cost=0.00..0.24 rows=24 width=16)
Note no difference in cost due to distribution of data between nodes (DB_DIST_INNER).