Why PostgreSQL Ignored Our Index (and What the Planner Was Thinking)

Fabien Martinet's article explores the intricacies of PostgreSQL's cost-based optimizer and its decision-making process, particularly why it sometimes opts for sequential scans over index scans in large tables. At Mergify, where tables often contain millions of rows, the organization experienced cases where PostgreSQL ignored indexes, resulting in increased API latencies. By understanding how the planner evaluates execution plans and the influence of factors like stale statistics, weak correlation, and cost model mismatches, they were able to optimize queries effectively. They found that using SQLAlchemy and techniques such as ORDER BY clauses, adjusting cost constants, and ensuring frequent statistics refreshes could significantly enhance query performance. The article emphasizes the importance of treating PostgreSQL's planner as a partner in query optimization, advocating for a strategy of conversational optimization that involves analyzing and responding to the planner's cost outputs to achieve consistent high performance.