Class: RailsVitals::Analyzers::ExplainAnalyzer

Inherits:

Object

• Object
• RailsVitals::Analyzers::ExplainAnalyzer

Defined in:

lib/rails_vitals/analyzers/explain_analyzer.rb

Defined Under Namespace

Classes: PlanNode, Result

Constant Summary

SUPPORTED_ENVIRONMENTS =

%w[development test].freeze

COLOR_DANGER =

"#fc8181"

COLOR_HEALTHY =

"#68d391"

COLOR_WARNING =

"#f6ad55"

COLOR_INFO =

"#9f7aea"

COLOR_NEUTRAL =

"#a0aec0"

COLOR_COOL =

"#76e4f7"

NODE_METADATA =

{
  "Seq Scan" => {
    risk: :danger,
    color: COLOR_DANGER,
    label: "Sequential Scan",
    explanation: "PostgreSQL read every row in the table to find matches. " \
                 "No index was used. This gets linearly slower as the table grows, " \
                 "at 1M rows it scans 1M rows for every query hit.",
    fix_type: :missing_index
  },
  "Index Scan" => {
    risk: :healthy,
    color: COLOR_HEALTHY,
    label: "Index Scan",
    explanation: "PostgreSQL used an index to locate matching rows directly. " \
                 "Fast and consistent regardless of table size.",
    fix_type: nil
  },
  "Index Only Scan" => {
    risk: :healthy,
    color: COLOR_HEALTHY,
    label: "Index Only Scan",
    explanation: "PostgreSQL satisfied the query entirely from the index " \
                 "without touching the table. The most efficient scan type.",
    fix_type: nil
  },
  "Bitmap Heap Scan" => {
    risk: :neutral,
    color: COLOR_WARNING,
    label: "Bitmap Heap Scan",
    explanation: "PostgreSQL built a bitmap of matching index entries, then " \
                 "fetched the actual rows. Common with IN (...) conditions and " \
                 "multiple indexes. Generally acceptable.",
    fix_type: nil
  },
  "Bitmap Index Scan" => {
    risk: :neutral,
    color: COLOR_WARNING,
    label: "Bitmap Index Scan",
    explanation: "Builds a bitmap of row locations from an index. " \
                 "Works in conjunction with Bitmap Heap Scan.",
    fix_type: nil
  },
  "Hash Join" => {
    risk: :neutral,
    color: COLOR_INFO,
    label: "Hash Join",
    explanation: "Builds a hash table from one side of the JOIN, then probes " \
                 "it for each row from the other side. Common with includes() " \
                 "and eager_load(). Efficient for large datasets.",
    fix_type: nil
  },
  "Nested Loop" => {
    risk: :warning,
    color: COLOR_WARNING,
    label: "Nested Loop",
    explanation: "For each row on the outer side, scans the inner side. " \
                 "Fast when the inner side is small or uses an index. " \
                 "Dangerous when both sides are large, O(n²) complexity.",
    fix_type: :check_join_indexes
  },
  "Merge Join" => {
    risk: :neutral,
    color: COLOR_COOL,
    label: "Merge Join",
    explanation: "Joins two pre-sorted datasets by merging them in order. " \
                 "Efficient when both sides are already sorted on the join key.",
    fix_type: nil
  },
  "Aggregate" => {
    risk: :neutral,
    color: COLOR_NEUTRAL,
    label: "Aggregate",
    explanation: "Computes an aggregate function (COUNT, SUM, AVG, etc.) " \
                 "over a set of rows. Generated by .count, .sum, .average in Rails.",
    fix_type: nil
  },
  "Hash" => {
    risk: :neutral,
    color: COLOR_NEUTRAL,
    label: "Hash",
    explanation: "Builds a hash table in memory for use by a Hash Join node above it.",
    fix_type: nil
  },
  "Sort" => {
    risk: :warning,
    color: COLOR_WARNING,
    label: "Sort",
    explanation: "PostgreSQL sorted rows in memory (or on disk if large). " \
                 "Generated by ORDER BY on an unindexed column. " \
                 "An index on the sort column eliminates this node entirely.",
    fix_type: :index_sort_column
  },
  "Limit" => {
    risk: :healthy,
    color: COLOR_HEALTHY,
    label: "Limit",
    explanation: "Stops fetching rows once the LIMIT is reached. " \
                 "Good, always paginate large result sets.",
    fix_type: nil
  }
}.freeze

