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