我有2张表,如下所示:
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给我这个:
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)
过了一会儿给我这个完全相同的查询(仍然不使用索引):
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值和类型值进行过滤,为什么不使用此唯一索引?
您的表没有足够的行来使用索引。它们适合放在单个磁盘页面中,因此使用cpu时间读取整个内容并筛选出行要比两次执行同一操作(一次用于索引,另一次用于数据)要快。