Readit Newsangry__panda commented on When should you use the IN instead of the OR operator in Postgres? ottertune.com/blog/query-... · Posted by u/angry__panda
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.
When should you use IN instead of the OR operator?
Tl;dr;
Always.
This is true for Postgres.
But might not be true for other databases. For example, for MySQL, in some cases OR is faster than IN operator based on our experiments.
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."
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)."