ALLOWED_FUNCTIONS =

%w[
  COUNT SUM AVG MIN MAX
  COALESCE NULLIF
  NOW CURRENT_TIMESTAMP CURRENT_DATE CURRENT_TIME LOCALTIMESTAMP LOCALTIME
  EXTRACT DATE_PART DATE_TRUNC AGE
  UPPER LOWER TRIM LENGTH SUBSTRING CONCAT REPLACE POSITION REVERSE
  ROUND FLOOR CEIL ABS MOD POWER SQRT
  GREATEST LEAST CAST
  ROW_NUMBER RANK DENSE_RANK LAG LEAD FIRST_VALUE LAST_VALUE NTILE
  ARRAY_AGG STRING_AGG
  JSON_EXTRACT_PATH_TEXT JSONB_EXTRACT_PATH
].freeze

FUNCTION_CALL_PATTERN =

/\b(?!#{ALLOWED_FUNCTIONS.join('|')}\b)[a-zA-Z_]\w*\s*\(/

Class Method Summary

• .analyze(sql, binds: []) ⇒ Object
• .build_node(plan) ⇒ Object
• .build_suggestions(warnings, root_node) ⇒ Object
• .contains_function_calls?(sql) ⇒ Boolean
• .count_rows_examined(plan) ⇒ Object
• .dry_run(sql, binds: []) ⇒ Object
• .exec_in_read_only_transaction(sql) ⇒ Object
• .extract_fk_from_filter(filter) ⇒ Object
• .extract_warnings(node, warnings = []) ⇒ Object
• .interpret(result) ⇒ Object
• .postgresql? ⇒ Boolean
• .select_query?(sql) ⇒ Boolean
• .substitute_binds(sql, binds) ⇒ Object
• .supported_environment? ⇒ Boolean
• .unsupported_env ⇒ Object
• .unsupported_sql ⇒ Object

Class Method Details

.analyze(sql, binds: []) ⇒ Object

# File 'lib/rails_vitals/analyzers/explain_analyzer.rb', line 152

def self.analyze(sql, binds: [])
  return unsupported_env unless supported_environment?
  return unsupported_sql unless select_query?(sql)

  safe_sql = substitute_binds(sql, binds)
  has_function_calls = contains_function_calls?(safe_sql)

  dry_run_result = dry_run(safe_sql)
  return dry_run_result if dry_run_result.error

  raw = exec_in_read_only_transaction(
    "EXPLAIN (FORMAT JSON, ANALYZE true, BUFFERS false) #{safe_sql}"
  )

  plan_json = JSON.parse(raw)
  root_plan = plan_json.first["Plan"]
  exec_time = plan_json.first["Execution Time"]&.round(2)

  root_node = build_node(root_plan)
  warnings = extract_warnings(root_node)
  suggestions = build_suggestions(warnings, root_node)

  if has_function_calls
    warnings << {
      type: :function_call,
      severity: :info,
      message: "Query contains function calls — executed inside a read-only transaction that prevents writes"
    }
  end

  result = Result.new(
    sql: safe_sql,
    plan: root_node,
    warnings: warnings,
    suggestions: suggestions,
    total_cost: root_plan["Total Cost"]&.round(2),
    actual_time_ms: exec_time,
    rows_examined: count_rows_examined(root_plan),
    function_calls: has_function_calls,
    dry_run_result: dry_run_result,
    error: nil
  )

  result.interpretation = interpret(result)
  result
rescue => e
  Result.new(error: e.message, sql: sql, plan: nil,
             warnings: [], suggestions: [], total_cost: nil,
             actual_time_ms: nil, rows_examined: nil)
end

.build_node(plan) ⇒ Object

# File 'lib/rails_vitals/analyzers/explain_analyzer.rb', line 276

def self.build_node(plan)
  meta = NODE_METADATA[plan["Node Type"]] || {
    risk: :neutral, color: COLOR_NEUTRAL,
    label: plan["Node Type"], explanation: nil, fix_type: nil
  }

  PlanNode.new(
    node_type: plan["Node Type"],
    relation: plan["Relation Name"],
    alias_name: plan["Alias"],
    startup_cost: plan["Startup Cost"]&.round(2),
    total_cost: plan["Total Cost"]&.round(2),
    actual_startup_ms: plan["Actual Startup Time"]&.round(2),
    actual_total_ms: plan["Actual Total Time"]&.round(2),
    plan_rows: plan["Plan Rows"],
    actual_rows: plan["Actual Rows"],
    filter: plan["Filter"],
    index_name: plan["Index Name"],
    index_condition: plan["Index Cond"],
    rows_removed_by_filter: plan["Rows Removed by Filter"],
    loops: plan["Actual Loops"],
    plan_width: plan["Plan Width"],
    metadata: meta,
    children: Array(plan["Plans"]).map { |p| build_node(p) }
  )
end

.build_suggestions(warnings, root_node) ⇒ Object

# File 'lib/rails_vitals/analyzers/explain_analyzer.rb', line 334

def self.build_suggestions(warnings, root_node)
  suggestions = []

  warnings.each do |w|
    case w[:type]
    when :sequential_scan
      fk = extract_fk_from_filter(w[:filter])
      suggestions << {
        severity: :danger,
        title: "Add index on #{w[:table]}#{fk ? ".#{fk}" : ""}",
        body: "PostgreSQL scanned #{w[:rows].to_i + w[:removed].to_i} rows " \
              "to return #{w[:rows]} — no index was used on #{w[:table]}.",
        migration: fk ?
          "add_index :#{w[:table]}, :#{fk}" :
          "add_index :#{w[:table]}, :COLUMN_NAME",
        command: "rails g migration Add#{fk&.camelize || 'Index'}To#{w[:table].camelize}"
      }
    when :sort_without_index
      suggestions << {
        severity: :warning,
        title: "Add index for ORDER BY",
        body: "A Sort node appeared — PostgreSQL sorted rows in memory because " \
              "the ORDER BY column has no index.",
        migration: "add_index :#{w[:table]}, :SORT_COLUMN",
        command: nil
      }
    when :large_nested_loop
      suggestions << {
        severity: :warning,
        title: "Large Nested Loop — check join indexes",
        body: "A Nested Loop processed #{w[:rows]} rows. " \
              "Ensure the inner side of the join has an index on the join column.",
        migration: nil,
        command: nil
      }
    end
  end

  suggestions
end

.contains_function_calls?(sql) ⇒ Boolean

Returns:

• (Boolean)

# File 'lib/rails_vitals/analyzers/explain_analyzer.rb', line 235

def self.contains_function_calls?(sql)
  clean = sql.gsub(/'.*?(?:''|')/, "").gsub(/".*?"/, "")
  clean.match?(FUNCTION_CALL_PATTERN)
