From: Andrey Lepikhov <a.lepikhov@postgrespro.ru>
To: _@ericsheng.com, pgsql-bugs@lists.postgresql.org, PG Bug reporting form <noreply@postgresql.org>
Subject: Re: BUG #17544: Join order for INNER JOIN ... USING with GROUP BY on join column affects selected query plan
Date: Tue, 12 Jul 2022 11:34:09 +0500
Message-ID: <ddd9555b-96d4-ca8d-8b04-f6fec2b90124@postgrespro.ru> (raw)
In-Reply-To: <17544-ebd06b00b8836a04@postgresql.org>
References: <17544-ebd06b00b8836a04@postgresql.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: a.lepikhov@postgrespro.ru, noreply@postgresql.org
Subject: Re: BUG #17544: Join order for INNER JOIN ... USING with GROUP BY on join column affects selected query plan
In-Reply-To: <ddd9555b-96d4-ca8d-8b04-f6fec2b90124@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox