RE: Plan a filtering inner join as a semijoin

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 <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Sent: Tuesday, August 25, 2026 8:31 PM
To: William Bernbaum <wbernbaum(at)dwdev(dot)com>
Cc: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Re: Plan a filtering inner join as a semijoin

William Bernbaum <wbernbaum(at)dwdev(dot)com> 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