Module: PgQueryOptimizer::ClassMethods

Defined in:
lib/pg_query_optimizer.rb

Constant Summary collapse

DEFAULT_SETTINGS = 
{
  max_parallel_workers_per_gather: 4,
  parallel_setup_cost: 1000,
  parallel_tuple_cost: 0.1,
  min_parallel_table_scan_size: '8MB',
  min_parallel_index_scan_size: '512kB'
}.freeze

Instance Method Summary collapse

Instance Method Details

#enable_parallel_execution ⇒ Object 



50
51
52
53
54
55
56
57
 
# File 'lib/pg_query_optimizer.rb', line 50

def enable_parallel_execution
  store_current_values
  with_suppressed_logs do
    DEFAULT_SETTINGS.each do |key, value|
      ActiveRecord::Base.connection.execute("SET #{key} = '#{value}'")
    end
  end
end

#pg_optimize(max_workers: 4) ⇒ Object 



63
64
65
66
67
68
69
70
71
72
73
74
 
# File 'lib/pg_query_optimizer.rb', line 63

def pg_optimize(max_workers: 4)
  verify_postgresql_adapter!
  if sufficient_resources?
    enable_parallel_execution
    yield
  else
    # If resources are insufficient, run the query without parallel execution
    yield
  end
ensure
  reset_parallel_execution 
end

#reset_parallel_execution ⇒ Object 



59
60
61
 
# File 'lib/pg_query_optimizer.rb', line 59

def reset_parallel_execution
  restore_previous_values
end

#restore_previous_values ⇒ Object 



34
35
36
37
38
39
40
 
# File 'lib/pg_query_optimizer.rb', line 34

def restore_previous_values
  with_suppressed_logs do
    @previous_values.each do |key, value|
      ActiveRecord::Base.connection.execute("SET #{key} = '#{value}'")
    end
  end
end

#store_current_values ⇒ Object 



22
23
24
25
26
27
28
29
30
31
32
 
# File 'lib/pg_query_optimizer.rb', line 22

def store_current_values
  with_suppressed_logs do
    @previous_values = {
      max_parallel_workers_per_gather: ActiveRecord::Base.connection.execute("SHOW max_parallel_workers_per_gather").first["max_parallel_workers_per_gather"],
      parallel_setup_cost: ActiveRecord::Base.connection.execute("SHOW parallel_setup_cost").first["parallel_setup_cost"],
      parallel_tuple_cost: ActiveRecord::Base.connection.execute("SHOW parallel_tuple_cost").first["parallel_tuple_cost"],
      min_parallel_table_scan_size: ActiveRecord::Base.connection.execute("SHOW min_parallel_table_scan_size").first["min_parallel_table_scan_size"],
      min_parallel_index_scan_size: ActiveRecord::Base.connection.execute("SHOW min_parallel_index_scan_size").first["min_parallel_index_scan_size"]
    }
  end
end

#sufficient_resources? ⇒ Boolean

Returns:

  • (Boolean) 


42
43
44
45
46
47
48
 
# File 'lib/pg_query_optimizer.rb', line 42

def sufficient_resources?
  # Implement checks to ensure system has sufficient resources
  total_memory = `free -m | grep Mem: | awk '{print $2}'`.to_i
  required_memory = 1024 # required memory in MB

  total_memory >= required_memory
end

#verify_postgresql_adapter! ⇒ Object 



76
77
78
79
80
 
# File 'lib/pg_query_optimizer.rb', line 76

def verify_postgresql_adapter!
  unless ActiveRecord::Base.connection.adapter_name.downcase == 'postgresql'
    raise UnsupportedDatabaseError, "PgQueryOptimizer only supports PostgreSQL"
  end
end

#with_suppressed_logs ⇒ Object 



82
83
84
85
86
87
88
89
90
 
# File 'lib/pg_query_optimizer.rb', line 82

def with_suppressed_logs
  original_logger = ActiveRecord::Base.logger
  begin
    ActiveRecord::Base.logger = Logger.new(nil)  # Suppress logs
    yield
  ensure
    ActiveRecord::Base.logger = original_logger  # Restore original logger
  end
end