From: Tom Lane <tgl@sss.pgh.pa.us>
To: Philip Lykke Carlsen <philip@hasura.io>
cc: pgsql-performance@postgresql.org
Subject: Re: Optimising outer joins in the presence of non-nullable references
In-reply-to: 
 <CAN-AqA4MTY34V9nt66YrHE75ZaCpFanqcoTyL3ESbEQMX_Gz-Q@mail.gmail.com>
References: 
 <CAN-AqA4MTY34V9nt66YrHE75ZaCpFanqcoTyL3ESbEQMX_Gz-Q@mail.gmail.com>
Comments: In-reply-to Philip Lykke Carlsen <philip@hasura.io>
	message dated "Mon, 24 May 2021 14:10:24 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <885507.1621865499.1@sss.pgh.pa.us>
Date: Mon, 24 May 2021 10:11:39 -0400
Message-ID: <885508.1621865499@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/885508.1621865499%40sss.pgh.pa.us>
Precedence: bulk

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