Re: BUG #17544: Join order for INNER JOIN ... USING with GROUP BY on join column affects selected query plan

On 7/10/22 09:52, PG Bug reporting form wrote:
> All join orders for inner joins give semantically equivalent results, and
> documentation at https://www.postgresql.org/docs/current/explicit-joins.html
> indicates that the planner should explore all join orders unless there are
> too many tables, so I would have expected the join order here to be
> immaterial to the query plan chosen.
> 
> 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. The same difference can be observed running:
> 
>      EXPLAIN (ANALYZE, TIMING) SELECT groups.group_id, BOOL_AND(finished)
> FROM tasks INNER JOIN groups ON tasks.group_id = groups.group_id GROUP BY
> groups.group_id, priority ORDER BY priority ASC LIMIT 10;
>      (the slow plan)
> 
>      EXPLAIN (ANALYZE, TIMING) SELECT tasks.group_id, BOOL_AND(finished) FROM
> tasks INNER JOIN groups ON tasks.group_id = groups.group_id GROUP BY
> tasks.group_id, priority ORDER BY priority ASC LIMIT 10;
>      (the fast plan) 
Documentation speaks the truth - optimizer checks all join permutations. 
But USING clause doesn't pull both group_id vars and implicitly chooses 
only one according to an algorithm, described in 
parse_clause.c::buildMergedJoinVar(). So, because you use group_id in 
upper GROUP BY, optimizer is limited to specific set of strategies, 
because it must use the same grouping variable, as implicitly chosen in 
the JOIN USING clause.

-- 
Regards
Andrey Lepikhov
Postgres Professional