# Post.calculate\_avg\_time() taking up a long time **URL:** \ **Category:** Support\ **Created:** [2016年九月5日 19:32 UTC](https://meta.discourse.org/t/post-calculate-avg-time-taking-up-a-long-time/49750 "2016-09-05T19:32:20Z")\ **Posts on this page:** 9\ **Page:** 2
**Author:** ![sam](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/sam/32/102149_2.png) [@sam](https://meta.discourse.org/u/sam)\ **Post date:** [2019年三月5日 22:27 UTC](https://meta.discourse.org/t/post-calculate-avg-time-taking-up-a-long-time/49750/21 "2019-03-05T22:27:55Z")
@Falco I don’t want this lost forever can you do a quick PR to shift the `WHERE` clause up, I think this is safe enough and will not do any damage as far as I can tell. ---
**Author:** ![Falco](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/falco/32/179432_2.png) [@Falco](https://meta.discourse.org/u/Falco)\ **Post date:** [2019年四月30日 18:10 UTC](https://meta.discourse.org/t/post-calculate-avg-time-taking-up-a-long-time/49750/23 "2019-04-30T18:10:34Z")
The SQL changes suggested here are live now. ---
**Author:** ![Falco](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/falco/32/179432_2.png) [@Falco](https://meta.discourse.org/u/Falco)\ **Post date:** [2019年五月1日 04:11 UTC](https://meta.discourse.org/t/post-calculate-avg-time-taking-up-a-long-time/49750/24 "2019-05-01T04:11:28Z")
Looks like it went from ~ 85s to 35s here in Meta. In another instance (which runs in our beefier bare metal hosting) it went from 1350ms to 350ms. ---
**Author:** ![codinghorror](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/codinghorror/32/110067_2.png) [@codinghorror](https://meta.discourse.org/u/codinghorror)\ **Post date:** [2019年五月1日 05:23 UTC](https://meta.discourse.org/t/post-calculate-avg-time-taking-up-a-long-time/49750/25 "2019-05-01T05:23:16Z")
![niiiice-gif-2](https://global.discourse-cdn.com/meta/original/3X/c/9/c9a032da1b42132e91fe6cd31dea9b1b19b3e763.gif) ---
**Author:** ![kokmok](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/kokmok/32/78954_2.png) [@kokmok](https://meta.discourse.org/u/kokmok)\ **Post date:** [2019年五月5日 17:37 UTC](https://meta.discourse.org/t/post-calculate-avg-time-taking-up-a-long-time/49750/26 "2019-05-05T17:37:07Z")
Is it possible that changes cause performance issue ? Our instance is running slow, and I had to cancel a query to get discourse running again (but still looks slow). The query is : ``` 172 | 02:07:54.107363 | UPDATE posts +| active | | SET avg_time = (x.gmean / 1000) +| | | FROM (SELECT post_timings.topic_id, +| | | post_timings.post_number, +| | | round(exp(avg(CASE WHEN msecs > 0 THEN ln(msecs) ELSE 0 END))) AS gmean +| | | FROM post_timings +| | | INNER JOIN posts AS p2 +| | | ON p2.post_number = post_timings.post_number +| | | AND p2.topic_id = post_timings.topic_id +| | | AND p2.user_id <> post_timings.user_id +| | | WHERE (p2.topic_id IN (SELECT id FROM topics where bumped_at > '2019-05-03 09:39:22.717153'))+| | | GROUP BY post_timings.topic_id, post_timings.post_number) AS x +| | | WHERE (x.topic_id = posts.topic_id +| | | AND x.post_number = posts.post_number +| | | AND (posts.avg_time <> (x.gmean / 1000)::int OR posts.avg_time IS NULL)) | ``` We have a huge database with lots of topics containing 10k+ messages. We are trying to split them but cannot succeed to. ---
**Author:** ![Falco](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/falco/32/179432_2.png) [@Falco](https://meta.discourse.org/u/Falco)\ **Post date:** [2019年五月5日 20:38 UTC](https://meta.discourse.org/t/post-calculate-avg-time-taking-up-a-long-time/49750/27 "2019-05-05T20:38:25Z")
My changes affected only the daily query, which operates over posts from the last two days. ---
**Author:** ![kokmok](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/kokmok/32/78954_2.png) [@kokmok](https://meta.discourse.org/u/kokmok)\ **Post date:** [2019年五月6日 05:20 UTC](https://meta.discourse.org/t/post-calculate-avg-time-taking-up-a-long-time/49750/28 "2019-05-06T05:20:02Z")
I can see the date in the where clause of the query. But since our last discourse update we have performance issue. That query was running for two hours when I cancelled it. Don’t you think it’s related ? Should I create a new topic in support ? ---
**Author:** ![sam](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/sam/32/102149_2.png) [@sam](https://meta.discourse.org/u/sam)\ **Post date:** [2019年五月6日 05:47 UTC](https://meta.discourse.org/t/post-calculate-avg-time-taking-up-a-long-time/49750/29 "2019-05-06T05:47:59Z")
I will be removing this whole job in the interim, we are getting very low amounts of value out of this data and need to rethink this subsystem. EDIT: done per [https://github.com/discourse/discourse/commit/f8eddd40ade5a62cd5f9349b71a2d1b60e3c7d2e](https://github.com/discourse/discourse/commit/f8eddd40ade5a62cd5f9349b71a2d1b60e3c7d2e) Closing this as this topic is no longer applicable. ---
**Author:** ![sam](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/sam/32/102149_2.png) [@sam](https://meta.discourse.org/u/sam)\ **Post date:** [2019年五月6日 06:00 UTC](https://meta.discourse.org/t/post-calculate-avg-time-taking-up-a-long-time/49750/30 "2019-05-06T06:00:17Z")
[上一頁](https://meta.discourse.org/t/post-calculate-avg-time-taking-up-a-long-time/49750.md?page=1)