Confirm a missing index with EXPLAIN ANALYZE and index inspection
When a listing or search query feels slow, adding an index is an attractive immediate step. The safer, more reliable workflow starts with EXPLAIN (ANALYZE) and a careful comparison of planner estimates to actual execution. This short guide explains an implementation-focused approach and what you can expect.
Implementation
- Capture the exact slow query with realistic parameters and run EXPLAIN (ANALYZE, FORMAT JSON). Use the JSON form for deterministic parsing and automated checks.
- Read the plan node by node. Compare estimated rows to actual rows; identify the slow child node. Nested loop inner scans, Sort nodes, and Seq Scans on large tables are common hotspots.
- Inspect existing indexes (pg_indexes or pg_catalog). Read the column order and any included columns; match the index’s leading columns against equality/range predicates and check whether the index ordering satisfies ORDER BY to avoid Sort.
Expected behavior
- Seq Scan on a selective predicate over a large table is a strong signal an index could help — but only after you run ANALYZE. Stale statistics can mislead the planner.
- Index-only scans with nonzero Heap Fetches indicate the index is not covering: adding included columns or changing the projection may reduce heap visits.
- Large mismatches between estimated and actual rows often point to statistics problems. Updating statistics or tuning statistics targets can be more effective than adding an index.
Limitations
- EXPLAIN ANALYZE executes the statement; for data-modifying queries run it inside a rollbackable transaction if you must avoid persistence.
- This guidance focuses on index shape (column order + ordering) and statistics. It does not automatically generate production-ready DDL for functional, expression, or partial indexes.
- Application-level latency (client rendering, network) is outside server-side EXPLAIN's scope — always combine server-side plans with before-and-after measurements in the app context.
Use the article’s small JSON-plan analyzer as a checklist: it flags sequential scans, index presence, Heap Fetches, and large estimate mismatches so you can prioritize ANALYZE, a covering index, or a query rewrite — then re-run EXPLAIN ANALYZE to confirm the result.
(Example implementation is inserted by the site as a verified code block.)
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