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
app/controllers/topics_controller.rb:580:in `block (2 levels) in perform_show_response'
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 (
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