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 = 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)

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

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

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

You can read more about it here [Beibei] How do I know if the statistics of a Postgres table are up to date? postgresql, statistics, analyzer, vacuum asked by Beibei on 08:17PM - 01 Aug 11 UTC

Slow (over 3 sec) SQL query

Product Support Self-hosting

codinghorror (Jeff Atwood) 20 בינואר,‏ 2017,‏ 9:10pm 5

You can read more about it here

נושא		תגובות	צפיות
Slow SQL query causes homepage to load in 2-4 sec Product Support	15	1870	7 בפברואר,‏ 2018
App/models/topic_tracking_state - `report` - long response times? Development slow-sql	6	1964	26 בפברואר,‏ 2016
PostgreSQL runaway IO Self-hosting	35	3888	3 במרץ,‏ 2021
Long loading times for user summary page with slow database Self-hosting unsupported-install	17	2195	29 באוקטובר,‏ 2019
Posting is taking multiple seconds Product Support	21	1478	15 במאי,‏ 2020

Slow (over 3 sec) SQL query

נושאים קשורים