Comments on: SQL Optimizations in PostgreSQL: IN vs EXISTS vs ANY/ALL vs JOIN https://www.percona.com/blog/sql-optimizations-in-postgresql-in-vs-exists-vs-any-all-vs-join/ Thu, 05 Oct 2023 13:06:34 +0000 hourly 1 https://wordpress.org/?v=6.5.2 By: Rajat Gupta https://www.percona.com/blog/sql-optimizations-in-postgresql-in-vs-exists-vs-any-all-vs-join/#comment-10972956 Mon, 18 Jan 2021 15:52:40 +0000 https://www.percona.com/blog/?p=66585#comment-10972956 Trying to assess a situation where from a heavy table in PostgreSQL, data is fetched using an API and filter is applied making use of an IN clause and the values to filter are passed in a measure of few thousands. Will psql still use the in clause optimally? or some other construct suits well?

]]> By: harisai hari https://www.percona.com/blog/sql-optimizations-in-postgresql-in-vs-exists-vs-any-all-vs-join/#comment-10972955 Mon, 18 Jan 2021 08:57:43 +0000 https://www.percona.com/blog/?p=66585#comment-10972955 I personally like the first scenario, no problem you can call me a lazy DBA. I always afraid of exclusion queries triggered by tall developers.

]]> By: Francisco Puga https://www.percona.com/blog/sql-optimizations-in-postgresql-in-vs-exists-vs-any-all-vs-join/#comment-10972390 Tue, 23 Jun 2020 13:16:12 +0000 https://www.percona.com/blog/?p=66585#comment-10972390 Really nice post.

I know that is centered on performance but as some people points that NOT IN is a don’t [1], include a note about how NULLs are handled in the different options or at least writing a warn about it will be nice.

[1] https://wiki.postgresql.org/wiki/Don't_Do_This

]]> By: Scott C. https://www.percona.com/blog/sql-optimizations-in-postgresql-in-vs-exists-vs-any-all-vs-join/#comment-10972129 Sat, 18 Apr 2020 14:28:28 +0000 https://www.percona.com/blog/?p=66585#comment-10972129 The exclusion queries are not equivalent and can produce vastly different results. Just try it where the exclusion set contains 100 values, one of which is null.

]]> By: Stanislav Sumariuk https://www.percona.com/blog/sql-optimizations-in-postgresql-in-vs-exists-vs-any-all-vs-join/#comment-10972125 Sat, 18 Apr 2020 08:46:20 +0000 https://www.percona.com/blog/?p=66585#comment-10972125 I will definitely revisit some of my queries. Thank you for this post.

]]>