# Performance improvements on long topics?
**URL:** https://meta.discourse.org/t/performance-improvements-on-long-topics/30187
**Category:** Support
**Created:** [19.Июнь.2015 01:05:44 UTC](https://meta.discourse.org/t/performance-improvements-on-long-topics/30187 "2015-06-19T01:05:44Z")
**Posts on this page:** 11
**Page:** 2
### Author:  [@codinghorror](https://meta.discourse.org/u/codinghorror)
#### Post date: [22.Июнь.2018 09:58:42 UTC](https://meta.discourse.org/t/performance-improvements-on-long-topics/30187/21 "2018-06-22T09:58:42Z")
👍 I’m fine with a few days spent on this because we have deferred the work for two years now..
---
### Author:  [@eviltrout](https://meta.discourse.org/u/eviltrout)
#### Post date: [22.Июнь.2018 15:18:49 UTC](https://meta.discourse.org/t/performance-improvements-on-long-topics/30187/22 "2018-06-22T15:18:49Z")
> [@tgxworld](#):
>
> Instead of having the client send the post ids that it wants to the server while scrolling, I plan to have the client send the current position and tell the server whether it is looking up/down the stream. I’ve got it working locally but I’ll need to sort out all the other edge cases.
It’s tricky though, because what about filters? For example best of mode, or when restricting to one particular poster, or when moderators have “gaps” that they can expand to see deleted posts.
There are a lot of edge cases. I’m all for optimizing it, but be prepared for a LOT of regressions and testing if you take this path.
---
### Author:  [@tgxworld](https://meta.discourse.org/u/tgxworld)
#### Post date: [22.Июнь.2018 15:50:56 UTC](https://meta.discourse.org/t/performance-improvements-on-long-topics/30187/23 "2018-06-22T15:50:56Z")
> [@eviltrout](#):
>
> It’s tricky though, because what about filters? For example best of mode, or when restricting to one particular poster, or when moderators have “gaps” that they can expand to see deleted posts.
I’m not sure about the challenges with filters at the moment but my plan is to move most if not all of the calculations that we’re doing client side into the server side. The logic for scrolling up and down would simply be given the current position which is determined by the `post_number` ask the server for the next segment of posts/gaps. The server has knowledge of what filters are applied and can query for the segments accordingly. The client would just render what is given by the server. I’m still in the experimental stages but that is how I think the architecture should be.
> [@eviltrout](#):
>
> There are a lot of edge cases. I’m all for optimizing it, but be prepared for a LOT of regressions and testing if you take this path.
---
### Author:  [@codinghorror](https://meta.discourse.org/u/codinghorror)
#### Post date: [02.Июль.2018 22:10:09 UTC](https://meta.discourse.org/t/performance-improvements-on-long-topics/30187/24 "2018-07-02T22:10:09Z")
Did you want to summarize here tomorrow?
---
### Author:  [@tgxworld](https://meta.discourse.org/u/tgxworld)
#### Post date: [12.Июль.2018 00:39:24 UTC](https://meta.discourse.org/t/performance-improvements-on-long-topics/30187/27 "2018-07-12T00:39:24Z")
> [@sam](#):
>
> When we grab any additional posts in the topic (eg: you scroll up or down and load a new chunk) we grab every post\_id in the topic.
There was [another spot](https://github.com/discourse/discourse/commit/969e79d7c601cd1d2b66474ca1ea40ddc6ea293f) that has been fixed and should resolve the following problem:
> [@hammer](#):
>
> Any progress on this issue? We have a topic with ~500 replies and it’s quite painful to scroll through.
### What is the deal with performance as the size of a topic grows?
The **first load** performance of a topic degrades as a topic gets larger and is due to the fact that we have to send down every single post id when we load a topic. This would naturally raise a red flag for anyone reading this for the first time but after reading through the code base, I’ve concluded that this is a trade off that we’re making in order to be able to have clean and accurate implementation for the topic timeline.
If we look at the two numbers on our topic timeline and translate them into their respective DB queries, we would get the following:
```sql
SELECT posts.*
FROM (
SELECT posts.*, ROW_NUMBER() OVER () AS row_number
FROM posts
WHERE posts.deleted_at IS NULL
AND posts.topic_id = 1
AND posts.post_type in (1, 2, ,3, 4)
) AS posts
```
```sql
SELECT COUNT(*)
FROM posts
WHERE posts.deleted_at IS NULL
AND posts.topic_id = 1 AND posts.post_type in (1, 2, ,3, 4)
```
For the first query, we basically need the DB to fetch all the posts for the given filters before we can figure out the “index” of each post in the stream.
The second query is a count which naturally becomes expensive as the topic grows. A counter cache would not work here because the count changes based on what filters are applied to the topic.
In addition, having the entire stream of post ids present on the client side makes the following features straight forward to implement:
1. Jumping to a certain index in the stream,
2. Selecting all posts below a certain index
3. Fetching excepts while scrolling through a stream.
_Attempts were made to re-implement (1) and (3) above to work without the stream of post ids but that either made the feature inaccurate or complicated the client side code so much that I felt it wasn’t worth the trade off_
### Stopping the bleeding on MEGATOPICs (\> 10\_000 posts)
Megatopics are expensive to load for three main reasons:
1. To calculate all the gaps within a topic, a query is run to fetch all the `posts.id` for posts regardless of whether the posts have been deleted or not.
2. To generate the stream of all post ids, a query is run to fetch all the `posts.id` for posts given a set of filters.
3. First load time suffers because the client has to download the stream of post ids which has a length that is greater than 10\_000.
Our plan to stop the bleeding here is to drop/approximate certain features on Megatopics:
1. The ability to display the closest date for a given index is dropped on megatopics
2. [Gaps are not supported](https://github.com/tgxworld/discourse/commit/b4e1388f9bebc458a63cfd4864864ad9df7b7b0a) on megatopics
3. Loading excerpts while scrolling through the timeline is not supported
4. The numbers on the timeline becomes an approximation. Instead of the index of the post in the set of possible results, we use `Post#post_number` of the post. Instead of a count of all the possible results, we use the `Topic#highest_post_number`.
5. Jumping to an index on the timeline becomes jumping to the closest post number on the timeline
This may seem like taking a step backwards but do note that MEGATOPICs are quite rate in the wild. We’ve mainly seen them appear on sites with imported content since normal sites would hit the `SiteSetting#auto_close_topics_post_count` guard that is in place. For more information on why we think Megatopics are bad, you can read @codinghorror’s analysis about it.
> [@The MEGATOPIC: public good, or public menace?](https://meta.discourse.org/t/the-megatopic-public-good-or-public-menace/85592):
>
> When we founded Discourse in 2013, I had the idea that we’d support topics of any length or size, from 10 replies to 100 to 10,000 to 100,000 or even more! A few years ago we ran into a technical limitation where we send down a list of all the post IDs in the topic when you enter the topic. This starts to cause problems on the client – particularly older smartphones with less memory and CPU power – at around ~10,000 replies, so we created a site setting that automatically closes topic…
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [12.Июль.2018 01:07:40 UTC](https://meta.discourse.org/t/performance-improvements-on-long-topics/30187/28 "2018-07-12T01:07:40Z")
Might there be a way to take advantage of the difference between `count(*)` and `count(field_identier)` ?
eg. `count(*)` counts _all_ rows, whereas `count(field_intentier)` counts only the fields that are not null.
My thinking is that if each row had something like an `is_countable` field that was null for rows not wanted to be counted it could simplify the query and hopefully improve performance. The cost being that the field would need to be updated which might offset any benefit it might provide.
---
### Author:  [@codinghorror](https://meta.discourse.org/u/codinghorror)
#### Post date: [12.Июль.2018 01:25:50 UTC](https://meta.discourse.org/t/performance-improvements-on-long-topics/30187/29 "2018-07-12T01:25:50Z")
> [@tgxworld](#):
>
> Stopping the bleeding on MEGATOPICs (\>10k posts)
Just confirming one thing. With these megatopic-specific shortcuts in place, performance on megatopics improved by two thirds, correct? So what used to take 1000ms will now take ~333ms with these changes?
---
### Author:  [@tgxworld](https://meta.discourse.org/u/tgxworld)
#### Post date: [12.Июль.2018 01:31:06 UTC](https://meta.discourse.org/t/performance-improvements-on-long-topics/30187/30 "2018-07-12T01:31:06Z")
For a 70K posts topic, time spent in the DB goes from 400.1ms to 137.7ms while the first payload size decreases from 735 KB to 178 KB
---
### Author:  [@codinghorror](https://meta.discourse.org/u/codinghorror)
#### Post date: [12.Июль.2018 01:31:52 UTC](https://meta.discourse.org/t/performance-improvements-on-long-topics/30187/31 "2018-07-12T01:31:52Z")
Fantastic improvement 😍
And to summarize, this is the tradeoff on megatopics (\>10k posts) only:
1. Dates will not be shown on the timeline
2. “view x hidden replies” and other gaps will not appear in the post stream
3. Excerpts will not be shown when scrolling the timeline
4. Timeline post numbers and “jump to post” may be absolute versus relative post numbers
---
### Author:  [@tgxworld](https://meta.discourse.org/u/tgxworld)
#### Post date: [12.Июль.2018 04:50:58 UTC](https://meta.discourse.org/t/performance-improvements-on-long-topics/30187/32 "2018-07-12T04:50:58Z")
> [@Mittineague](#):
>
> My thinking is that if each row had something like an `is_countable` field that was null for rows not wanted to be counted it could simplify the query and hopefully improve performance
The sheer size of the number of records that has to be fetched would still make the query expensive to run.
---
### Author:  [@tgxworld](https://meta.discourse.org/u/tgxworld)
#### Post date: [20.Август.2018 01:44:25 UTC](https://meta.discourse.org/t/performance-improvements-on-long-topics/30187/39 "2018-08-20T01:44:25Z")
[Предыдущая страница](https://meta.discourse.org/t/performance-improvements-on-long-topics/30187.md?page=1)