end

.count_rows_examined(plan) ⇒ Object

# File 'lib/rails_vitals/analyzers/explain_analyzer.rb', line 382

def self.count_rows_examined(plan)
  children = Array(plan["Plans"])

  if children.empty?
    # Leaf node, actual table read
    plan["Actual Rows"].to_i + plan["Rows Removed by Filter"].to_i
  else
    # Intermediate node, recurse only into children
    children.sum { |p| count_rows_examined(p) }
  end
end

.dry_run(sql, binds: []) ⇒ Object

# File 'lib/rails_vitals/analyzers/explain_analyzer.rb', line 203

def self.dry_run(sql, binds: [])
  safe_sql = substitute_binds(sql, binds)

  raw = exec_in_read_only_transaction(
    "EXPLAIN (FORMAT JSON, ANALYZE false, BUFFERS false) #{safe_sql}"
  )

  plan_json = JSON.parse(raw)
  root_plan = plan_json.first["Plan"]

  Result.new(
    sql: safe_sql,
    total_cost: root_plan["Total Cost"]&.round(2),
    plan: build_node(root_plan),
    error: nil
  )
rescue => e
  Result.new(error: e.message, sql: sql, plan: nil,
             warnings: [], suggestions: [], total_cost: nil,
             actual_time_ms: nil, rows_examined: nil)
end

.exec_in_read_only_transaction(sql) ⇒ Object

# File 'lib/rails_vitals/analyzers/explain_analyzer.rb', line 240

def self.exec_in_read_only_transaction(sql)
  if postgresql?
    connection = ActiveRecord::Base.connection
    connection.execute("BEGIN")
    connection.execute("SET TRANSACTION READ ONLY")
    begin
      result = connection.execute(sql)
      connection.execute("COMMIT")
      result.first["QUERY PLAN"]
    rescue => e
      connection.execute("ROLLBACK") rescue nil
      raise e
    end
  else
    ActiveRecord::Base.connection.execute(sql).first["QUERY PLAN"]
  end
