From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Philip Lykke Carlsen <philip(at)hasura(dot)io> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Optimising outer joins in the presence of non-nullable references |
Date: | 2021-05-24 14:11:39 |
Message-ID: | 885508.1621865499@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Philip Lykke Carlsen <philip(at)hasura(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Bob Jolliffe | 2021-05-24 16:22:28 | Re: transaction blocking on COMMIT |
Previous Message | Vijaykumar Jain | 2021-05-24 14:09:47 | Re: transaction blocking on COMMIT |