Class: QueryGuard::Suggest::IndexSuggester

Inherits:
Object
  • Object
show all
Defined in:
lib/query_guard/suggest/index_suggester.rb

Overview

Generates index suggestions for queries with missing index indicators. Conservative approach: only suggests when patterns match common cases. All suggestions include disclaimer that they're recommendations only.

Example:

suggester = IndexSuggester.new
suggestion = suggester.suggest_for_sequential_scan(
"SELECT * FROM users WHERE email = '[email protected]'",
table_name: "users"
)
# => {
#   suggested_index_sql: "CREATE INDEX idx_users_email ON users (email);",
#   explanation: "Index on email column for WHERE clause equality",
#   confidence: :medium,
#   columns: ["email"]
# }

Constant Summary collapse

CONFIDENCE_LEVELS = 
%i[high medium low].freeze

Instance Method Summary collapse

Constructor Details

#initialize ⇒ IndexSuggester

Returns a new instance of IndexSuggester.



24
25
26
 
# File 'lib/query_guard/suggest/index_suggester.rb', line 24

def initialize
  @extractors = PatternExtractors.new
end

Instance Method Details

#build_recommendation_text(suggestion) ⇒ String

Build neutral recommendation text for a suggestion

Parameters:

  • suggestion (Hash) —

    Suggestion from suggest_* methods

Returns:

  • (String) —

    User-facing recommendation text



97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
 
# File 'lib/query_guard/suggest/index_suggester.rb', line 97

def build_recommendation_text(suggestion)
  return nil unless suggestion

  text = suggestion[:explanation]
  text += " (confidence: #{suggestion[:confidence]})"
  text += "\n\nIMPORTANT: This is a recommendation based on query pattern analysis. "
  text += "Before creating the index:\n"
  text += "  1. Verify this index hasn't already been suggested elsewhere\n"
  text += "  2. Check index size impact and maintenance cost\n"
  text += "  3. Run EXPLAIN ANALYZE with and without the index\n"
  text += "  4. Consider selectivity of indexed columns\n"
  text += "  5. Test in development first\n\n"
  text += "Suggested SQL (REVIEW BEFORE RUNNING):\n#{suggestion[:suggested_index_sql]}"
  text
end

#suggest_for_complex_filter(sql, table_name:) ⇒ Hash?

Generate index suggestion for multi-column filter For WHERE with multiple equality conditions, suggest composite

Parameters:

  • sql (String) —

    SQL query

  • table_name (String) —

    Table being filtered

Returns:

  • (Hash, nil) —

    Suggestion hash or nil



69
70
71
72
73
74
75
76
77
78
79
 
# File 'lib/query_guard/suggest/index_suggester.rb', line 69

def suggest_for_complex_filter(sql, table_name:)
  return nil if sql.nil? || table_name.nil?

  where_cols = @extractors.extract_where_columns(sql)
  return nil if where_cols.length < 2

  # Conservative: only suggest composite for 2-3 columns
  return nil if where_cols.length > 3

  suggest_composite_index(table_name, where_cols, reason: "composite filter")
end

#suggest_for_expensive_sort(sql, table_name:) ⇒ Hash?

Generate index suggestion for expensive sort

Parameters:

  • sql (String) —

    SQL query

  • table_name (String) —

    Table being sorted

Returns:

  • (Hash, nil) —

    Suggestion hash or nil



52
53
54
55
56
57
58
59
60
61
 
# File 'lib/query_guard/suggest/index_suggester.rb', line 52

def suggest_for_expensive_sort(sql, table_name:)
  return nil if sql.nil? || table_name.nil?

  # Extract ORDER BY columns
  order_cols = @extractors.extract_order_by_columns(sql)
  return nil if order_cols.empty?

  # For sorts, index all ORDER BY columns in order
  suggest_composite_index(table_name, order_cols, reason: "sort")
end

#suggest_for_join(column_name, table_name:) ⇒ Hash

Generate index suggestion for JOIN condition Suggests index on join key in inner table

Parameters:

  • column_name (String) —

    Join column name

  • table_name (String) —

    Inner table name

Returns:

  • (Hash) —

    Suggestion hash



87
88
89
90
91
 
# File 'lib/query_guard/suggest/index_suggester.rb', line 87

def suggest_for_join(column_name, table_name:)
  return nil if column_name.nil? || table_name.nil?

  suggest_index(table_name, column_name, high_confidence: true, reason: "join condition")
end

#suggest_for_sequential_scan(sql, table_name:, filter_condition: nil) ⇒ Hash?

Generate index suggestion for sequential scan with filter

Parameters:

  • sql (String) —

    SQL query

  • table_name (String) —

    Table being scanned

  • filter_condition (String) (defaults to: nil) —

    WHERE clause filter (optional)

Returns:

  • (Hash, nil) —

    Suggestion hash or nil if no reasonable suggestion



34
35
36
37
38
39
40
41
42
43
44
45
 
# File 'lib/query_guard/suggest/index_suggester.rb', line 34

def suggest_for_sequential_scan(sql, table_name:, filter_condition: nil)
  return nil if sql.nil? || table_name.nil?

  # Extract columns from WHERE clause
  where_cols = @extractors.extract_where_columns(sql)
  return nil if where_cols.empty?

  # Use first column as primary index candidate
  # This is conservative: most benefit comes from first filter
  primary_col = where_cols.first
  suggest_index(table_name, primary_col, high_confidence: true)
end