Re: Postgres NOT IN vs NOT EXISTS optimization

2022-08-11 Thread Bruce Momjian
On Tue, Jun 14, 2022 at 12:09:16PM -0400, Tom Lane wrote: > "Dirschel, Steve" writes: > > Is Postgres able to drive the query the same way with the NOT IN as the > > NOT EXISTS is doing or is that only available if the query has a NOT > > EXISTS? > > NOT IN is not optimized very well in PG, becau

Re: Postgres NOT IN vs NOT EXISTS optimization

2022-06-14 Thread Tom Lane
"Dirschel, Steve" writes: > Is Postgres able to drive the query the same way with the NOT IN as the > NOT EXISTS is doing or is that only available if the query has a NOT > EXISTS? NOT IN is not optimized very well in PG, because of the strange semantics that the SQL spec demands when the sub-que

Re: Postgres NOT IN vs NOT EXISTS optimization

2022-06-14 Thread Jeremy Smith
I think this explains the situation well: https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_NOT_IN On Tue, Jun 14, 2022 at 11:59 AM Dirschel, Steve < steve.dirsc...@thomsonreuters.com> wrote: > We are in the process of migrating from Oracle to Postgres and the > following query does muc

Postgres NOT IN vs NOT EXISTS optimization

2022-06-14 Thread Dirschel, Steve
We are in the process of migrating from Oracle to Postgres and the following query does much less work with Oracle vs Postgres. explain (analyze, buffers) select favoritegr0_.FAVORITE_GROUP_SID as favorite1_2_, favoritegr0_.CHANGED as changed2_2_, favoritegr0_.TYPE_DISCRIMINATOR as type_dis3_2_,