Search Postgresql Archives

Re: Query takes around 15 to 20 min over 20Lakh rows

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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)

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux