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