Philip Lykke Carlsen <philip@xxxxxxxxx> 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
