Have you ever used this site to visualize the explain plan and spot bad estimates and slow nodes? https://explain.depesz.com/s/WE1R
This stands out to me-
Subquery Scan on npiactionjoin (cost=10,165,289.40..10,167,192.01 rows=293 width=16) (actual time=118,413.432..118,806.684 rows=446,782 loops=1)
Filter: (npiactionjoin.rn = 1)
Filter: (npiactionjoin.rn = 1)
It seems that estimate is pretty far off and this node and the final node above this are the biggest slowdowns. If you filtered down to the record you want from task_history BEFORE the join, then maybe you would have quicker results. I might try a materialized CTE or even an analyzed temp table if that option is available to you, so the planner makes informed decisions.
By the way, the order by on that row_number seems like you are getting the OLDEST activity related to the task which could maybe be cached rather than re-calculated daily as this query runs.
Michael Lewis | Database Engineer
Entrata
