title: Postgresql 10 query with large(?) IN lists in filters
body: <p>I am working on a relatively simple query:</p>
  
  <pre><code>SELECT row.id, row.name FROM things AS row 
  WHERE row.type IN ( 
  '00000000-0000-0000-0000-000000031201',
  ...
   )
  ORDER BY row.name ASC, row.id ASC 
  LIMIT 2000;
  </code></pre>
  
  <p><strong>The problem:</strong></p>
  
  <p>the query is fine if the list contains 25 or less UUIDs:</p>
  
  <pre><code> Limit  (cost=21530.51..21760.51 rows=2000 width=55) (actual time=5.057..7.780 rows=806 loops=1)
     -&gt;  Gather Merge  (cost=21530.51..36388.05 rows=129196 width=55) (actual time=5.055..6.751 rows=806 loops=1)
           Workers Planned: 1
           Workers Launched: 1
           -&gt;  Sort  (cost=20530.50..20853.49 rows=129196 width=55) (actual time=2.273..2.546 rows=403 loops=2)
                 Sort Key: name, id
                 Sort Method: quicksort  Memory: 119kB
                 -&gt;  Parallel Index Only Scan using idx_things_type_name_id on things row  (cost=0.69..9562.28 rows=129196 width=55) (actual time=0.065..0.840 rows=403 loops=2)
                       Index Cond: (type = ANY ('{00000000-0000-0000-0000-000000031201,... (&lt; 24 more)}'::text[]))
                       Heap Fetches: 0
   Planning time: 0.202 ms
   Execution time: 8.485 ms
  </code></pre>
  
  <p>but once the list grows larger than 25 elements a different index is used and the query execution time really goes up:</p>
  
  <pre><code> Limit  (cost=1000.58..15740.63 rows=2000 width=55) (actual time=11.553..29789.670 rows=952 loops=1)
     -&gt;  Gather Merge  (cost=1000.58..2400621.01 rows=325592 width=55) (actual time=11.551..29855.053 rows=952 loops=1)
           Workers Planned: 2
           Workers Launched: 2
           -&gt;  Parallel Index Scan using idx_things_name_id on things row  (cost=0.56..2362039.59 rows=135663 width=55) (actual time=3.570..24437.039 rows=317 loops=3)
                 Filter: ((type)::text = ANY ('{00000000-0000-0000-0000-000000031201,... (&gt; 24 more)}'::text[]))
                 Rows Removed by Filter: 5478258
   Planning time: 0.209 ms
   Execution time: 29857.454 ms
  </code></pre>
  
  <p><strong>Details:</strong></p>
  
  <p>The table contains 16435726 rows, 17 columns. The 3 columns relevant to the query are:</p>
  
  <ul>
  <li>id   - varchar(36), not null, unique, primary key</li>
  <li>type - varchar(36), foreign key</li>
  <li>name - varchar(2000)</li>
  </ul>
  
  <p>The relevant indexes are:</p>
  
  <ul>
  <li>create unique index idx_things_pkey on things (id);</li>
  <li>create index idx_things_type on things (type);</li>
  <li>create index idx_things_name_id on things (name, id);</li>
  <li>create index idx_things_type_name_id on things (type, name, id);</li>
  </ul>
  
  <p>there are 70 different type values of which 2 account for ~15 million rows. Those two are NOT in the IN list.</p>
  
  <p><strong>Experiments and questions:</strong></p>
  
  <p>I started by checking if this index helps:</p>
  
  <p>create index idx_things_name_id_type ON things (name, id, type);</p>
  
  <p>it did but slightly. 12s is not acceptable:</p>
  
  <pre><code> Limit  (cost=1000.71..7638.73 rows=2000 width=55) (actual time=5.888..12120.907 rows=952 loops=1)
     -&gt;  Gather Merge  (cost=1000.71..963238.21 rows=289917 width=55) (actual time=5.886..12154.580 rows=952 loops=1)
           Workers Planned: 2
           Workers Launched: 2
           -&gt;  Parallel Index Only Scan using idx_things_name_id_type on things row  (cost=0.69..928774.57 rows=120799 width=55) (actual time=1.024..9852.923 rows=317 loops=3)
                 Filter: ((type)::text = ANY ('{00000000-0000-0000-0000-000000031201,... 37 more}'::text[]))
                 Rows Removed by Filter: 5478258
                 Heap Fetches: 0
   Planning time: 0.638 ms
   Execution time: 12156.817 ms
  </code></pre>
  
  <p>I know that large IN lists are not efficient in Postgres but I was surprised to hit this as soon as 25 elements. Or is the issue here something else?</p>
  
  <p>I tried the solutions suggested in other posts (to inner join to a VALUES list, too change IN to IN VALUES, ...) but it made things even worse. Here is an example of one if the experiments:</p>
  
  <pre><code>SELECT row.id, row.name
  FROM things AS row 
  WHERE row.type IN (VALUES ('00000000-0000-0000-0000-000000031201'), ... )
  ORDER BY row.name ASC, row.id ASC 
  LIMIT 2000;
  </code></pre>
  
  <pre><code> Limit  (cost=0.56..1254.91 rows=2000 width=55) (actual time=45.718..847919.632 rows=952 loops=1)
     -&gt;  Nested Loop Semi Join  (cost=0.56..10298994.72 rows=16421232 width=55) (actual time=45.714..847917.788 rows=952 loops=1)
           Join Filter: ((row.type)::text = "*VALUES*".column1)
           Rows Removed by Join Filter: 542360414
           -&gt;  Index Scan using idx_things_name_id on things row  (cost=0.56..2170484.38 rows=16421232 width=92) (actual time=0.132..61387.582 rows=16435726 loops=1)
           -&gt;  Materialize  (cost=0.00..0.58 rows=33 width=32) (actual time=0.001..0.022 rows=33 loops=16435726)
                 -&gt;  Values Scan on "*VALUES*"  (cost=0.00..0.41 rows=33 width=32) (actual time=0.004..0.030 rows=33 loops=1)
   Planning time: 1.131 ms
   Execution time: 847920.680 ms
  (9 rows)
  </code></pre>
  
  <p>Query plan from inner join values():</p>
  
  <pre><code> Limit  (cost=0.56..1254.91 rows=2000 width=55) (actual time=38.289..847714.160 rows=952 loops=1)
     -&gt;  Nested Loop  (cost=0.56..10298994.72 rows=16421232 width=55) (actual time=38.287..847712.333 rows=952 loops=1)
           Join Filter: ((row.type)::text = "*VALUES*".column1)
           Rows Removed by Join Filter: 542378006
           -&gt;  Index Scan using idx_things_name_id on things row  (cost=0.56..2170484.38 rows=16421232 width=92) (actual time=0.019..60303.676 rows=16435726 loops=1)
           -&gt;  Materialize  (cost=0.00..0.58 rows=33 width=32) (actual time=0.001..0.022 rows=33 loops=16435726)
                 -&gt;  Values Scan on "*VALUES*"  (cost=0.00..0.41 rows=33 width=32) (actual time=0.002..0.029 rows=33 loops=1)
   Planning time: 0.247 ms
   Execution time: 847715.215 ms
  (9 rows)
  </code></pre>
  
  <p>Am I doing something incorrectly here?</p>
  
  <p>Any tips on how to handle this? 
  If any more info is needed I will add it as you guys ask.</p>
  
  <p>ps. The column/table/index names were "anonymised" to comply with the company policy so please do not point to the stupid names :)</p>