[pdw]

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."

[angry__panda]

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)."

[consoomer]

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?

[angry__panda]

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

[sdfghswe]

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.

[bakuninsbart]

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.

[timfi]

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.

[print_goto_ten]

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

[wiz21c]

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

[sdfghswe]

Because I've been around.