Avoiding “OR” for Better Query Performance

In Oracle land you have no choice but to use a long list of OR because IN has a low clause limit (1000). So if you need e.g. 1.1K rows, even using a fast indexed PK, your choices are OR or pain. The workarounds are horrible, like splitting your IN clauses and UNION ALL two copies of the same query (runs twice), or using a global temp table (which is awkward as heck to setup&#x2F;destroy each execution, particularly with IN clauses that could range from 1 through 1K+).<p>Postgres has no practical limit, there&#x27;s examples of people using 100K+ IN clauses (not recommending). People keep telling me that Oracle is &quot;enterprise grade&quot; and that Postgres is not. To be honest all I see is Postgres is flexible and modern, and Oracle is kludgy and outmoded. I&#x27;m forced to use Oracle but I&#x27;d never voluntarily start any new projects on it, I&#x27;m all in on Postgres from here on out.

It&#x27;s surprising to me that &quot;where id = 42 or id = 4711&quot; behaves differently than &quot;where id in (42, 4711)&quot;.<p>I would have guessed the database optimizes this away. It seems like an easy to implement and safe optimization. (Look at abstract syntax tree, find &quot;or&quot; expressions, find equality test expressions under there, check if two or more compare the same sub-expression against constants, then combine.)<p>Did I misunderstand what the article is saying PostgreSQL does, or does it actually not do this optimization for you?

All of the examples seem like relatively straight-forward transformations.<p>I&#x27;m quite surprised that the PG query optimizer doesn&#x27;t to those. How do other engines compare here?

Here&#x27;s a slightly different and older take on the same problem:<p><a href="http:&#x2F;&#x2F;blog.rhodiumtoad.org.uk&#x2F;2017&#x2F;01&#x2F;22&#x2F;performance-issues-with-ored-conditions&#x2F;" rel="nofollow">http:&#x2F;&#x2F;blog.rhodiumtoad.org.uk&#x2F;2017&#x2F;01&#x2F;22&#x2F;performance-issues...</a>

In MySQL, discovering the UNION hack a couple years ago was like a revelation. It’s something that I’ve since been preaching to everyone who’ll listen.<p>It’s extremely common to want to OR a small set of items. It <i>feels</i> like the compiler would be smart enough to use an index (presuming one exists matching the searches fields), but it doesn’t. UNION makes it use the index.<p>It’s a nice tool to have in your kit.<p>(At least not in 5.6 or 5.7.)

IDK about the profiler... but I have a hard time believing why the union of 2 joins will be faster than filtering first and then join the results :thinking:

The simple query:<p><pre><code> explain (analyze, timing) SELECT id FROM a WHERE a_val LIKE &#x27;something%&#x27; OR a_val LIKE &#x27;other%&#x27;; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ Bitmap Heap Scan on a (cost=8.87..12.88 rows=20 width=4) (actual time=0.012..0.012 rows=0 loops=1) Recheck Cond: ((a_val ~~ &#x27;something%&#x27;::text) OR (a_val ~~ &#x27;other%&#x27;::text)) Filter: ((a_val ~~ &#x27;something%&#x27;::text) OR (a_val ~~ &#x27;other%&#x27;::text)) -&gt; BitmapOr (cost=8.87..8.87 rows=1 width=0) (actual time=0.011..0.011 rows=0 loops=1) -&gt; Bitmap Index Scan on a_val_idx (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=0 loops=1) Index Cond: ((a_val ~&gt;=~ &#x27;something&#x27;::text) AND (a_val ~&lt;~ &#x27;somethinh&#x27;::text)) -&gt; Bitmap Index Scan on a_val_idx (cost=0.00..4.43 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1) Index Cond: ((a_val ~&gt;=~ &#x27;other&#x27;::text) AND (a_val ~&lt;~ &#x27;othes&#x27;::text)) Planning Time: 0.088 ms Execution Time: 0.033 ms (10 rows) </code></pre> using union:<p><pre><code> explain (analyze, timing) select id from a where a_val like &#x27;something%&#x27; union select id from a where a_val like &#x27;other%&#x27;; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ HashAggregate (cost=17.23..17.43 rows=20 width=4) (actual time=0.135..0.135 rows=0 loops=1) Group Key: a.id -&gt; Append (cost=0.42..17.18 rows=20 width=4) (actual time=0.133..0.133 rows=0 loops=1) -&gt; Index Scan using a_val_idx on a (cost=0.42..8.44 rows=10 width=4) (actual time=0.128..0.128 rows=0 loops=1) Index Cond: ((a_val ~&gt;=~ &#x27;something&#x27;::text) AND (a_val ~&lt;~ &#x27;somethinh&#x27;::text)) Filter: (a_val ~~ &#x27;something%&#x27;::text) -&gt; Index Scan using a_val_idx on a a_1 (cost=0.42..8.44 rows=10 width=4) (actual time=0.004..0.004 rows=0 loops=1) Index Cond: ((a_val ~&gt;=~ &#x27;other&#x27;::text) AND (a_val ~&lt;~ &#x27;othes&#x27;::text)) Filter: (a_val ~~ &#x27;other%&#x27;::text) Planning Time: 0.124 ms Execution Time: 0.164 ms (11 rows) </code></pre> using gin index:<p><pre><code> EXPLAIN (analyze, timing) SELECT id FROM a WHERE a_val LIKE ANY (ARRAY[&#x27;something%&#x27;, &#x27;other%&#x27;]); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on a (cost=176.15..247.11 rows=20 width=4) (actual time=0.036..0.036 rows=0 loops=1) Recheck Cond: (a_val ~~ ANY (&#x27;{something%,other%}&#x27;::text[])) -&gt; Bitmap Index Scan on a_val_trgm_idx (cost=0.00..176.15 rows=20 width=0) (actual time=0.034..0.034 rows=0 loops=1) Index Cond: (a_val ~~ ANY (&#x27;{something%,other%}&#x27;::text[])) Planning Time: 0.091 ms Execution Time: 0.193 ms (6 rows) </code></pre> Doesn&#x27;t the simple query win?

Thank you, that was a well written and informative post.

Is this applicable to mysql?

Why not just use a sub query ??

I&#x27;ve always said SQL is a bad abstraction. An artifact from the past that we have to deal with like css and JavaScript.<p>This article solidifies my point. Seriously, ors are bad so union two queries as a cheaper alternative? Theres no rhyme or reason to it at the API level. You have to look under the hood to see why this hack works. Hence why SQL is such a bad abstraction. We program and optimize sql not with logic but with memorization of hacks.