<story><title>When should you use the IN instead of the OR operator in Postgres?</title><url>https://ottertune.com/blog/query-best-practices-when-should-you-use-the-in-instead-of-the-or-operator</url></story><parent_chain><item><author>thargor90</author><text>Yes, this explains the how, but not the why.&lt;p&gt;Why is there no optimization in place for this? Converting a=x or a=y or a=z to a in(x,y,z) should be trivial and the db should have heuristics to calculate the expected query cost to decide when to apply this transformation.</text></item><item><author>ZeroClickOk</author><text>from the article:&lt;p&gt;&amp;quot;For the OR clause query, the DBMS sequentially compares each condition&amp;#x27;s value one by one. In the given example, it performs three comparisons for each row to determine if (price = 1), (price = 2), and then (price = 3). This evaluation approach means that for N predicates, the complexity of the filtering operation per row is O(N).&lt;p&gt;On the other hand, with the IN clause, PostgreSQL builds a temporary hash table populated with the elements in the query’s IN clause (ExecEvalHashedScalarArrayOp). Then as the DBMS scans each tuple, it probes this hash table to see whether the tuple&amp;#x27;s attribute matches with any entry. This hash-based evaluation has a more efficient complexity of O(1) since the DBMS only needs to perform one lookup in the hash table per tuple.&lt;p&gt;Hence, as the number of predicates increases in a query, the OR clause incurs significant overhead because the DBMS evaluates predicates for each row individually, in contrast to the more efficient IN clause.&amp;quot;</text></item><item><author>thargor90</author><text>That&amp;#x27;s weird. I thought this would be optimized based on database statistics and not depend on the query syntax. At least for those simple queries.&lt;p&gt;Can anyone elaborate why this is the case?</text></item></parent_chain><comment><author>mike_hock</author><text>Yes, and Postgres remains staunchly opposed to planner hints because the planner knows better! It always computes the optimal query plan!&lt;p&gt;The &amp;quot;optimal query plan&amp;quot; changes at the drop of a hat, as you can see in this case. Absolutely trivial syntax changes result in a completely different query, sometimes turning a sequential scan into an index-only scan or vice versa. So, 100x difference in query time, it doesn&amp;#x27;t just do that for small tables.</text></comment>
<story><title>When should you use the IN instead of the OR operator in Postgres?</title><url>https://ottertune.com/blog/query-best-practices-when-should-you-use-the-in-instead-of-the-or-operator</url></story><parent_chain><item><author>thargor90</author><text>Yes, this explains the how, but not the why.&lt;p&gt;Why is there no optimization in place for this? Converting a=x or a=y or a=z to a in(x,y,z) should be trivial and the db should have heuristics to calculate the expected query cost to decide when to apply this transformation.</text></item><item><author>ZeroClickOk</author><text>from the article:&lt;p&gt;&amp;quot;For the OR clause query, the DBMS sequentially compares each condition&amp;#x27;s value one by one. In the given example, it performs three comparisons for each row to determine if (price = 1), (price = 2), and then (price = 3). This evaluation approach means that for N predicates, the complexity of the filtering operation per row is O(N).&lt;p&gt;On the other hand, with the IN clause, PostgreSQL builds a temporary hash table populated with the elements in the query’s IN clause (ExecEvalHashedScalarArrayOp). Then as the DBMS scans each tuple, it probes this hash table to see whether the tuple&amp;#x27;s attribute matches with any entry. This hash-based evaluation has a more efficient complexity of O(1) since the DBMS only needs to perform one lookup in the hash table per tuple.&lt;p&gt;Hence, as the number of predicates increases in a query, the OR clause incurs significant overhead because the DBMS evaluates predicates for each row individually, in contrast to the more efficient IN clause.&amp;quot;</text></item><item><author>thargor90</author><text>That&amp;#x27;s weird. I thought this would be optimized based on database statistics and not depend on the query syntax. At least for those simple queries.&lt;p&gt;Can anyone elaborate why this is the case?</text></item></parent_chain><comment><author>dumbfounder</author><text>My gut says it&amp;#x27;s a resource issue. Oracle has the time and money to optimize a gajillion scenarios that increase performance without making developers think things through. Which makes the product easier to use and seem faster. It&amp;#x27;s very simple to code this one, but then there are 10,000 other cases you need to code too to make it cover a large percentage of potential optimizations.</text></comment>