[Bug]: Comment search is slow when sorting by published

LemmyNet / lemmy

🐀 A link aggregator and forum for the fediverse

https://join-lemmy.org

GNU Affero General Public License v3.0

13.18k stars 871 forks source link

[Bug]: Comment search is slow when sorting by published #4851

Closed dullbananas closed 1 week ago

dullbananas commented 3 months ago

Requirements

[X] Is this a bug report? For questions or discussions use https://lemmy.ml/c/lemmy_support
[X] Did you check to see if this issue already exists?
[X] Is this only a single bug? Do not put multiple bugs in one issue.
[X] Do you agree to follow the rules in our Code of Conduct?
[X] Is this a backend issue? Use the lemmy-ui repo for UI / frontend issues.

Summary

Any search query with comments and with New sort is very slow

Steps to Reproduce

Do a search for comments or all
Set sort to new

Technical Details

🫘

Version

0.19.5

Lemmy Instance URL

https://lemmy.ca

dessalines commented 3 months ago

I see we have separate indexes for both the content and published desc, but we might need to add some combined indexes for several of these, in the same way we do for the post_aggregates table.

Indexes: "comment_pkey" PRIMARY KEY, btree (id)
"idx_comment_ap_id" UNIQUE CONSTRAINT, btree (ap_id)
"idx_comment_content_trigram" gin (content gin_trgm_ops)
"idx_comment_creator" btree (creator_id)
"idx_comment_language" btree (language_id)
"idx_comment_post" btree (post_id)
"idx_comment_published" btree (published DESC)
"idx_path_gist" gist (path)

Nutomic commented 1 week ago

Tested this on lemmy.ml and lemmy.ca. In both cases the API call takes around 400ms, which is consistent with other endpoints /api/v3/post/list. So there seems to be no need for any changes.