title: Optimize query with multiple "between" conditions
body: <p>I have a table <code>playground</code> with column <code>val</code>, column <code>val</code> is indexed.</p>
  
  <p>I have a list of ranges <code>[(min1, max1), (min2, max2), ... , (minN, maxN)]</code> 
  and I want to select all rows with <code>val</code> that fit in any of those ranges.</p>
  
  <p>E.g. my ranges looks like that: <code>[(1,5), (20,25), (200,400)]</code> 
  Here is the simple query that extracts corresponding rows:</p>
  
  <pre><code>select p.*
  from playground p
  where (val between 1 AND 5) or (val between 20 and 25) or
      (val between 200 and 400);
  </code></pre>
  
  <p>The problem here is that this list of ranges is dynamic, my application generates it and sends it along with the query to postgres. </p>
  
  <p>I tried to rewrite the query to accept dynamic list of ranges:</p>
  
  <pre><code>select p.*
  from playground p,
      unnest(ARRAY [(1, 5),(20, 25),(200, 400)]) as r(min_val INT, max_val INT)
  where p.val between r.min_val and r.max_val;
  </code></pre>
  
  <p>It extracts the same rows, but I don't know is an effective query or not?</p>
  
  <p>This is how the explain looks like for the first query:</p>
  
  <pre><code>Bitmap Heap Scan on playground p  (cost=12.43..16.45 rows=1 width=36) (actual time=0.017..0.018 rows=4 loops=1)
    Recheck Cond: (((val &gt;= 1) AND (val &lt;= 5)) OR ((val &gt;= 20) AND (val &lt;= 25)) OR ((val &gt;= 200) AND (val &lt;= 400)))
    Heap Blocks: exact=1
    -&gt;  BitmapOr  (cost=12.43..12.43 rows=1 width=0) (actual time=0.012..0.012 rows=0 loops=1)
          -&gt;  Bitmap Index Scan on playground_val_index  (cost=0.00..4.14 rows=1 width=0) (actual time=0.010..0.010 rows=3 loops=1)
                Index Cond: ((val &gt;= 1) AND (val &lt;= 5))
          -&gt;  Bitmap Index Scan on playground_val_index  (cost=0.00..4.14 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1)
                Index Cond: ((val &gt;= 20) AND (val &lt;= 25))
          -&gt;  Bitmap Index Scan on playground_val_index  (cost=0.00..4.14 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)
                Index Cond: ((val &gt;= 200) AND (val &lt;= 400))
  Planning Time: 0.071 ms
  Execution Time: 0.057 ms
  </code></pre>
  
  <p>And here is the explain for the second:</p>
  
  <pre><code>Nested Loop  (cost=0.14..12.52 rows=2 width=36) (actual time=0.033..0.065 rows=4 loops=1)
    -&gt;  Function Scan on unnest r  (cost=0.00..0.03 rows=3 width=8) (actual time=0.011..0.012 rows=3 loops=1)
    -&gt;  Index Scan using playground_val_index on playground p  (cost=0.13..4.15 rows=1 width=36) (actual time=0.008..0.015 rows=1 loops=3)
          Index Cond: ((val &gt;= r.min_val) AND (val &lt;= r.max_val))
  Planning Time: 0.148 ms
  Execution Time: 0.714 ms
  </code></pre>
  
  <p>NOTE: In both cases I did <code>set enable_seqscan = false;</code> to make the index work.</p>
  
  <p>I am worried about the "Nested Loop" stage. Is it Okay? Or there are more effective ways to pass dynamic list of ranges into a query?
  My postgres version is <code>12.1</code></p>