Message-ID: <ddd9555b-96d4-ca8d-8b04-f6fec2b90124@postgrespro.ru>
Date: Tue, 12 Jul 2022 11:34:09 +0500
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.9.1
Subject: Re: BUG #17544: Join order for INNER JOIN ... USING with GROUP BY on
 join column affects selected query plan
Content-Language: en-US
To: _@ericsheng.com, pgsql-bugs@lists.postgresql.org,
 PG Bug reporting form <noreply@postgresql.org>
References: <17544-ebd06b00b8836a04@postgresql.org>
From: Andrey Lepikhov <a.lepikhov@postgrespro.ru>
Organization: Postgres Professional
In-Reply-To: <17544-ebd06b00b8836a04@postgresql.org>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/ddd9555b-96d4-ca8d-8b04-f6fec2b90124%40postgrespro.ru>
Precedence: bulk

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