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

最新推荐文章于 2024-04-23 21:37:02 发布

原创最新推荐文章于 2024-04-23 21:37:02 发布 · 4.3k 阅读

2 ·

CC 4.0 BY-SA版权

PostgreSQL 专栏收录该内容

211 篇文章

订阅专栏

本文通过创建表和索引，使用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.