The strange case of the underestimated Merge Join node

Summary

Brest, France, 12 January 2026 We recently encountered a strange optimizer behaviour, reported by one of our customers: Customer: “Hi Dalibo, we have a query that is very slow on the first execution after a batch process, and then very fast. We initially suspected a caching effect, but then we noticed that the execution plan was different.” Dalibo: “That’s a common issue. Autoanalyze didn’t have the opportunity to process the table after the batch job had finished, and before the first execution of the query. You should run the VACUUM ANALYZE command (or at least ANALYZE) immediately after your batch job.” Customer: “Yes, it actually solves the problem, but… your hypothesis is wrong. We looked at pg_stat_user_tables, and are certain that the tables were not vacuumed or analyzed between the slow and fast executions. We don’t have a production problem, but we would like to understand.” Dalibo: “That’s very surprising! we would also like to understand…” So let’s dive in! Execution plans The query is quite basic (table and column names have been anonymized): SELECT * FROM bar LEFT JOIN foo ON (bar.a = foo.a) WHERE id = 10744501 ORDER BY bar.x DESC, foo.x DESC; Here’s the plan of the first execution of the query after the batch job: QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=17039.22..17042.11 rows=1156 width=786) (actual time=89056.476..89056.480 rows=6 loops=1) Sort Key: bar.x DESC, foo.x DESC Sort Method: quicksort Memory: 25kB Buffers: shared hit=2255368 read=717581 dirtied=71206 written=11997 -> Merge Right Join (cost=2385.37..16980.41 rows=1156 width=786) (actual time=89056.428..89056.432 rows=6 loops=1) Inner Unique: true Merge Cond: (foo.a = bar.a) Buffers: shared hit=2255365 read=717581 dirtied=71206 written=11997 -> Index Scan using foo_fk1 on foo (cost=0.57..145690068.16 rows=80462556 width=734) (actual time=89050.555..89050.557 rows=1 loop...