4 ms·
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: "All
by pdw 3y ago
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."
- sdfghswe 3y agoThe 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.
- print_goto_ten 3y agoOr they are new to the industry and thought that they might share their learning journey. Why be so cynical?
- bakuninsbart 3y agoThis 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 3y agoSometimes 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.
- angry__panda 3y agoThe 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 3y agoSeems 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 3y agoBased on my experiments, Postgres switches from scanning the list to a hash when the array size > 8.