PG Bug reporting form <noreply@postgresql.org> writes:
> It seems like the join order of a simple INNER JOIN ... USING(...) affects
> query plan selection even when only joining two tables, when used with GROUP
> BY on the join column.
> ...
> Digging a bit deeper, this appears to be due to the USING clause causing the
> GROUP BY group_id to be rewritten to `groups.group_id` in the first query
> and `tasks.group_id` in the second query, and the former resulting in the
> simpler plan not being used.
Yeah. In principle that shouldn't matter, but a confluence of
peculiarities of this specific scenario and a perhaps-overly-aggressive
optimization on the GROUP BY clause prevent the planner from finding
the good plan when the JOIN USING variable is resolved as coming from
the same table as the other GROUP BY column. Technical details at [1].
I'm not sure whether we'll end up using the quick-hack patch shown there,
but if you're desperate for a fix you could apply that locally.
regards, tom lane
[1] https://www.postgresql.org/message-id/1657885.1657647073%40sss.pgh.pa.us