@blakeyrat said:
By "self corrects" you mean "deletes information relevant to the user without their knowledge or consent", then... yes.
If you are going for an inbox ONE MILLION and objecting that we do not support that ...
Sorry.
Or if you really want to... let me know how to rewrite this without a limit so it does not rape us.
WITH x AS (
SELECT u.id AS user_id,
topics.id AS topic_id,
topics.created_at,
highest_post_number,
last_read_post_number,
c.id AS category_id,
tu.notification_level
FROM topics
JOIN users u on u.id = 576
JOIN user_stats AS us ON us.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 = 576 AND
topics.archetype <> 'private_message' AND
(("topics"."deleted_at" IS NULL AND tu.last_read_post_number < topics.highest_post_number AND COALESCE(tu.notification_level, 1) >= 2) OR ("topics"."deleted_at" IS NULL AND topics.created_at >= GREATEST(CASE
WHEN COALESCE(u.new_topic_duration_minutes, 2880) = -1 THEN u.created_at
WHEN COALESCE(u.new_topic_duration_minutes, 2880) = -2 THEN COALESCE(u.previous_visit_at,u.created_at)
ELSE ('2015-09-09 21:12:17.314182'::timestamp - INTERVAL '1 MINUTE' * COALESCE(u.new_topic_duration_minutes, 2880))
END, us.new_since) 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 = 576 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 = 576 AND
cu.category_id = topics.category_id AND
cu.notification_level = 0)
ORDER BY topics.bumped_at DESC ) SELECT * FROM x LIMIT 500
Limit can be set by admins ... but at 500 it's already taking 150ms here for my account, so yeah we could bump it up to 100000 in site settings here and people with one million unread can wait an hour to get the front page.
So win.