Why postgres is not using the index in my query

Question

I have 2 tables as follow:

tb_st:
Columns:
st_id  | integer
st     | character varying(80)
type   | integer
Indexes:
    PRIMARY KEY (st_id)
    UNIQUE INDEX (st, type)
    INDEX (st)

tb_pd:
Column
st_id  | integer
bot_id | integer
Indexes:
    PRIMARY KEY (st_id, bot_id)
    INDEX (bot_id)
Foreign-key constraints:
    FOREIGN KEY (st_id) REFERENCES tb_st(st_id)

When i explain the query:

select p.bot_id
from tb_pd p inner join
     tb_st s
     on p.st_id = s.st_id
where s.st = 'abc' and s.type = 1

postgres gives me this:

 Nested Loop  (cost=4.24..16.10 rows=11 width=194)
   ->  Seq Scan on tb_st s  (cost=0.00..1.07 rows=1 width=186)
         Filter: (((st)::text = 'abc'::text) AND (type = 1))
   ->  Bitmap Heap Scan on tb_pd p  (cost=4.24..14.91 rows=11 width=8)
         Recheck Cond: (st_id = s.st_id)
         ->  Bitmap Index Scan on tb_pd_pkey  (cost=0.00..4.24 rows=11 width=0)
               Index Cond: (st_id = s.st_id)
(7 rows)

after a while gave me this for the exact same query (still not using indexes):

 Nested Loop  (cost=0.00..2.19 rows=1 width=4)
   Join Filter: (p.st_id = s.st_id)
   ->  Seq Scan on tb_st s  (cost=0.00..1.07 rows=1 width=4)
         Filter: (((st)::text = 'abc'::text) AND (type = 1))
   ->  Seq Scan on tb_pd p  (cost=0.00..1.05 rows=5 width=8)
(5 rows)

My question is: If i am only filtering by a st value and a type value that composes a UNIQUE INDEX, why this unique index is not being used?

Denis de Bernardy · Accepted Answer · 2014-04-16 22:09:31Z

2

Your tables don't have enough rows to use an index. They fit in a single disk page, so it's faster to read the entire thing and filter rows out using cpu time than it is to do the same thing twice (once for the index, and another time for the data).

answered Apr 16, 2014 at 22:09

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ivan De Sousa Paz Over a year ago

That's exactly what happened. I populated the tables with millions of fake rows for testing and the explain used indexes for everything! I love postgres!

Collectives™ on Stack Overflow

Why postgres is not using the index in my query

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related