Re: Optimising outer joins in the presence of non-nullable references

Philip Lykke Carlsen <philip@hasura.io> writes:
> My question then is, shouldn't the inner and outer join queries be
> semantically equivalent when the columns we are joining on are
> non-nullable foreign keys?

Maybe, but no such knowledge is built into the planner.

> Is there some corner case I'm not considering?

I'm a little suspicious whether it's actually a safe assumption to
make, in view of the fact that enforcement of FKs is delayed till
end-of-statement or even end-of-transaction.  Thus, the relationship
isn't necessarily valid at every instant.

> Would it be a good addition to postgres if it could detect this and
> produce a plan that exploits the indices?

Maybe.  Aside from semantic correctness issues, the big question
would be whether the detection could be made cheap enough to not
be a drag on the 99.99% of cases where it's not helpful.

            regards, tom lane