PostgreSQL not using the index on queries that return large number of records I have a table called my_table which contains about 30M records. The table has an index set on column_a. However, I'm noticing that when number of returned queries are high, the index is not used. 

Index not used:

explain (buffers, analyze) select * from my_table where column_a in (1);
                                                               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on my_table  (cost=0.00..1306095.85 rows=7772384 width=648) (actual time=0.009..6231.312 rows=7720995 loops=1)
   Filter: (column_a = 1)
   Rows Removed by Filter: 7398657
   Buffers: shared hit=463612 read=653528
 Planning time: 0.840 ms
 Execution time: 7717.923 ms
(6 rows)


Index Used:

explain (buffers, analyze) select * from my_table where column_a in (8);
                                                                            QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on my_table  (cost=1378.58..233236.51 rows=73567 width=648) (actual time=14.258..67.795 rows=74756 loops=1)
   Recheck Cond: (column_a = 8)
   Heap Blocks: exact=36425
   Buffers: shared hit=36632
   -&gt;  Bitmap Index Scan on my_table_column_a_idx  (cost=0.00..1360.19 rows=73567 width=0) (actual time=8.595..8.596 rows=74756 loops=1)
         Index Cond: (column_a = 8)
         Buffers: shared hit=207
 Planning time: 0.855 ms
 Execution time: 82.253 ms
(9 rows)


I have the table vacuumed:

select relallvisible, relpages, relallvisible/relpages as ratio from pg_class where relname='my_table';
 relallvisible | relpages | ratio
---------------+----------+-------
       1117140 |  1117140 |     1
(1 row)
That is just as it should be.

If you return a significant part of the table, the overhead of the bitmap index scan part is not worth paying, because you have to visit most of the heap blocks anyway.

So PostgreSQL just skips that part and goes to the heap scan directly. The result is a sequential scan.