title: Why is Postgres not using index on a simple GROUP BY?
body: <p>I have created a 36M rows table with an index on <code>type</code> column:</p>
  
  <pre><code>CREATE TABLE items AS
    SELECT
      (random()*36000000)::integer AS id,
      (random()*10000)::integer AS type,
      md5(random()::text) AS s
    FROM
      generate_series(1,36000000);
  CREATE INDEX items_type_idx ON items USING btree ("type");
  </code></pre>
  
  <p>I run this simple query and expect postgresql to use my index:</p>
  
  <pre><code>explain select count(*) from "items" group by "type";
  </code></pre>
  
  <p>But the query planner decides to use Seq Scan instead:</p>
  
  <pre><code>HashAggregate  (cost=734592.00..734627.90 rows=3590 width=12) (actual time=6477.913..6478.344 rows=3601 loops=1)
    Group Key: type
    -&gt;  Seq Scan on items  (cost=0.00..554593.00 rows=35999800 width=4) (actual time=0.044..1820.522 rows=36000000 loops=1)
  Planning time: 0.107 ms
  Execution time: 6478.525 ms
  </code></pre>
  
  <p><em>Time without EXPLAIN:</em> 5s 979ms</p>
  
  <p>I have tried several solutions from <a href="https://stackoverflow.com/questions/8228326/keep-postgresql-from-sometimes-choosing-a-bad-query-plan">here</a> and <a href="https://stackoverflow.com/questions/23143463/why-is-postgres-scanning-a-huge-table-instead-of-using-my-index">here</a>:</p>
  
  <ul>
  <li>Run <code>VACUUM ANALYZE</code> or <code>VACUUM ANALYZE</code></li>
  <li>Configure <code>default_statistics_target</code>, <code>random_page_cost</code>, <code>work_mem</code></li>
  </ul>
  
  <p>but nothing helps apart from setting <code>enable_seqscan = OFF</code>:</p>
  
  <pre><code>SET enable_seqscan = OFF;
  explain select count(*) from "items" group by "type";
  
  GroupAggregate  (cost=0.56..1114880.46 rows=3590 width=12) (actual time=5.637..5256.406 rows=3601 loops=1)
    Group Key: type
    -&gt;  Index Only Scan using items_type_idx on items  (cost=0.56..934845.56 rows=35999800 width=4) (actual time=0.074..2783.896 rows=36000000 loops=1)
          Heap Fetches: 0
  Planning time: 0.103 ms
  Execution time: 5256.667 ms
  </code></pre>
  
  <p><em>Time without EXPLAIN:</em> 659ms</p>
  
  <p>Query with index scan is about 10x faster on my machine.</p>
  
  <p>Is there a better solution than setting <code>enable_seqscan</code>?</p>
  
  <p><strong>UPD1</strong></p>
  
  <p>My postgresql version is 9.6.3, work_mem = 4MB (tried 64MB), random_page_cost = 4 (tried 1.1), max_parallel_workers_per_gather = 0 (tried 4).</p>
  
  <p><strong>UPD2</strong></p>
  
  <p>I have tried to fill <em>type</em> column not with random numbers, but with <code>i / 10000</code> to make <code>pg_stats.correlation</code> = 1 - still seqscan.</p>
  
  <p><strong>UPD3</strong></p>
  
  <p>@jgh is 100% right:</p>
  
  <blockquote>
    <p>This typically only happens when the table's row width is much wider than some indexes</p>
  </blockquote>
  
  <p>I've made large column <code>data</code> and now postgres use index. Thanks everyone!</p>