> What is your Postgres version? > Do you have correct statistics on this tables? > Please show yours execution plans with buffers i.e. explain (analyze,buffers) ... >
The difference is in the top of plans. As we see, hashjoin time is practically the same. But fast plan uses hashagg first and only 43k rows require sorting. Slow plan dominated by sorting 900k rows.
I wonder if increasing cpu_tuple_cost will help. As cost difference between two plans is negligible now.