DEV: Add include_pms option to TopicQuery (PR #10647)

This is intended for use by plugins which are building their own topic lists, and want to include PMs alongside regular topics (e.g. discourse-assign). It does not get used directly in core.

GitHub

One thing I noticed while working on search is that querying for all the topics can be expensive and hard to optimize. How does the performance on this query look on a fairly large site?

Testing the query used on the group assigned tab. On Meta, with my user_id and the ‘admins’ user group, it takes approx 20ms with include_pms, and 10ms without include_pms.

So it is slower (understandably), but I think for the intended use case it is still performant enough.

The query generated looks like

SELECT "topics".*
FROM "topics"
LEFT OUTER JOIN topic_users AS tu ON (topics.id = tu.topic_id
                                      AND tu.user_id = 23968)
WHERE ((topics.archetype <> 'private_message')
       OR "topics"."archetype" = 'private_message'
       AND (topics.id IN
              (SELECT topic_id
               FROM topic_allowed_users
               WHERE user_id = 23968
               UNION ALL SELECT tg.topic_id
               FROM topic_allowed_groups tg
               JOIN group_users gu ON gu.user_id = 23968
               AND gu.group_id = tg.group_id)))
  AND (topics.deleted_at IS NULL)
  AND (topics.id IN
         (SELECT topic_id
          FROM topic_custom_fields
          WHERE name = 'assigned_to_id'
            AND value IN
              (SELECT user_id::varchar(255)
               FROM group_users
               WHERE group_id = '1')))
ORDER BY topics.bumped_at DESC
LIMIT 30

Also note that we are already using this kind of query in the discourse-assign plugin. This PR is refactoring the logic into core, rather than the plugin. (see https://github.com/discourse/discourse-assign/pull/111)

Do you think this approach is ok performance-wise? If not, do you have any suggestions on how I can investigate improving it?

I do something like this in events too, so will probably be useful here too.

@davidtaylorhq Thank you for the explanation, the query looks ok to me perf wise. I just wanted to point out that there may be potential pit falls with searching through everything especially once we start joining against other tables like we do for search.