0

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?

2

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.

  • And it would be nice to get feedback which of the points richyen mentioned are now the solution (for example „yes it was a small table“) – eckes Nov 13 at 11:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.