Quick tutorial: confirm a missing index with EXPLAIN (ANALYZE, FORMAT JSON)

Question

Prerequisites

• Permission to run EXPLAIN (ANALYZE, FORMAT JSON) and to read pg_catalog/pg_indexes.
• The real query parameters and an understanding of expected result cardinality.

Implementation decision

• Run EXPLAIN (ANALYZE, FORMAT JSON) for the production-shaped query to capture per-node estimated rows, actual rows, node types, and any Heap Fetches.
• Inspect existing index definitions for the table and compare their leading columns to the query’s WHERE predicates and ORDER BY. Prefer multi-column indexes whose leading columns match the most selective predicates and whose trailing columns satisfy ordering to avoid Sort nodes.

Expected result

• Diagnostic outcomes typically fall into three cases: (1) inaccurate statistics — fix with ANALYZE or stats tuning; (2) no appropriate index exists — a targeted multi-column index can remove Seq Scans/Sort; (3) an index exists but is not covering — adding included columns or changing the projection can reduce Heap Fetches.
• After one focused change, re-run EXPLAIN ANALYZE with the same parameters and compare node types, actual row counts, and wall time to validate the effect.

One limitation

• This procedure diagnoses index shape and statistic issues but does not produce production-ready DDL for expression/partial/index-class edge cases. Collations, operator classes, or functional indexes require manual design and validation.

The article includes a compact JSON-plan analyzer that flags sequential scans, index presence, Heap Fetches, and estimate mismatches. Use it as an actionable checklist: ANALYZE first if estimates are wrong; design a covering index only when the plan shows heap fetches or an absent matching index; always measure the same query before and after the change.

Working example

export function analyzeExplainAnalyze(planJson, {rowEstimateTolerance = 3, heapFetchThreshold = 1} = {}){
  // planJson: a parsed EXPLAIN (ANALYZE, FORMAT JSON) output: array with one plan object
  // Returns a diagnostics object describing signs that a missing or wrong index is slowing the query
  if (!Array.isArray(planJson) || planJson.length === 0) throw new TypeError('planJson must be a non-empty array (EXPLAIN JSON output)')
  const root = planJson[0].Plan ?? planJson[0]

  const findings = []
  let totalEstimated = 0
  let totalActual = 0

  function walk(node){
    if (!node) return

    // Normalize node type
    const nodeTypeRaw = node['Node Type'] || node['Node-Type'] || node['Plan Node Type'] || ''
    const nodeType = (typeof nodeTypeRaw === 'string') ? nodeTypeRaw.toLowerCase() : ''

    // Detect sequential scan or index scans (cover common variations)
    if (nodeType.includes('seq scan')){
      findings.push({type: 'seq_scan', message: `Sequential scan detected: ${nodeTypeRaw}`, node})
    }
    if (nodeType.includes('index scan') || nodeType.includes('index only') || nodeType.includes('index-only') || nodeType.includes('index-only scan') || nodeType.includes('indexonly') || nodeType.includes('bitmap index') || nodeType.includes('bitmap heap') ){
      findings.push({type: 'index_scan_present', message: `Index scan node present: ${nodeTypeRaw}`, node})
    }

    // Detect heap fetches
    // Common EXPLAIN JSON uses 'Heap Fetches' or 'Heap Fetches' exactly; be defensive about key name
    const heapFetchKeys = ['Heap Fetches', 'HeapFetches', 'Heap_Fetches', 'heap_fetches']
    for (const k of heapFetchKeys){
      const v = node[k]
      if (typeof v === 'number' && v > heapFetchThreshold){
        findings.push({type: 'heap_fetches', message: `Heap fetches: ${v}`, node})
        break
      }
    }

    // Compare planner estimates and actuals when available under common keys
    const estRows = (typeof node['Plan Rows'] === 'number' ? node['Plan Rows'] : (typeof node['PlanRows'] === 'number' ? node['PlanRows'] : undefined))
    const actRows = (typeof node['Actual Rows'] === 'number' ? node['Actual Rows'] : (typeof node['ActualRows'] === 'number' ? node['ActualRows'] : undefined))

    if (typeof estRows === 'number' && typeof actRows === 'number'){
      totalEstimated += estRows
      totalActual += actRows
      if (estRows === 0 && actRows > 0){
        findings.push({type: 'zero_estimate', message: `Planner estimated 0 rows but ${actRows} were returned (bad stats)`, node})
      } else if (estRows > 0 && actRows / estRows > rowEstimateTolerance){
        findings.push({type: 'estimation_mismatch', message: `Actual rows (${actRows}) exceed estimate (${estRows}) by > ${rowEstimateTolerance}x`, node, ratio: actRows/estRows})
      }
    }

    // Recurse into children
    const children = node.Plans || node['Plans'] || node['plan nodes'] || []
    if (Array.isArray(children)) for (const c of children) walk(c)
  }

  walk(root)

  // Summarize
  const hasSeq = findings.some(f => f.type === 'seq_scan')
  const hasIndex = findings.some(f => f.type === 'index_scan_present')
  const hasHeapFetches = findings.some(f => f.type === 'heap_fetches')
  const hasEstimation = findings.some(f => f.type === 'estimation_mismatch' || f.type === 'zero_estimate')

  const suggestions = []
  if (hasSeq && !hasIndex){
    suggestions.push('No index usage found in the plan. Consider creating an index that matches WHERE and ORDER BY patterns (create an index), or ensure ANALYZE has been run so planner statistics are current.')
  }
  if (hasHeapFetches){
    suggestions.push('Heap fetches are present. Consider a covering (multi-column) index or include needed columns so an index-only scan can be used.')
  }
  if (hasEstimation){
    suggestions.push('Planner row estimates disagree with actuals. Run ANALYZE on the table and consider increasing statistics targets for relevant columns.')
  }
  if (!hasSeq && hasIndex && !hasHeapFetches && !hasEstimation){
    suggestions.push('Plan shows index usage and no obvious heap fetches or estimation errors. Index-related issue is unlikely.')
  }

  return {
    findings,
    summary: {
      hasSeq,
      hasIndex,
      hasHeapFetches,
      hasEstimation,
      totalEstimated: totalEstimated || null,
      totalActual: totalActual || null
    },
    suggestions
  }
}

export function suggestIndexFromQuery({whereColumns = [], orderBy = []}, existingIndexes = []){
  // Very small helper to recommend an index key order: leading columns from predicates then ordering columns
  // existingIndexes: array of {name, columns: ['a','b']} — we only compare simple equality
  const needed = [...new Set([...whereColumns, ...orderBy])]
  const matches = existingIndexes.find(idx => {
    if (!Array.isArray(idx.columns)) return false
    // good match if idx.columns startsWith needed
    for (let i=0;i<needed.length;i++){
      if (idx.columns[i] !== needed[i]) return false
    }
    return needed.length > 0
  })
  if (matches) return {found: true, index: matches}
  return {found: false, suggestion: needed.length ? `CREATE INDEX ON table (${needed.join(', ')})` : 'No predicate/order columns provided; review query.'}
}

Read the full article: https://chriseugenerodriguez.com/blog/postgresql-explain-index-inspection