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