Re: Remove inner joins based on foreign keys - Mailing list pgsql-hackers

On Sat, 21 Mar 2026 at 15:47, Richard Guo <guofenglinux@gmail.com> wrote:
> Currently, the planner can remove useless left joins if the join
> condition cannot match more than one RHS row, and the RHS rel is not
> referenced above the join.  I'd like to propose a similar optimization
> for inner joins.

I tried this many years ago and it was pretty much a dead end with how
the current foreign key implementation deferring the cascade of the
foreign key until the end of the query.

There's plenty of discussion. See [1] and [2]. In particular, read and
follow along from what Heikki mentions in [3]. You should read all of
that and understand it to prevent prompting the same discussions all
over again.

It doesn't look like your patch has anything to protect against any of
the issues mentioned in [1], so I assume you weren't aware of that
work.

David

[1]
https://www.postgresql.org/message-id/flat/CAApHDvpDXXvKE%2B%3Dug1kA--nKfa%3DbjrjvK8Gp9G8UYwv6nHckVg%40mail.gmail.com#2f33cb1e196e863eba0e1376084cdf4d
[2] https://www.postgresql.org/message-id/CAApHDvocUEYdt1uT%2BDLDPs2xEu%3Dv3qJGT6HeXKonQM4rY_OsSA%40mail.gmail.com
[3] https://www.postgresql.org/message-id/54240C51.8040304%40vmware.com