Window aggregate resulting in slow query on postgres 9.6

Question

I'm using Postgresql 9.6 and I'm running into a performance issue. I have a relatively simple query:

explain analyze
 SELECT * FROM ( 
     SELECT A.person_id, O.session_id,  timestamp_ AS step_ts_min ,rank() over (partition by A.person_id order by timestamp_) as rn
     FROM event_page O
     JOIN alias A ON (O.person_alias = A.alias)
     WHERE O.timestamp_ between '2017-02-18 00:00:00.000' AND  '2017-03-13 00:00:00.000' 
     and O.location_host like '2016.myhost.ca'
) as innerstep WHERE rn=1  

The event_page table is actually a view over monthly tables union-ed together, you can see that in the plan.

This query takes over 2.5 minutes to run and you can see the lions share of that time is used by quicksort, along with 1.8G of ram!!!

Here is the execution plan: https://explain.depesz.com/s/v51h

Is there a way to improve performance and optimize the query?

Answer 1

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.

->  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_ >= '2017-02-18 00:00:00'::timestamp without time zone) AND (timestamp_ <= '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.