Tom, Fair; completely agreed. In my initial testing, I couldn't overcome a case where pinning a filter rel to the RHS deferred a qual that should have been first. And the selectivity (surely obvious to some; not to me) outweighed any row reduction benefit.
That's why I narrowed it to a single-relation RHS (and gave up the prize - the measured hand-written EXISTS). One clarification.. not a defense. This isn't quite the reverse of reduce_unique_semijoins(). This only pays when the RHS is not unique. I think the real defect (but please correct me if I'm wrong) is that it makes a rewrite-time decision about something that should be decided on cost. I came to this conclusion late, but decided to share anyway because I believe there's a path forward. At a high level: build a second rel; carry it through the join search; let it compete on cost. More optionality for the planner. -----Original Message----- From: Tom Lane <[email protected]> Sent: Tuesday, August 25, 2026 8:31 PM To: William Bernbaum <[email protected]> Cc: [email protected] Subject: Re: Plan a filtering inner join as a semijoin William Bernbaum <[email protected]> writes: > Recently I improved a dynamic SQL compiler (an ORM-like layer) using a > pattern that seems to generalize to the Postgres planner. I taught > the layer to emit an EXISTS subquery when the target relation only > served to filter, rather than fanning out a join whose extra rows were > discarded. > That led me into the join elimination machinery, and to the attached > draft patch. It adds convert_joins_to_semijoins(), which recognizes > an inner join that only restricts which rows survive and adds a > JOIN_SEMI SpecialJoinInfo for the filtering relation. I find myself a bit astonished by this proposal. Normally we try to convert semijoins to inner joins in order to gain join-order flexibility. I can't see a good reason to go in the other direction. There might be a reason to install some special case for the query pattern you have in mind, but I really doubt that this particular special case is the best option. regards, tom lane
