# Private Message Page Performance
**URL:** https://meta.discourse.org/t/private-message-page-performance/39865
**Category:** Development
**Tags:** slow-sql
**Created:** [2016年二月19日 20:04 UTC](https://meta.discourse.org/t/private-message-page-performance/39865 "2016-02-19T20:04:41Z")
**Posts on this page:** 6
**Page:** 1
### Author:  [@Falco](https://meta.discourse.org/u/Falco)
#### Post date: [2016年二月19日 20:04 UTC](https://meta.discourse.org/t/private-message-page-performance/39865/1 "2016-02-19T20:04:41Z")
Continuing the discussion from [Improve performance of Topic load - ORDER BY RANDOM](https://meta.discourse.org/t/improve-performance-of-topic-load-order-by-random/25619/23):
The [added index](https://github.com/discourse/discourse/commit/20bee937b70e04aa5afb09635b8aa85866dee588) is great, but it excludes PMs :sadpanda:.
Since we have suggested topics on the PM page (since when?) the performance vary a **lot** (sub second to 30 secods) on this query (on a dedicated PostgreSQL 9.4 Server with 12GB RAM).
So what do you think @sam should we create another one for PMs? (That way we don’t bloat the original one, and doesn’t add penalties for small communities with less than 100k users/ 100k PMs)
EDIT: Here’s a sample:
```plaintext
Executing action: show
T+141.4 ms
Reader
12337.6 ms
lib/suggested_topics_builder.rb:29:in `add_results'
lib/topic_query.rb:85:in `list_suggested_for'
lib/topic_view.rb:298:in `suggested_topics'
app/serializers/topic_view_serializer.rb:90:in `details'
app/controllers/application_controller.rb:236:in `render_json_dump'
app/controllers/topics_controller.rb:580:in `block (2 levels) in perform_show_response'
app/controllers/topics_controller.rb:572:in `perform_show_response'
app/controllers/topics_controller.rb:89:in `show'
lib/middleware/anonymous_cache.rb:129:in `call'
config/initializers/100-quiet_logger.rb:10:in `call_with_quiet_assets'
config/initializers/100-silence_logger.rb:26:in `call'
lib/middleware/request_tracker.rb:73:in `call'
config/initializers/000-rails-master-polyfills.rb:23:in `call'
lib/scheduler/defer.rb:85:in `process_client'
SELECT "topics".* FROM "topics" LEFT JOIN topic_users tu ON topics.id = tu.topic_id AND tu.user_id = 1 WHERE ("topics"."deleted_at" IS NULL) AND (topics.archetype = 'private_message') AND (topics.id IN (
SELECT topic_id
FROM topic_allowed_users
WHERE user_id = 1
)) AND (topics.created_at >= '2006-02-19 20:06:16.135577') AND (tu.last_read_post_number IS NULL) AND (COALESCE(tu.notification_level, 2) >= 2) AND (topics.id NOT IN (30983)) AND "topics"."visible" = $1 ORDER BY topics.bumped_at DESC LIMIT 5
```
---
### Author:  [@codinghorror](https://meta.discourse.org/u/codinghorror)
#### Post date: [2016年二月19日 23:55 UTC](https://meta.discourse.org/t/private-message-page-performance/39865/2 "2016-02-19T23:55:55Z")
Aha yes good catch – @sam we should extend the index.
---
### Author:  [@sam](https://meta.discourse.org/u/sam)
#### Post date: [2016年二月23日 00:12 UTC](https://meta.discourse.org/t/private-message-page-performance/39865/3 "2016-02-23T00:12:20Z")
It’s a completely different query though, agree it needs to be optimised but not sure that index will sort it.
In particular, “suggested” searches for stuff you have access to by joining multiple tables.
Also searches for previous messages with overlap of participants
It is complicated work
---
### Author:  [@Falco](https://meta.discourse.org/u/Falco)
#### Post date: [2016年二月23日 13:55 UTC](https://meta.discourse.org/t/private-message-page-performance/39865/4 "2016-02-23T13:55:26Z")
Created the following index:
```plaintext
CREATE INDEX index_private_message_topics_on_created_at_and_visible
ON topics
USING btree
(created_at, visible)
WHERE deleted_at IS NULL AND archetype::text = 'private_message'::text;
```
And it indeed doesn’t work :sadpanda:.
If I get something that works I will report here.
---
### Author:  [@Falco](https://meta.discourse.org/u/Falco)
#### Post date: [2016年三月14日 16:12 UTC](https://meta.discourse.org/t/private-message-page-performance/39865/5 "2016-03-14T16:12:09Z")
Sam,
It looks like the query is only slow for me!
It’s because the “Welcome to Discourse” messages came from my user, so 30% (112.000) of the rows from `topic_users` are on my user\_id.
For other users, the query is crazy fast!
And after trying many indexes and query modifications it’s really tricky to get good performance.
Maybe lazy loading the suggested topics would be a good way? It would make the topic render faster (3 or so less to wait) and be more forgiving of slowness on suggestions. Of course is a very bigger refactor, but just saving my findings here.
---
### Author:  [@tgxworld](https://meta.discourse.org/u/tgxworld)
#### Post date: [2017年九月13日 14:30 UTC](https://meta.discourse.org/t/private-message-page-performance/39865/6 "2017-09-13T14:30:26Z")
I got some nice improvement out of this
[https://github.com/discourse/discourse/commit/28148197d6467cdc7469409f961c00d4e32f4c41](https://github.com/discourse/discourse/commit/28148197d6467cdc7469409f961c00d4e32f4c41)