A deep dive into PostgreSQL's enable_nestloop GUC, explaining how nested loop joins work, when they excel (small outer relation with indexed inner lookups in OLTP), and when they catastrophically fail (bad cardinality estimates causing hundreds of thousands of inner loops). The key insight is that a disastrous nested loop is almost always a symptom of a bad row count estimate, not a bad join algorithm choice. The EXPLAIN (ANALYZE) signature to look for is a large 'loops=' value with a huge gap between estimated and actual rows on the outer side. The correct fix is to improve statistics via ANALYZE, raising default_statistics_target, or using CREATE STATISTICS for correlated columns — not leaving enable_nestloop = off globally, which distorts all plans and can make things worse.
