Private Message Page Performance

Dev
1

Continuing the discussion from Improve performance of Topic load - ORDER BY RANDOM:

The added index 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:

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
3 Likes
2

Aha yes good catch – @sam we should extend the index.

1 Like
3

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

3 Likes
4

Created the following index:

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.

5

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.

2 Likes
6

I got some nice improvement out of this

7 Likes