Window aggregate resulting in slow query on postgres 9.6
Your window function has to sort 10,453,164 rows. Look at cutting that down. Sorting 10.2 million rows in 158 seconds isn't too bad.

Sort (cost=3,047,865.12..3,073,554.61 rows=10,275,796 width=55) (actual time=144,515.353..148,038.268 rows=10,453,164 loops=1)
Sort Key: a.person_id, event_page_2016_8.timestamp_
Sort Method: quicksort Memory: 1863192kB


I also think there is something wrong with your partitioning. 

-&gt;  Index Only Scan using ep_2016_8_host_ts_a_ses on event_page_2016_8  (cost=0.55..4.57 rows=1 width=42) (actual time=0.016..0.016 rows=0 loops=1)
                        Index Cond: ((location_host = '2016.myhost.ca'::text) AND (timestamp_ &gt;= '2017-02-18 00:00:00'::timestamp without time zone) AND (timestamp_ &lt;= '2017-03-13 00:00:00'::timestamp without time zone))


Why does this query search a table called ep_2016_8_host_ts_a_ses when the timestamp clearly exists in 2017-02, and 2017-03. The query planner is supposed to know better. Look up constraint exclusion

My guess is here that the query can't make use of the partitions index to sort the table. It should be able to -- if all partitions were indexed by timestamp_ I would think it could walk through the indexes in parallel and get just the first rank fairly easily. I could be wrong though. I may have to play with this later. In the mean time, try getting constraint exclusion working and giving it another go.