Re: Removing unneeded self joins

On 26/6/2025 07:40, Michael Paquier wrote:
> Anyway, it seems to me that we may need to do something here before
> the release. Note that if the consensus is "you should update your
> module and not rely on the past behavior", I'm OK with that. I just
> wanted to raise the issue before this goes GA. And well, I have a
> pretty big pool of users that rely on this module, so..
Thanks for the report and detailed explanation!
Before diving into the pg_hint_plan code, I wonder why you don't have
similar issues with the remove_useless_joins. We intentionally designed
SJE coupled with the left-join removal feature to avoid such type of
complaints:

CREATE TABLE test (x integer PRIMARY KEY);
EXPLAIN (COSTS OFF)
SELECT t1.* FROM test t1 LEFT JOIN test t2 ON (t1.x=t2.x);

/*
QUERY PLAN
---------------------
Seq Scan on test t1
(1 row)
*/

It seems that this join removal is also beyond the pg_hint_plan control ...

--
regards, Andrei Lepikhov