Class: Dexter::Indexer

Inherits:
Object
  • Object
show all
Includes:
Logging
Defined in:
lib/dexter/indexer.rb

Constant Summary

Constants included from Logging

Logging::COLOR_CODES

Instance Method Summary collapse

Methods included from Logging

#colorize, #log, #output

Constructor Details

#initialize(options) ⇒ Indexer

Returns a new instance of Indexer.



5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 
# File 'lib/dexter/indexer.rb', line 5

def initialize(options)
  @create = options[:create]
  @tablespace = options[:tablespace]
  @log_level = options[:log_level]
  @exclude_tables = options[:exclude]
  @include_tables = Array(options[:include].split(",")) if options[:include]
  @log_sql = options[:log_sql]
  @log_explain = options[:log_explain]
  @min_time = options[:min_time] || 0
  @min_calls = options[:min_calls] || 0
  @analyze = options[:analyze]
  @min_cost_savings_pct = options[:min_cost_savings_pct].to_i
  @options = options
  @mutex = Mutex.new

  if server_version_num < 110000
    raise Dexter::Abort, "This version of Dexter requires Postgres 11+"
  end

  check_extension

  execute("SET lock_timeout = '5s'")
end

Instance Method Details

#process_queries(queries) ⇒ Object 



35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
 
# File 'lib/dexter/indexer.rb', line 35

def process_queries(queries)
  # reset hypothetical indexes
  reset_hypothetical_indexes

  tables = Set.new(database_tables + materialized_views)

  # map tables without schema to schema
  no_schema_tables = {}
  search_path_index = Hash[search_path.map.with_index.to_a]
  tables.group_by { |t| t.split(".")[-1] }.each do |group, t2|
    no_schema_tables[group] = t2.sort_by { |t| [search_path_index[t.split(".")[0]] || 1000000, t] }[0]
  end

  # add tables from views
  view_tables = database_view_tables
  view_tables.each do |v, vt|
    view_tables[v] = vt.map { |t| no_schema_tables[t] || t }
  end

  # fully resolve tables
  # make sure no views in result
  view_tables.each do |v, vt|
    view_tables[v] = vt.flat_map { |t| view_tables[t] || [t] }.uniq
  end

  # filter queries from other databases and system tables
  queries.each do |query|
    # add schema to table if needed
    query.tables = query.tables.map { |t| no_schema_tables[t] || t }

    # substitute view tables
    new_tables = query.tables.flat_map { |t| view_tables[t] || [t] }.uniq
    query.tables_from_views = new_tables - query.tables
    query.tables = new_tables

    # check for missing tables
    query.missing_tables = !query.tables.all? { |t| tables.include?(t) }
  end

  # set tables
  tables = Set.new(queries.reject(&:missing_tables).flat_map(&:tables))

  # must come after missing tables set
  if @include_tables
    include_set = Set.new(@include_tables)
    tables.keep_if { |t| include_set.include?(t) || include_set.include?(t.split(".")[-1]) }
  end

  if @exclude_tables.any?
    exclude_set = Set.new(@exclude_tables)
    tables.delete_if { |t| exclude_set.include?(t) || exclude_set.include?(t.split(".")[-1]) }
  end

  # remove system tables
  tables.delete_if { |t| t.start_with?("information_schema.", "pg_catalog.") }

  queries.each do |query|
    query.candidate_tables = !query.missing_tables && query.tables.any? { |t| tables.include?(t) }
  end

  # analyze tables if needed
  analyze_tables(tables) if tables.any? && (@analyze || @log_level == "debug2")

  # create hypothetical indexes and explain queries
  if tables.any?
    # process in batches to prevent "hypopg: not more oid available" error
    # https://hypopg.readthedocs.io/en/rel1_stable/usage.html#configuration
    queries.select(&:candidate_tables).each_slice(500) do |batch|
      create_hypothetical_indexes(batch)
    end
  end

  # see if new indexes were used and meet bar
  new_indexes = determine_indexes(queries, tables)

  # display and create new indexes
  show_and_create_indexes(new_indexes, queries)
end

#process_stat_statements ⇒ Object 



29
30
31
32
33
 
# File 'lib/dexter/indexer.rb', line 29

def process_stat_statements
  queries = stat_statements.map { |q| Query.new(q) }.sort_by(&:fingerprint).group_by(&:fingerprint).map { |_, v| v.first }
  log "Processing #{queries.size} new query fingerprints"
  process_queries(queries)
end