From: Tom Lane <tgl@sss.pgh.pa.us>
To: _@ericsheng.com
Cc: pgsql-bugs@lists.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 13:52:26 -0400
Message-ID: <1662281.1657648346@sss.pgh.pa.us> (raw)
In-Reply-To: <17544-ebd06b00b8836a04@postgresql.org>
References: <17544-ebd06b00b8836a04@postgresql.org>
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
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: tgl@sss.pgh.pa.us, _@ericsheng.com, pgsql-bugs@lists.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: <1662281.1657648346@sss.pgh.pa.us>
* 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