Proposal: subquery_where helper
What does this MR do and why?
This MR introduces the .subquery_where
AR method which provides flexibility about how a certain subquery should be formulated thus allowing us to alter the query planner behaviour on a case-by-case basis.
Normally, you'd write a group-level query like this:
Issue.where(title: 'test').where(project_id: Group.find(9970).all_projects.select(:id))
ActiveRecord will generate an IN ()
subquery.
The alternative syntax:
inner_scope = Group.find(9970).all_projects.select(:id)
Issue
.where(title: test)
.subquery_where(:group_hierarchy, column: Issue.arel_table[:project_id], scope: inner_scope, strategy: :in)
The snippet above produces the same query. The currently supported strategies:
-
IN ()
subquery -
EXISTS
query - Pull the subquery into a CTE and do an
IN ()
subquery.
The benefit of the approach is that we could alter the strategy depending on the feature. Let's say we have the AR scope defined with in
strategy and we want to use EXISTS
for a particular feature:
# IssuesController
def index
IssuesFinder
.new()
.execute
.use_subquery_strategy(:group_hierarchy, strategy: :exists) # rewrite the strategy because in this case EXISTS produces a better execution plan
.limit(20)
# WHERE EXISTS (SELECT ...)
end
For code that is hard to reach (deeply nested classes, finders), we could do some sort of Thread[:current]
based hinting:
def index
use_subquery_strategy(:group_hierarchy, strategy: :exists) do
load_records
end
end
How to set up and validate locally
Numbered steps to set up and validate the change are strongly suggested.
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Related to #384551 (closed)