Tricking PostgresSQL into Using an Insane – but 200x Faster – Query Plan

The article discusses the optimization of a PostgreSQL query used by Spacelift, a CI/CD platform specializing in Infrastructure as Code. The original query was slow and consumed significant processing time due to incorrect row count estimation by PostgreSQL. By leveraging domain knowledge and rewriting the query to scan active Runs first and then filtering them based on relevance, the author achieved a vast improvement in performance, reducing the execution time from 20 seconds to 100 milliseconds. The article highlights the importance of understanding query plans and using domain knowledge for optimizing queries without adding unnecessary indices or denormalizing data models.