Hacker News Reader

Comment by pdw | Hacker News Reader

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

View on HN

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

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?

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

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.

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.

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.

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

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

Because I've been around.

Reply on news.ycombinator.com