Re: Bug in execution of EXISTS and IN clauses for large tables - Mailing list pgsql-bugs

From David Rowley
Subject Re: Bug in execution of EXISTS and IN clauses for large tables
Date
Msg-id CAApHDvpCKAe-7K-JfV3vYfvfTET7sjEEUFCArco3MqknmnpJYg@mail.gmail.com
Whole thread Raw
In response to RE: Bug in execution of EXISTS and IN clauses for large tables  (Martin Kos <martin.kos@molecularhealth.com>)
List pgsql-bugs
On Wed, 23 Feb 2022 at 09:29, Martin Kos <martin.kos@molecularhealth.com> wrote:
> Just to confirm (sorry if this is a trivial question) - the "exists" and "in" should never behave like an inner join,
right?

If the query planner is able first determine that the IN/EXISTs can be
executed as a semi-join, and it later can also find proofs that any
given outer side row of the semi-join can match to *at most* 1 inner
side row, then the planner will switch this to an inner join. This
allows more flexibility in the join orders, which can produce more
efficient plans.

David



pgsql-bugs by date:

Previous
From: Mark Murawski
Date:
Subject: Re: Bug plperl.c
Next
From: PG Bug reporting form
Date:
Subject: BUG #17414: install failed