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
In-reply-to: <17544-ebd06b00b8836a04@postgresql.org>
References: <17544-ebd06b00b8836a04@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Sun, 10 Jul 2022 04:52:26 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1662280.1657648346.1@sss.pgh.pa.us>
Date: Tue, 12 Jul 2022 13:52:26 -0400
Message-ID: <1662281.1657648346@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1662281.1657648346%40sss.pgh.pa.us>
Precedence: bulk

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