The strange case of the underestimated Merge Join node
Summary
Dalibo investigates an optimizer oddity where a query's execution plan shifts between runs despite unchanged data. The first run uses a Merge Join with a high startup cost due to histogram-based selectivity estimation, while the second run uses a Nested Loop Join after index dead tuples affect get_actual_variable_endpoint. The article explains the PostgreSQL planner behavior, the role of histogram endpoints, and a patch that limits heap page reads to prevent planning time explosions. It also provides a reproduction script and methods to verify hypotheses via Explain plan and setting enable_nestloop. The takeaway is that index statistics and planner behavior can cause plan instability, especially after batch processes and without recent ANALYZE/VACUUM.