Continuing the discussion from Improve performance of Topic load - ORDER BY RANDOM:
The added index is great, but it excludes PMs .
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