Performance optimisation tips

Hi there,
We have pretty large site (PG base is 97GB) + we store 256GB of images.

It runs on Digital Ocean server — 6 vCPUs / 16GB / 320GB Disk.

Is there any tips, tricks, scripts to optimise PG, rebuild something to speed things up?

THANKS

Some time Latest page takes ages to draw.

Executing action: latest
T+125.6 ms
Reader
7237.4 ms

app/models/topic_tracking_state.rb:301:in `report'
app/controllers/application_controller.rb:628:in `preload_current_user_data'
app/controllers/application_controller.rb:428:in `preload_json'
app/controllers/application_controller.rb:404:in `block in with_resolved_locale'
app/controllers/application_controller.rb:404:in `with_resolved_locale'
lib/middleware/omniauth_bypass_middleware.rb:71:in `call'
lib/content_security_policy/middleware.rb:12:in `call'
lib/middleware/anonymous_cache.rb:368:in `call'
config/initializers/100-quiet_logger.rb:23:in `call'
config/initializers/100-silence_logger.rb:31:in `call'
lib/middleware/enforce_hostname.rb:23:in `call'
lib/middleware/request_tracker.rb:202:in `call'
SELECT 
           DISTINCT topics.id as topic_id,
           u.id as user_id,
           topics.created_at,
           topics.updated_at,
           topics.highest_staff_post_number AS highest_post_number,
           last_read_post_number,
           c.id as category_id,
           tu.notification_level,
           us.first_unread_at,
           GREATEST(
              CASE
              WHEN COALESCE(uo.new_topic_duration_minutes, 10080) = -1 THEN u.created_at
              WHEN COALESCE(uo.new_topic_duration_minutes, 10080) = -2 THEN COALESCE(
                u.previous_visit_at,u.created_at
              )
              ELSE ('2022-05-11 22:22:20.701444'::timestamp - INTERVAL '1 MINUTE' * COALESCE(uo.new_topic_duration_minutes, 10080))
              END, u.created_at, '2022-05-03 03:50:38'
           ) AS treat_as_new_topic_start_date
FROM topics
JOIN users u on u.id = 1
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
LEFT JOIN dismissed_topic_users ON dismissed_topic_users.topic_id = topics.id AND dismissed_topic_users.user_id = 1

WHERE u.id = 1 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, 10080) = -1 THEN u.created_at
                  WHEN COALESCE(uo.new_topic_duration_minutes, 10080) = -2 THEN COALESCE(u.previous_visit_at,u.created_at)
                  ELSE ('2022-05-11 22:22:20.699581'::timestamp - INTERVAL '1 MINUTE' * COALESCE(uo.new_topic_duration_minutes, 10080))
               END, u.created_at, '2022-05-03 03:50:38'))) AND (tu.last_read_post_number IS NULL) AND (COALESCE(tu.notification_level, 2) >= 2) AND topics.created_at > '2022-05-03 03:50:38' AND dismissed_topic_users.id IS NULL)) AND
      
      
      topics.deleted_at IS NULL AND
      
      NOT (
        last_read_post_number IS NULL AND
        (
          topics.category_id IN (SELECT "categories"."id" FROM "categories" LEFT JOIN categories categories2 ON categories2.id = categories.parent_category_id LEFT JOIN category_users ON category_users.category_id = categories.id AND category_users.user_id = 1 LEFT JOIN category_users category_users2 ON category_users2.category_id = categories2.id AND category_users2.user_id = 1 WHERE ((category_users.id IS NULL AND COALESCE(category_users2.notification_level, 1) = 0) OR COALESCE(category_users.notification_level, 1) = 0))
          AND tu.notification_level <= 1
        )
      )

UNION ALL

SELECT 
           DISTINCT topics.id as topic_id,
           u.id as user_id,
           topics.created_at,
           topics.updated_at,
           topics.highest_staff_post_number AS highest_post_number,
           last_read_post_number,
           c.id as category_id,
           tu.notification_level,
           us.first_unread_at,
           GREATEST(
              CASE
              WHEN COALESCE(uo.new_topic_duration_minutes, 10080) = -1 THEN u.created_at
              WHEN COALESCE(uo.new_topic_duration_minutes, 10080) = -2 THEN COALESCE(
                u.previous_visit_at,u.created_at
              )
              ELSE ('2022-05-11 22:22:20.701444'::timestamp - INTERVAL '1 MINUTE' * COALESCE(uo.new_topic_duration_minutes, 10080))
              END, u.created_at, '2022-05-03 03:50:38'
           ) AS treat_as_new_topic_start_date
FROM topics
JOIN users u on u.id = 1
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 = 1 AND
       topics.updated_at >= us.first_unread_at 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.deleted_at IS NULL AND
      
      NOT (
        last_read_post_number IS NULL AND
        (
          topics.category_id IN (SELECT "categories"."id" FROM "categories" LEFT JOIN categories categories2 ON categories2.id = categories.parent_category_id LEFT JOIN category_users ON category_users.category_id = categories.id AND category_users.user_id = 1 LEFT JOIN category_users category_users2 ON category_users2.category_id = categories2.id AND category_users2.user_id = 1 WHERE ((category_users.id IS NULL AND COALESCE(category_users2.notification_level, 1) = 0) OR COALESCE(category_users.notification_level, 1) = 0))
          AND tu.notification_level <= 1
        )
      )


 LIMIT 5000;

One of the reasons they pushed to pg13 was the speed ups that it offers. So that and rebuilding indexes as described in PostgreSQL 13 update is the first thing to do.