How do I make Postgres use an index when using IN or ANY?

Question

I have a query such as:

select col1 from mytable where col1 in('a','b','c')

But running such a query yields a Seq Scan per EXPLAIN

If I have an index on col1, why does Postgres not do an index scan?

Answer 1

I don't think that it's failing to use the index, but that it has a lot to do with a combination of factors:

• number of rows in the table
• number of unique values in col1
• query planner tuning (seq_page_cost,random_page_cost,etc.)

It could very well be that the lowest-cost plan involves a Sequential Scan, at least for this situation.

On my machine, I get the following:

edb=# create table mytable (col1 text);
CREATE TABLE
edb=# create index mt_idx on mytable(col1);
CREATE INDEX
edb=# insert into mytable values ('a'),('b'),('c');
INSERT 0 3
edb=# explain analyze select col1 from mytable where col1 in ('a','b','c');
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on mytable  (cost=8.62..18.90 rows=20 width=32) (actual time=0.087..0.131 rows=3 loops=1)
   Recheck Cond: (col1 = ANY ('{a,b,c}'::text[]))
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on mt_idx  (cost=0.00..8.62 rows=20 width=0) (actual time=0.058..0.071 rows=3 loops=1)
         Index Cond: (col1 = ANY ('{a,b,c}'::text[]))
 Planning time: 0.124 ms
 Execution time: 0.290 ms
(7 rows)

edb=# insert into mytable values (generate_series(1,1000000)::text);
INSERT 0 1000000
edb=# analyze;
ANALYZE;
edb=# explain analyze select col1 from mytable where col1 in ('a','b','c');
                                                      QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
 Index Only Scan using mt_idx on mytable  (cost=0.42..13.33 rows=3 width=6) (actual time=0.039..0.105 rows=3 loops=1)
   Index Cond: (col1 = ANY ('{a,b,c}'::text[]))
   Heap Fetches: 0
 Planning time: 0.223 ms
 Execution time: 0.211 ms
(5 rows)

If you think that the query planner is erroneously choosing a Sequential Scan, please share more details about your data and test case (though from my experience, whenever I question the query planner's choices, I'm usually wrong).

You may also wish to SET enable_seqscan = off and try the EXPLAIN again, and see the cost of using the Index, and compare with the cost of the Sequential Scan.