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.
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
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.