Docuemnt in-operator usage with JOIN columns
What does this MR do and why?
This MR documents how to use the in-operator optimization with JOIN
queries. One small change was required for the query generator:
When the original scope contains a CTE, copy it over to the base query.
Example usage snippet:
MergeRequests::ComplianceViolation.include(FromUnion)
cte_query = MergeRequests::ComplianceViolation
.where('merged_at > ?', '2022-03-09 03:30:45.197747')
.joins("INNER JOIN merge_request_metrics on merge_request_metrics.merge_request_id=merge_requests_compliance_violations.merge_request_id")
.select('merge_request_metrics.target_project_id AS metrics_project_id', 'merge_request_metrics.merged_at AS metrics_merged_at', 'merge_request_metrics.merge_request_id AS metrics_merge_request_id', 'merge_requests_compliance_violations.id')
cte = Gitlab::SQL::CTE.new(:compliance_cte, cte_query, materialized: false)
order = Gitlab::Pagination::Keyset::Order.build([
Gitlab::Pagination::Keyset::ColumnOrderDefinition.new(
attribute_name: 'metrics_merged_at',
order_expression: MergeRequests::ComplianceViolation.arel_table[:metrics_merged_at].desc,
sql_type: 'timestamp without time zone',
nullable: :nulls_last,
distinct: false
),
Gitlab::Pagination::Keyset::ColumnOrderDefinition.new(
attribute_name: 'metrics_merge_request_id',
order_expression: MergeRequests::ComplianceViolation.arel_table[:metrics_merge_request_id].desc,
sql_type: 'bigint',
nullable: :not_nullable,
distinct: true
)
])
scope = cte.apply_to(MergeRequests::ComplianceViolation.where({}).reorder(order))
opts = {
scope: scope,
array_scope: Project.where(namespace_id: Group.find(9970).self_and_descendants.select(:id)).select(:id),
array_mapping_scope: -> (id_expression) { MergeRequests::ComplianceViolation.where(MergeRequests::ComplianceViolation.arel_table[:metrics_project_id].eq(id_expression)) }
}
records = Gitlab::Pagination::Keyset::InOperatorOptimization::QueryBuilder
.new(**opts)
.execute
.limit(20)
.to_a
Plan: https://explain.depesz.com/s/uCxx
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.
Edited by Adam Hegyi