title: Postgres not using index inside "IN" clause for UUID
body: <p>I have a table <code>users</code> with an index on <code>id</code>.</p>
  
  <p>I have another table <code>orders</code> which belongs to <code>users</code> and has a <code>user_id</code> field which also indexed</p>
  
  <p>I have the following query :</p>
  
  <pre><code>SELECT * FROM users
  WHERE id IN (
      SELECT user_id FROM orders
      WHERE user_id = '4CF93940-390D-4D70-BE62-61AFC73663BF'
  );
  </code></pre>
  
  <p>Here Postgres uses the two indexes correctly (by checking the output of EXPLAIN).</p>
  
  <p>However if I do this:</p>
  
  <pre><code>SELECT * FROM users
  WHERE id IN (
      SELECT user_id FROM orders
  );
  </code></pre>
  
  <p>(Removed the where clause from the inner Select)</p>
  
  <p>It doesn't use any index at all. It doesn't use the index on <code>orders.user_id</code> since I am selecting all of it, but why doesn't it use the index on <code>users.id</code>? </p>
  
  <p>Here is the explain output for the two queries:</p>
  
  <pre><code>Nested Loop Semi Join  (cost=4.36..22.54 rows=1 width=16)
     -&gt;  Index Only Scan using user_id on users  (cost=0.15..8.17 rows=1 width=16)
           Index Cond: (id = '4cf93940-390d-4d70-be62-61afc73663bf'::uuid)
     -&gt;  Bitmap Heap Scan on orders  (cost=4.21..14.35 rows=7 width=16)
           Recheck Cond: (user_id = '4cf93940-390d-4d70-be62-61afc73663bf'::uuid)
           -&gt;  Bitmap Index Scan on order_user_id  (cost=0.00..4.21 rows=7 width=0)
                 Index Cond: (user_id = '4cf93940-390d-4d70-be62-61afc73663bf'::uuid)
  
  
  
  Hash Join  (cost=30.88..67.21 rows=885 width=16)
      Hash Cond: (users.id = orders.user_id)
      -&gt;  Seq Scan on users  (cost=0.00..27.70 rows=1770 width=16)
      -&gt;  Hash  (cost=28.38..28.38 rows=200 width=16)
          -&gt;  HashAggregate  (cost=26.38..28.38 rows=200 width=16)
                  Group Key: orders.user_id
                  -&gt;  Seq Scan on orders  (cost=0.00..23.10 rows=1310 width=16)
  </code></pre>
  
  <p><em>UPDATE</em>:</p>
  
  <p>So running this query:</p>
  
  <pre><code>EXPLAIN SELECT * FROM users
  WHERE id IN ('4CF93940-390D-4D70-BE62-61AFC73663BF', '4CF93940-390D-4D70-BE62-61AFC73663BF');
  </code></pre>
  
  <p>uses the index correctly. So basically why is </p>
  
  <pre><code>WHERE id IN ('4CF93940-390D-4D70-BE62-61AFC73663BF', '4CF93940-390D-4D70-BE62-61AFC73663BF')
  </code></pre>
  
  <p>different from</p>
  
  <pre><code>WHERE id IN (SELECT user_id FROM orders);
  </code></pre>
  
  <p>Seems like both are just an array of ids.</p>