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