On LATERAL Joins

A new API endpoint at Mergify encountered performance issues when deployed to production, timing out due to inefficient SQL queries. Initially, a naive approach involved a subquery that scanned 660,000 rows to map SHAs to pull requests, which significantly slowed down the system as it constructed a comprehensive SHA-to-pull-request mapping before applying selective filters. This inefficiency was rectified by using a LATERAL JOIN, which allowed for selective filters to be applied first, drastically reducing the number of rows processed. By performing an indexed lookup for each row in the test metrics rather than a full table scan, the response time improved by a factor of 1000, demonstrating the impact of join order on query performance in PostgreSQL.