关于PostgreSQL执行计划中的Bitmap Heap Scan、Bitmap Index Scan、Recheck Cond

本文通过创建表和索引,使用HighGo数据库5.6.3版本进行性能测试,详细展示了如何利用EXPLAIN命令分析查询计划,理解Bitmap Heap Scan和Bitmap Index Scan的工作原理,以及Recheck Cond在查询优化中的作用。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

D:\highgo\database\5.6.3\bin>psql -d highgo -U highgo -p 5868
用户 highgo 的口令:
psql (5.6.3)

PSQL: Release 5.6.3
Connected to:
HighGo Database V5.6 Debug Edition Release 5.6.3 - 64-bit Production

输入 "help" 来获取帮助信息.

highgo=# create table t_lei as select oid,relname from pg_class;
SELECT 571
highgo=# create index idx_t_lei_1 on t_lei(oid)
highgo-# ;
CREATE INDEX
highgo=# explain analyze select * from t_lei where oid<100 and relname='dump';
                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t_lei  (cost=3.90..14.75 rows=1 width=68) (actual time=0.089..0.089 rows=0 loops=1)  ———————>>>此处
   Recheck Cond: (oid < '100'::oid)———————>>>此处
   Filter: (relname = 'dump'::name)
   ->  Bitmap Index Scan on idx_t_lei_1  (cost=0.00..3.90 rows=190 width=0) (actual time=0.077..0.077 rows=0 loops=1)———————>>>此处
         Index Cond: (oid < '100'::oid)
 Planning time: 7.475 ms
 Execution time: 0.244 ms
(7 行记录)


highgo=# \d+ t_lei
                           数据表 "public.t_lei"
  栏位   | 类型 | Collation | Nullable | Default | 存储  | 统计目标 | 描述
---------+------+-----------+----------+---------+-------+----------+------
 oid     | oid  |           |          |         | plain |          |
 relname | name |           |          |         | plain |          |
索引:
    "idx_t_lei_1" btree (oid)


highgo=#

解释如下,来源于:
https://stackoverflow.com/questions/50959814/what-does-recheck-cond-in-explain-result-mean

If the bitmap gets too large we convert it to "lossy" style, 
in which we only remember which pages contain matching tuples instead of remembering each tuple individually. 
When that happens, 
the table-visiting phase has to examine each tuple on the page and recheck the scan condition to see which tuples to return.

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值