Reviewed by Aditya Kumar · Last reviewed 2026-03-24
**Systematic approach**: (1) **Profile**: EXPLAIN ANALYZE—identify seq scans on large tables, costly hash joins, sort spills. (2) **Baseline**: Current runtime, row counts, data distribution. (3) **Index**: Add B-tree/hash on filter and join columns; verify usage in plan. (4)...
Systematic approach: (1) Profile: EXPLAIN ANALYZE—identify seq scans on large tables, costly hash joins, sort spills. (2) Baseline: Current runtime, row counts, data distribution. (3) Index: Add B-tree/hash on filter and join columns; verify usage in plan. (4) Statistics: Run ANALYZE; planner accuracy affects join order. (5) Rewrite: Replace correlated subqueries with JOINs or window functions; push predicates. (6) Partition: Prune by partition key. (7) Tune: Work_mem, parallel workers. Scalability: At billion-row scale, missing partition filter can scan 100× more data. Cost: Index maintenance trade-off; materialized views for repeated aggregates.
Red Flag: Adding indexes without measuring—can slow writes. Pro-Move: 'We used pg_stat_statements to find top 5 queries, ran EXPLAIN ANALYZE, added one composite index—P99 dropped from 12s to 200ms; we measured write impact at +3%.'
According to DataEngPrep.tech, this is one of the most frequently asked SQL interview questions, reported at 1 company. DataEngPrep.tech maintains an editor-reviewed database of 1,863 data engineering interview questions across 7 categories.