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

[dullbananas]

Requirements

• Is this a bug report? For questions or discussions use https://lemmy.ml/c/lemmy_support
• Did you check to see if this issue already exists?
• Is this only a single bug? Do not put multiple bugs in one issue.
• Do you agree to follow the rules in our Code of Conduct?
• 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

1. Do a search for comments or all
2. Set sort to new

Technical Details

🫘

Version

0.19.5

Lemmy Instance URL

https://lemmy.ca

[dessalines]

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)