Optimizing Correlated Subqueries with Semi Joins in PostgreSQL and YugabyteDB

Mark Peacock's blog post delves into optimizing correlated subqueries using Semi Joins in PostgreSQL and YugabyteDB. He encountered performance issues with a query due to inefficient execution of correlated subqueries, which involved nested loop overhead. By analyzing the execution plan with EXPLAIN ANALYZE, Peacock identified that restructuring the query to reposition the correlation predicate from the JOIN condition to the WHERE clause allowed PostgreSQL’s optimizer to recognize the EXISTS condition as eligible for a Semi Join. This structural change enabled more efficient execution by reducing overhead and improving index usage, ultimately allowing the database to filter rows earlier. The blog highlights the nuances between ANSI and pre-ANSI syntax, emphasizing that pre-ANSI syntax offers more flexibility in optimizing joins. Peacock's experience underscores the importance of understanding PostgreSQL's handling of EXISTS clauses and the benefits of query restructuring in achieving optimal performance.