关于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.

 

Sort (cost=50966332402.09..50966332727.83 rows=130297 width=2263) Sort Key: b.modify_time DESC NULLS LAST -> Nested Loop Left Join (cost=3219.70..50966193960.42 rows=130297 width=2263) -> Nested Loop Left Join (cost=3219.28..50962082798.25 rows=130297 width=1137) Join Filter: ((a.storyid)::text = (desensitize_config.storyid)::text) -> Merge Left Join (cost=1002.71..50826114488.91 rows=130297 width=1095) Merge Cond: ((b.story_id)::text = (a.storyid)::text) -> Nested Loop Semi Join (cost=1002.29..50825843973.85 rows=130297 width=394) Join Filter: ((b.operator_code)::text = (regexp_split_to_table('U91000001,UVCN100003566,U1002513975,U14500001'::text, ','::text))) -> Gather Merge (cost=1002.29..50819699064.53 rows=446196 width=394) Workers Planned: 2 -> Merge Left Join (cost=2.26..50819646562.42 rows=185915 width=394) Merge Cond: ((b.story_id)::text = (main.story_id)::text) Filter: (CASE WHEN (main.story_id IS NULL) THEN '0'::text WHEN (main.set_time > CURRENT_TIMESTAMP) THEN '1'::text ELSE '2'::text END <> '2'::text) -> Parallel Index Scan using storyedit_storyinfo_pkey1 on storyedit_storyinfo b (cost=0.55..50819646073.62 rows=194768 width=240) Filter: ((publish = ANY ('{0,1}'::bigint[])) AND (story_type = ANY ('{1,3}'::numeric[])) AND ((author_code)::text <> '684233758342959'::text) AND (NOT (SubPlan 3))) SubPlan 3 -> Materialize (cost=0.00..181020.10 rows=5862873 width=16) -> Seq Scan on threecloud_developer d (cost=0.00..123077.73 rows=5862873 width=16) -> Sort (cost=1.71..1.77 rows=22 width=154) Sort Key: main.story_id -> Seq Scan on story_sunset_main main (cost=0.00..1.22 rows=22 width=154) -> Materialize (cost=0.00..20.02 rows=1000 width=32) -> ProjectSet (cost=0.00..5.02 rows=1000 width=32) -> Result (cost=0.00..0.01 rows=1 width=0) -> Index Scan using story_published_pkey on story_published a (cost=0.42..269613.73 rows=162357 width=701) -> Materialize (cost=2216.57..3955.75 rows=69567 width=42) -> HashAggregate (cost=2216.57..2912.24 rows=69567 width=42) Group Key: desensitize_config.storyid -> Seq Scan on desensitize_config (cost=0.00..2037.06 rows=71806 width=42) -> Index Scan using story_counttimes_pkey on story_counttimes (cost=0.42..5.37 rows=1 width=74) Index Cond: ((story_id)::text = (a.storyid)::text) SubPlan 1 -> Limit (cost=9.15..13.16 rows=1 width=42) -> Bitmap Heap Scan on story_collection c (cost=9.15..13.16 rows=1 width=42) Recheck Cond: (((storyid)::text = (b.story_id)::text) AND ((usercode)::text = '684233758342959'::text)) -> BitmapAnd (cost=9.15..9.15 rows=1 width=0) -> Bitmap Index Scan on pk_sc_storyid_0502 (cost=0.00..4.44 rows=3 width=0) Index Cond: ((storyid)::text = (b.story_id)::text) -> Bitmap Index Scan on pk_sc_usercode_0502 (cost=0.00..4.46 rows=6 width=0) Index Cond: ((usercode)::text = '684233758342959'::text) SubPlan 2 -> Limit (cost=9.00..13.01 rows=1 width=42) -> Bitmap Heap Scan on story_likes c_1 (cost=9.00..13.01 rows=1 width=42) Recheck Cond: (((usercode)::text = '684233758342959'::text) AND ((storyid)::text = (b.story_id)::text)) -> BitmapAnd (cost=9.00..9.00 rows=1 width=0) -> Bitmap Index Scan on pk_sl_usercode_0502 (cost=0.00..4.31 rows=3 width=0) Index Cond: ((usercode)::text = '684233758342959'::text) -> Bitmap Index Scan on pk_sl_storyid_0502 (cost=0.00..4.43 rows=3 width=0) Index Cond: ((storyid)::text = (b.story_id)::text)
最新发布
07-24
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值