关于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
<think>我们正在处理一个关于PostgreSQL查询计划优化的问题,特别是排序(Sort)和嵌套循环左连接(Nested Loop Left Join)操作。用户希望理解并优化这些操作。 首先,我们需要理解这些操作在PostgreSQL中是如何工作的,以及为什么优化器会选择它们。然后,我们可以讨论如何优化这些操作。 ### 1. 排序(Sort)操作 排序操作通常出现在以下情况: - 使用`ORDER BY`子句 - 为归并连接(Merge Join)准备数据 - 为窗口函数(Window Functions)准备数据 - 使用`DISTINCT`或`GROUP BY`(有时,如果数据已经排序,可能不需要显式排序) 排序是一个昂贵的操作,尤其是当数据量很大时。在PostgreSQL中,排序操作可能会使用磁盘空间(如果工作内存不足),这会导致性能下降。 #### 优化排序操作的方法: - **增加工作内存(work_mem)**:通过增加`work_mem`参数,可以让排序在内存中完成,避免使用磁盘。但是,设置过高可能会导致内存压力。 - **使用索引**:如果排序的列上有索引(如B-tree索引),那么数据库可能直接使用索引的顺序,从而避免显式排序。 - **减少排序的数据量**:通过`WHERE`条件过滤掉不需要的行,或者只选择必要的列,以减少排序的数据量。 - **使用LIMIT**:如果查询包含`LIMIT`子句,优化器可能会选择使用能够避免完全排序的查询计划(例如,使用索引扫描)。 ### 2. 嵌套循环左连接(Nested Loop Left Join) 嵌套循环连接的工作方式是:对于外层表的每一行,在内层表中查找匹配的行。左连接意味着即使内层表没有匹配,也要返回外层表的所有行。 嵌套循环连接在以下情况效率较高: - 外层表很小 - 内层表有高效的索引(特别是连接条件上的索引) 但是,如果外层表很大,或者内层表没有索引,嵌套循环连接可能会非常慢(时间复杂度为O(n*m))。 #### 优化嵌套循环左连接的方法: - **确保内层表有索引**:在连接条件的列上创建索引,这样内层表的查找可以快速完成。 - **减少外层表的大小**:通过过滤条件减少外层表的行数。 - **考虑使用其他连接类型**:如果嵌套循环连接效率低下,可以考虑使用哈希连接(Hash Join)或归并连接(Merge Join)。但是,这通常需要重写查询或调整配置参数(如`enable_nestloop`)来影响优化器的选择。 ### 结合排序和嵌套循环左连接的优化 如果查询计划中同时出现了排序和嵌套循环左连接,我们需要分析: 1. 排序操作是否必须?是否可以通过索引避免? 2. 嵌套循环左连接是否高效?内层表是否有索引? ### 示例分析 假设我们有如下查询: ```sql SELECT * FROM orders LEFT JOIN order_details ON orders.id = order_details.order_id WHERE orders.date > '2020-01-01' ORDER BY orders.date; ``` 可能的优化: 1. 在`orders.date`上创建索引,这样可以利用索引过滤(WHERE条件)和排序(ORDER BY)。这样可能避免显式排序操作。 2. 在`order_details.order_id`上创建索引,以加速嵌套循环连接的内层表查找。 ### 调整优化器行为 PostgreSQL的优化器基于成本模型。我们可以通过调整成本参数(如`random_page_cost`、`cpu_tuple_cost`等)或设置特定开关(如`enable_nestloop`、`enable_sort`)来影响优化器的选择。但是,这些调整需要谨慎,最好在测试环境中进行。 此外,使用`EXPLAIN`和`EXPLAIN ANALYZE`命令分析查询计划是优化的关键步骤。 ### 相关问题 根据用户的问题,我们可以生成以下相关问题: 1. 如何阅读和理解PostgreSQL的查询执行计划? 2. 在什么情况下嵌套循环连接比哈希连接或归并连接更有效? 3. 如何确定是否应该为排序操作增加`work_mem`? 4. 如何创建合适的索引以优化连接和排序操作? 5. 如何调整PostgreSQL的配置参数以优化查询性能? ### 总结 优化排序和嵌套循环左连接操作的关键在于: - 通过索引避免不必要的排序 - 确保嵌套循环连接的内层表有高效的索引 - 调整内存参数(如`work_mem`)以支持排序操作 - 分析查询计划,理解优化器的选择 通过以上方法,我们可以显著提升包含排序和嵌套循环左连接操作的查询性能。 §§相关问题§§ 1. 如何为PostgreSQL的排序操作选择合适的work_mem大小? 2. 嵌套循环连接在哪些场景下是最优的选择? 3. 如何通过索引优化来避免排序操作? 4. 在PostgreSQL中,如何强制优化器选择其他连接方法(如哈希连接)? 5. 如何分析一个查询执行计划,并识别性能瓶颈?</think>### PostgreSQL 查询计划优化:排序与嵌套循环左连接 #### 一、排序操作(Sort)的优化原理 排序操作在以下场景触发: - `ORDER BY` 子句显式排序 - 窗口函数(如 `ROW_NUMBER()`)需要排序 - `DISTINCT`/`GROUP BY`(当哈希聚合不可用时) - 归并连接(Merge Join)的预处理 **优化策略:** 1. **索引避免显式排序** 在排序字段创建 B-tree 索引可使优化器直接使用索引顺序: ```sql CREATE INDEX idx_orders_date ON orders(order_date); -- 使 ORDER BY order_date 免排序 ``` 2. **调整内存参数** ```sql SET work_mem = '64MB'; -- 增加排序内存(避免磁盘临时文件) ``` *建议:通过 `EXPLAIN ANALYZE` 观察 `Sort Method`,若出现 `external merge Disk` 则需增加 `work_mem`*[^1]。 3. **LIMIT 优化** 结合 `LIMIT` 时,使用索引扫描可避免全表排序: ```sql SELECT * FROM orders ORDER BY order_date LIMIT 10; -- 需索引支持 ``` 4. **分区表优化** 对分区表排序时,确保 `enable_partitionwise_aggregate=on` 以并行排序子分区。 --- #### 二、嵌套循环左连接(Nested Loop Left Join)优化 嵌套循环连接的工作逻辑: ```mermaid graph LR A[外层表驱动] --> B[遍历每行] B --> C[内层表索引扫描] C --> D[返回匹配行] ``` **性能瓶颈:** - 外层表行数过多($O(N \times M)$ 复杂度) - 内层表无高效索引 **优化方案:** 1. **内层表索引优化** ```sql CREATE INDEX idx_order_details_order_id ON order_details(order_id); -- 内层表必须索引 ``` *验证:执行计划应出现 `Index Scan` 而非 `Seq Scan`*[^2]。 2. **减少外层表数据集** ```sql SELECT * FROM (SELECT * FROM orders WHERE status='shipped') AS filtered_orders -- 先过滤 LEFT JOIN order_details ON ... ``` 3. **参数调优** ```sql SET random_page_cost = 1.1; -- SSD 环境降低随机读成本 SET enable_nestloop = ON; -- 确保嵌套循环可用 ``` 4. **替代连接方式** 当数据量较大时,考虑强制使用哈希连接: ```sql SET enable_hashjoin = ON; SET enable_mergejoin = OFF; ``` --- #### 三、联合优化案例 **问题查询:** ```sql EXPLAIN ANALYZE SELECT o.order_id, c.name, SUM(od.amount) FROM orders o LEFT JOIN order_details od ON o.order_id = od.order_id LEFT JOIN customers c ON o.cust_id = c.cust_id WHERE o.order_date > '2023-01-01' GROUP BY o.order_id, c.name ORDER BY SUM(od.amount) DESC; ``` **优化步骤:** 1. **索引创建** ```sql CREATE INDEX idx_orders_date ON orders(order_date); CREATE INDEX idx_order_details_order_id ON order_details(order_id); CREATE INDEX idx_customers_id ON customers(cust_id); ``` 2. **改写查询** ```sql WITH filtered_orders AS ( SELECT * FROM orders WHERE order_date > '2023-01-01' -- 提前过滤 ) SELECT fo.order_id, c.name, SUM(od.amount) FROM filtered_orders fo LEFT JOIN order_details od ON fo.order_id = od.order_id -- 索引扫描 LEFT JOIN customers c ON fo.cust_id = c.cust_id -- 索引扫描 GROUP BY fo.order_id, c.name ORDER BY SUM(od.amount) DESC; ``` 3. **参数调整** ```sql SET work_mem = '128MB'; -- 支持排序和哈希聚合 SET max_parallel_workers_per_gather = 4; -- 启用并行 ``` --- #### 四、执行计划关键诊断点 1. **排序诊断** ```bash Sort (cost=2874.28..2891.75 rows=6988 width=48) Sort Key: (sum(od.amount)) DESC Sort Method: quicksort Memory: 5MB --> 若显示 disk 需优化 ``` 2. **连接诊断** ```bash Nested Loop Left Join (cost=0.85..125.76 rows=98 width=48) Join Filter: (o.cust_id = c.cust_id) -> Index Scan using idx_orders_date... --> 理想状态 -> Seq Scan on customers c --> 危险信号(需索引) ``` --- #### 五、进阶优化工具 1. **扩展插件** ```sql CREATE EXTENSION pg_stat_statements; -- 识别高成本排序/连接 CREATE EXTENSION hypopg; -- 虚拟索引测试 ``` 2. **优化器提示**(需 pg_hint_plan) ```sql /*+ NestLoop(od c) Leading(o od c) */ SELECT ... -- 强制连接顺序 ``` > 关键原则:**通过索引将随机访问转化为顺序访问,通过过滤减少参与运算的数据量**。实际优化需结合 `EXPLAIN (ANALYZE, BUFFERS)` 数据验证[^3]。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值