end

.extract_fk_from_filter(filter) ⇒ Object

# File 'lib/rails_vitals/analyzers/explain_analyzer.rb', line 375

def self.extract_fk_from_filter(filter)
  return nil unless filter

  match = filter.match(/\((\w+)\s*=\s*/i)
  match ? match[1] : nil
end

.extract_warnings(node, warnings = []) ⇒ Object

# File 'lib/rails_vitals/analyzers/explain_analyzer.rb', line 303

def self.extract_warnings(node, warnings = [])
  case node.node_type
  when "Seq Scan"
    warnings << {
      type: :sequential_scan,
      table: node.relation,
      filter: node.filter,
      rows: node.actual_rows,
      removed: node.rows_removed_by_filter,
      severity: :danger
    }
  when "Sort"
    warnings << {
      type: :sort_without_index,
      table: node.relation,
      severity: :warning
    }
  when "Nested Loop"
    if node.actual_rows.to_i > 1000
      warnings << {
        type: :large_nested_loop,
        rows: node.actual_rows,
        severity: :warning
      }
    end
  end

  node.children.each { |child| extract_warnings(child, warnings) }
  warnings
end

.interpret(result) ⇒ Object

# File 'lib/rails_vitals/analyzers/explain_analyzer.rb', line 410

def self.interpret(result)
  return nil if result.error

  parts = []

  if result.warnings.any? { |w| w[:type] == :sequential_scan }
    parts << "Sequential scan detected — missing index is causing full table reads"
  end

  if result.function_calls
    parts << "Query uses function calls — executed in a read-only transaction that prevents writes"
  end

  if result.actual_time_ms.to_f > 100
    parts << "query took #{result.actual_time_ms}ms — above the 100ms warning threshold"
  end

  if result.plan&.plan_width.to_i > 200
    parts << "row width is #{result.plan.plan_width}B — consider using .select(:col) instead of SELECT *"
  end

  parts.empty? ? "Plan looks healthy — index used, no warnings." : parts.join(". ")
end

.postgresql? ⇒ Boolean

Returns:

• (Boolean)

# File 'lib/rails_vitals/analyzers/explain_analyzer.rb', line 258

def self.postgresql?
  ActiveRecord::Base.connection.adapter_name.match?(/postgresql/i)
rescue
  false
end

.select_query?(sql) ⇒ Boolean

Returns:

• (Boolean)

# File 'lib/rails_vitals/analyzers/explain_analyzer.rb', line 231

def self.select_query?(sql)
  sql.strip.match?(/\ASELECT/i)
end

.substitute_binds(sql, binds) ⇒ Object

# File 'lib/rails_vitals/analyzers/explain_analyzer.rb', line 264

def self.substitute_binds(sql, binds)
  # Replace $1, $2 placeholders with safe literal values
  result = sql.dup
  binds.each_with_index do |bind, i|
    value = bind.is_a?(String) ? "'#{bind.gsub("'", "''")}'" : bind.to_s
    result = result.gsub("$#{i + 1}", value)
  end

  # Replace any remaining ? placeholders with NULL
  result.gsub("?", "NULL")
end

.supported_environment? ⇒ Boolean

Returns:

• (Boolean)

# File 'lib/rails_vitals/analyzers/explain_analyzer.rb', line 227

def self.supported_environment?
  SUPPORTED_ENVIRONMENTS.include?(Rails.env.to_s)
end

.unsupported_env ⇒ Object

# File 'lib/rails_vitals/analyzers/explain_analyzer.rb', line 394

def self.unsupported_env
  Result.new(
    error: "EXPLAIN is only available in development and test environments.",
    sql: nil, plan: nil, warnings: [], suggestions: [],
    total_cost: nil, actual_time_ms: nil, rows_examined: nil
  )
end

.unsupported_sql ⇒ Object

# File 'lib/rails_vitals/analyzers/explain_analyzer.rb', line 402

def self.unsupported_sql
  Result.new(
    error: "EXPLAIN is only available for SELECT queries.",
    sql: nil, plan: nil, warnings: [], suggestions: [],
    total_cost: nil, actual_time_ms: nil, rows_examined: nil
  )
end