为什么Postgres没有在查询中使用索引 [英] Why postgres is not using the index in my query

问题描述

我有2个表，如下所示：

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)

当我解释查询时：

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给了我这个：

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)

我的问题是：如果我仅按构成唯一索引的st值和类型值进行过滤，为什么不使用此唯一索引？

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?

推荐答案

您的表没有足够的行来使用索引。它们适合放在单个磁盘页面中，因此使用cpu时间读取整个内容并筛选出行要比两次执行相同操作（一次为索引，一次为数据）更快。

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).

这篇关于为什么Postgres没有在查询中使用索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！