Class: Gitlab::Pagination::Keyset::InOperatorOptimization::QueryBuilder

Inherits:
Object
  • Object
show all
Defined in:
lib/gitlab/pagination/keyset/in_operator_optimization/query_builder.rb

Overview

rubocop: disable CodeReuse/ActiveRecord

Constant Summary collapse

RECURSIVE_CTE_NAME = 
'recursive_keyset_cte'

Instance Method Summary collapse

Constructor Details

#initialize(scope:, array_scope:, array_mapping_scope:, finder_query: nil, values: {}) ⇒ QueryBuilder

This class optimizes slow database queries (PostgreSQL specific) where the IN SQL operator is used with sorting.

Arguments: scope - ActiveRecord::Relation supporting keyset pagination array_scope - ActiveRecord::Relation for the IN subselect array_mapping_scope - Lambda for connecting scope with array_scope finder_query - ActiveRecord::Relation for finding one row by the passed in cursor values values - keyset cursor values (optional)

Example ActiveRecord query: Issues in the namespace hierarchy

scope = Issue .where(project_id: Group.find(9970).all_projects.select(:id)) .order(:created_at, :id) .limit(20);

Optimized version:

scope = Issue.where({}).order(:created_at, :id) # base scope array_scope = Group.find(9970).all_projects.select(:id) array_mapping_scope = -> (id_expression) { Issue.where(Issue.arel_table.eq(id_expression)) }

finding the record by id is good enough, we can ignore the created_at_expression

finder_query = -> (created_at_expression, id_expression) { Issue.where(Issue.arel_table.eq(id_expression)) }

Gitlab::Pagination::Keyset::InOperatorOptimization::QueryBuilder.new( scope: scope, array_scope: array_scope, array_mapping_scope: array_mapping_scope, finder_query: finder_query ).execute.limit(20)

Raises:



42
43
44
45
46
47
48
49
50
51
52
53
54
55
 
# File 'lib/gitlab/pagination/keyset/in_operator_optimization/query_builder.rb', line 42

def initialize(scope:, array_scope:, array_mapping_scope:, finder_query: nil, values: {})
  @scope, success = Gitlab::Pagination::Keyset::SimpleOrderBuilder.build(scope)

  raise(UnsupportedScopeOrder) unless success

  @order = Gitlab::Pagination::Keyset::Order.extract_keyset_order_object(scope)
  @array_scope = array_scope
  @array_mapping_scope = array_mapping_scope
  @values = values
  @model = @scope.model
  @table_name = @model.table_name
  @arel_table = @model.arel_table
  @finder_strategy = finder_query.present? ? Strategies::RecordLoaderStrategy.new(finder_query, model, order_by_columns) : Strategies::OrderValuesLoaderStrategy.new(model, order_by_columns)
end

Instance Method Details

#execute ⇒ Object 



57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
 
# File 'lib/gitlab/pagination/keyset/in_operator_optimization/query_builder.rb', line 57

def execute
  selector_cte = Gitlab::SQL::CTE.new(:array_cte, array_scope)

  cte = Gitlab::SQL::RecursiveCTE.new(RECURSIVE_CTE_NAME, union_args: { remove_duplicates: false, remove_order: false })
  cte << initializer_query
  cte << data_collector_query

  q = cte
    .apply_to(model.where({})
    .with(selector_cte.to_arel))
    .select(finder_strategy.final_projections)
    .where("count <> 0") # filter out the initializer row

  model.select(Arel.star).from(q.arel.as(table_name))
end