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
Date: Mon, 24 May 2021 10:11:39 -0400
Message-ID: <885508.1621865499@sss.pgh.pa.us> (raw)
In-Reply-To: <CAN-AqA4MTY34V9nt66YrHE75ZaCpFanqcoTyL3ESbEQMX_Gz-Q@mail.gmail.com>
References: <CAN-AqA4MTY34V9nt66YrHE75ZaCpFanqcoTyL3ESbEQMX_Gz-Q@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, philip@hasura.io
Subject: Re: Optimising outer joins in the presence of non-nullable references
In-Reply-To: <885508.1621865499@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox