Slow (over 3 sec) SQL query


(Tomek) #1

I’m trying to optimize a multi-docker Discourse instance for a forum with over 3M posts.
Once loaded, Disourse is quite fast.
Problem is that the first page load (or full F5 refresh) takes 4-5 seconds.
There is one SQL query that is responsible for 3,9 of these 4 seconds.
This is consistent and happening on every F5 or first page in a new session.
Can you point me to some direction, how to deal with this?
Thanks!

Executing action: top
T+173.2 ms
Reader
3749.7 ms

lib/freedom_patches/active_record_base.rb:7:in `exec_sql'
lib/sql_builder.rb:67:in `exec'
lib/sql_builder.rb:104:in `map_exec'
app/models/topic_tracking_state.rb:178:in `report'
app/controllers/application_controller.rb:417:in `preload_current_user_data'
app/controllers/application_controller.rb:248:in `preload_json'
lib/middleware/anonymous_cache.rb:138:in `call'
config/initializers/100-quiet_logger.rb:13:in `call_with_quiet_assets'
config/initializers/100-silence_logger.rb:26:in `call'
lib/middleware/request_tracker.rb:73:in `call'
lib/scheduler/defer.rb:85:in `process_client'

    SELECT 
           u.id AS user_id,
           topics.id AS topic_id,
           topics.created_at,
           highest_staff_post_number highest_post_number,
           last_read_post_number,
           c.id AS category_id,
           tu.notification_level
    FROM topics
    JOIN users u on u.id = 29625
    JOIN user_stats AS us ON us.user_id = u.id
    JOIN user_options AS uo ON uo.user_id = u.id
    JOIN categories c ON c.id = topics.category_id
    LEFT JOIN topic_users tu ON tu.topic_id = topics.id AND tu.user_id = u.id
    WHERE u.id = 29625 AND
          topics.archetype <> 'private_message' AND
          ((1=0) OR ("topics"."deleted_at" IS NULL AND topics.created_at >= GREATEST(CASE
                  WHEN COALESCE(uo.new_topic_duration_minutes, 2880) = -1 THEN u.created_at
                  WHEN COALESCE(uo.new_topic_duration_minutes, 2880) = -2 THEN COALESCE(u.previous_visit_at,u.created_at)
                  ELSE ('2017-01-19 22:35:42.805122'::timestamp - INTERVAL '1 MINUTE' * COALESCE(uo.new_topic_duration_minutes, 2880))
               END, us.new_since, '2016-12-28 11:05:22.000000') AND tu.last_read_post_number IS NULL AND COALESCE(tu.notification_level, 2) >= 2)) AND
          (topics.visible OR u.admin OR u.moderator) AND
          topics.deleted_at IS NULL AND
          ( NOT c.read_restricted OR u.admin OR category_id IN (
              SELECT c2.id FROM categories c2
              JOIN category_groups cg ON cg.category_id = c2.id
              JOIN group_users gu ON gu.user_id = 29625 AND cg.group_id = gu.group_id
              WHERE c2.read_restricted )
          )
          AND NOT EXISTS( SELECT 1 FROM category_users cu
                          WHERE last_read_post_number IS NULL AND
                               cu.user_id = 29625 AND
                               cu.category_id = topics.category_id AND
                               cu.notification_level = 0)

UNION ALL

SELECT 
       u.id AS user_id,
       topics.id AS topic_id,
       topics.created_at,
       highest_staff_post_number highest_post_number,
       last_read_post_number,
       c.id AS category_id,
       tu.notification_level
FROM topics
JOIN users u on u.id = 29625
JOIN user_stats AS us ON us.user_id = u.id
JOIN user_options AS uo ON uo.user_id = u.id
JOIN categories c ON c.id = topics.category_id
LEFT JOIN topic_users tu ON tu.topic_id = topics.id AND tu.user_id = u.id
WHERE u.id = 29625 AND
      topics.archetype <> 'private_message' AND
      (("topics"."deleted_at" IS NULL AND tu.last_read_post_number < topics.highest_staff_post_number AND COALESCE(tu.notification_level, 1) >= 2) OR (1=0)) AND
      (topics.visible OR u.admin OR u.moderator) AND
      topics.deleted_at IS NULL AND
  ( NOT c.read_restricted OR u.admin OR category_id IN (
      SELECT c2.id FROM categories c2
      JOIN category_groups cg ON cg.category_id = c2.id
      JOIN group_users gu ON gu.user_id = 29625 AND cg.group_id = gu.group_id
      WHERE c2.read_restricted )
  )
  AND NOT EXISTS( SELECT 1 FROM category_users cu
                  WHERE last_read_post_number IS NULL AND
                       cu.user_id = 29625 AND
                       cu.category_id = topics.category_id AND
                       cu.notification_level = 0)

(Sam Saffron) #2

If this is an import, try backing up and restoring from backup, stats may be in a bad state


(Tomek) #3

It’s after import from IPB. I’ll try that.


(Tomek) #4

It helped, can you explain why this could be an issue?


(Jeff Atwood) #5

You can read more about it here