PERF: Improve database query perf when loading topics for a category. (PR #14416)

Instead of left joining the topics table against categories by filtering with “categories.id”, we can improve the query plan by filtering against topics.category_id first before joining which helps to reduce the number of rows in the topics table that has to be joined against the other tables.

GitHub

Can’t fab! here because a nested category cannot be created unless SiteSetting.max_category_nesting is set.

Testing for counts is insufficient. A wrong topic could be returned and the assertion will still pass.

Private messages are covered by the index_topics_on_timestamps_private index.

Nitpick: can you run annotate to update topic.rb too? Lots of changes to indexes happen in this file.

LGTM! :+1:

Nitpick: do you know if we have a convention on naming indexes? I see that in topic.rb most of them start with index_topics_on_....

O right I forgot about it :+1:

We’ve never had a convention for index name. Those that start with index_topics_on... are usually generated by ActiveRecord while the idx_ prefix or rather a custom index name is used when the index name generated by ActiveRecord is too long

There is little point in having anything after bumped_at, because bumped_at will probably be unique.

Good point. I always forget that ordering is important when creating a multiple column index.

@danielwaterworth I’ve updated the PR per your review :slight_smile:

You could set the site setting in fab!, but this is fine.

Did you try creating an index over (category_id, bumped_at) as well? Postgres should be able to take advantage of that for queries with categories.

That doesn’t necessarily have to be done in this PR though.

Did you try creating an index over (category_id, bumped_at) as well?

Yup I did but based on the queries I tested, PG didn’t use those.

Thank you for reviewing @danielwaterworth @udan11