Investigate use of pg_hint_plan to improve issue searches when using complex sorts
Update: pg_hint_plan
has been installed in `#database_lab. I'm updating this ticket to reflect the next step of validating the hinting approach. If, upon investigation, we find that the approach won't help us here, then we can revisit the initial context from this thread.
Done when
-
Investigated the use of pg_hint_plan
to improve performance on issue search with complex sorts -
If successful, created additional issue(s) with implementation plan. -
If unsuccessful, relay findings here and go back to the drawing board on possible improvements.
This is related to #34030 (closed)
When using complex sorts (sorting by something that's not a column on the issues table), we cannot use the CTE optimization that we do so that the project_id
index is prioritized. And when the query planner selects the trigram indexes on issues.title
and issues.description
the query times out because of the large number of issues on GitLab.com.
From the previous discussion in #34030 (closed), I suggested the use of the
pg_hint_plan
extension to force the use of theproject_id
index instead of the trigram index. Now that we're on Rails 6, this should be a bit easier to implement on the Rails side: https://blog.bigbinary.com/2019/07/30/rails-6-supports-optimizer-hints.html