When should you use the IN instead of the OR operator in Postgres queries?

Re:When should you use the IN instead of the OR operator in ... by pdw on Wednesday August 30, 2023 @04:03PM (#37328177)

It's frustrating when articles like this don't mention the version of the software tested. Because that does matter.

From the PostgreSQL 14 release notes: "Allow hash lookup for IN clauses with many constants (James Coleman, David Rowley). Previously the code always sequentially scanned the list of values."

And in PostgreSQL 15: "Allow hash lookup for NOT IN clauses with many constants (David Rowley, James Coleman). Previously the code always sequentially scanned the list of values."

[ Parent | on HN ]

Re:When should you use the IN instead of the OR operator in ... by sdfghswe on Wednesday August 30, 2023 @04:06PM (#37328216)

The people writing these articles are usually not the most skilled. They're typically just filling their blogs. I swear I've seen this same exact subject at least 5 times before HN. They're al rehashing from each other.

[ Parent | on HN ]

Re:When should you use the IN instead of the OR operator in ... by print_goto_ten on Wednesday August 30, 2023 @04:08PM (#37328246)

Or they are new to the industry and thought that they might share their learning journey. Why be so cynical?

[ Parent | on HN ]

Re:When should you use the IN instead of the OR operator in ... by sdfghswe on Wednesday August 30, 2023 @04:34PM (#37328653)

Because I've been around.

[ Parent | on HN ]

Re:When should you use the IN instead of the OR operator in ... by wiz21c on Thursday August 31, 2023 @06:16AM (#37334883)

Because they don't usually say "I'm learning".

[ Parent | on HN ]

Re:When should you use the IN instead of the OR operator in ... by bakuninsbart on Thursday August 31, 2023 @06:25AM (#37334954)

This article perfectly answers the relatively simple question it asks. It is exactly the kind of search result I'm looking for if I ever wonder which approach is better and don't want to experiment myself.

[ Parent | on HN ]

Re:When should you use the IN instead of the OR operator in ... by timfi on Thursday August 31, 2023 @07:25AM (#37335498)

Sometimes this may be the case but not so much here. Firstly the mention the version they used and, secondly, OtterTune are a startup based around automatic DB steup/schema optimization founded by folks from CMUs DB group, they most certainly know what they are talking about here.

[ Parent | on HN ]

Re:When should you use the IN instead of the OR operator in ... by angry__panda on Wednesday August 30, 2023 @08:53PM (#37331170)

The article mentioned that it's tested on Postgres 14:

"We used a PostgreSQL v14 database on a db.t3.medium Amazon RDS instance equipped with 2 CPUs and 4GB RAM. The storage capacity is 200GB (gp2 storage)."

[ Parent | on HN ]

Re:When should you use the IN instead of the OR operator in ... by consoomer on Thursday August 31, 2023 @09:48AM (#37337110)

Seems like an obvious optimization to use a hash instead of scanning the array with comparisons... but I bet it's hard because on small arrays sequentially scanning is faster than building a hash first. Does anyone know at what size the optimizer switches from scanning the list to a hash?

[ Parent | on HN ]

Re:When should you use the IN instead of the OR operator in ... by angry__panda on Thursday August 31, 2023 @09:54AM (#37337200)

Based on my experiments, Postgres switches from scanning the list to a hash when the array size > 8.

[ Parent | on HN ]