1. explain和explain anayze

EXPLAIN和EXPLAIN ANALYZE生成的执行计划通常是一致的，但并不能保证完全一致。

1.1. 核心功能对比

-- 对于修改数据的语句，可以在事务中测试后回滚
BEGIN;
EXPLAIN ANALYZE UPDATE table_name SET column = value;
ROLLBACK;

1.2. 输出信息对比

1.3. I/O和缓冲区信息

1.4. 准确性对比

1.5. 使用场景对比

1.6. 高级选项对比

1.7. 性能影响对比

1.8. 输出示例对比

1.8.1. EXPLAIN 输出

Nested Loop  (cost=0.29..16.34 rows=1 width=68)
  ->  Index Scan using pk_orders on orders  (cost=0.29..8.30 rows=1 width=36)
        Index Cond: (id = 123)
  ->  Index Scan using pk_customers on customers  (cost=0.29..8.04 rows=1 width=32)
        Index Cond: (id = orders.customer_id)

1.8.2. EXPLAIN ANALYZE 输出

Nested Loop  (cost=0.29..16.34 rows=1 width=68) (actual time=0.234..0.456 rows=1 loops=1)
  ->  Index Scan using pk_orders on orders  (cost=0.29..8.30 rows=1 width=36) (actual time=0.123..0.234 rows=1 loops=1)
        Index Cond: (id = 123)
        Buffers: shared hit=3
  ->  Index Scan using pk_customers on customers  (cost=0.29..8.04 rows=1 width=32) (actual time=0.089..0.145 rows=1 loops=1)
        Index Cond: (id = orders.customer_id)
        Buffers: shared hit=3
Planning Time: 0.567 ms
Execution Time: 0.678 ms

2. cost和actual time解读

(预估成本与预估数据量) (实际执行时间与实际数据量):(cost=0.29..8.30 rows=1 width=36) (actual time=0.123..0.234 rows=1 loops=1)

2.1. cost=0.29..8.30 rows=1 width=36

• 0.29 = 启动成本 (Startup Cost)
  • 开始返回第一行数据前需要的成本
  • 包括：索引查找定位、初始化操作等
  • 对于索引扫描，通常是定位到第一个匹配记录的成本
• 8.30 = 总成本 (Total Cost)
  • 返回所有结果行的总成本
  • 包括：启动成本 + 处理所有数据的成本
  • 这是优化器用来比较不同执行计划的主要指标
• rows=1 = 预估行数
  • 优化器基于统计信息估算会返回1行
  • 基于表统计信息、WHERE条件选择性等计算
• width=36 = 平均行宽度
  • 每行数据的平均字节数为 36 字节
  • 用于内存使用和I/O成本估算

2.2. actual time=0.123..0.234 rows=1 loops=1

这部分是实际执行时记录的真实数据（仅 EXPLAIN ANALYZE 或 EXPLAIN (ANALYZE, ...) 会输出）：

• 0.123 ms = 首行返回时间
  • 实际开始返回第一行数据的时间
  • 对应启动成本的实际耗时
• 0.234 ms = 所有行返回完成时间
  • 返回所有结果行的总时间
  • 对应总成本的实际耗时
• actual rows=1 = 实际返回行数
  • 真实返回了 1 行数据
  • 与预估的 rows=1 完全一致（估算准确）
• loops=1 = 执行循环次数
  • 这个节点被执行了1次
  • 在嵌套循环中，内层节点的 loops 可能 > 1

3. Index Scan、BitMap scan

在数据库执行计划中，Index Scan（索引扫描）和 Bitmap Index Scan（位图索引扫描）是两种不同的索引使用方式，核心区别在于数据访问方式和适用场景。

3.1. 核心原理

3.1.1. Index Scan（索引扫描）

• 方式：直接按照索引的物理结构（如B树的顺序）逐条扫描索引条目，直接定位并访问符合条件的表数据行。
• 过程：
  1. 从索引中找到满足条件的条目（包含索引键值和对应表行的物理地址）；
  2. 按照索引顺序，通过物理地址直接访问表中的数据行（可能随机访问磁盘）；
  3. 若有过滤条件（如WHERE子句中的其他条件），在内存中进一步过滤数据。

3.1.2. Bitmap Index Scan（位图索引扫描）

第一阶段：Bitmap Index Scan（位图索引扫描）先通过索引生成一个位图（Bitmap），记录特定数据块是否包含符合条件的行，每个位代表一个数据块（通常8KB）。

数据块位图示例：
Block 1: [1] - 包含符合条件的行
Block 2: [0] - 不包含符合条件的行  
Block 3: [1] - 包含符合条件的行
Block 4: [0] - 不包含符合条件的行
Block 5: [1] - 包含符合条件的行

Bitmap只告诉我们哪些数据块可能有符合条件的行，但数据块内部可能包含不符合条件的行，因此必须逐行再次检查所有过滤条件。

• 过程：
  1. 从索引中找到满足条件的数据块，用位图标记这些块的位置
  2. 若有多个条件（如 WHERE A=? AND B=?），可通过BitmapAnd/BitmapOr组合多个位图（快速计算交集 / 并集），得到最终的 “符合所有条件的数据块” 标记。
  3. 最后根据位图一次性批量访问表中所有符合条件的数据块（顺序访问磁盘，减少 IO 开销）。
     第二阶段：Bitmap Heap Scan（位图堆扫描）通常与 Bitmap Index Scan 配合使用
     拿到标记了目标数据块的位图后，Bitmap Heap Scan 会按位图指示，批量访问这些数据块，并从块中提取出真正符合条件的行，此时需要二次过滤，因为一个块中可能只有部分行符合条件。此时会访问标记的数据块，逐行检查，对每一行执行Recheck Cond。

3.2. 关键区别

3.3. 适用场景

3.3.1. Index Scan更优的场景

• 结果集很小（如通过唯一索引查询单行数据或limit查询少量数据）；
• 索引顺序与查询需要的排序顺序一致（可避免额外排序）；
• 仅需访问索引包含的字段（即 “索引覆盖查询”，无需回表）。

3.3.2. Bitmap Index Scan更优的场景

• 查询包含多个条件（如WHERE A=? AND B=?），且每个条件都有对应的索引；
• 结果集中等大小（既不适合全表扫描，也不适合逐条索引扫描）；
• 表数据在磁盘上存储较集中（如按顺序插入的表），批量访问效率高。

3.3.3. 举例说明

• 若查询 WHERE id = 100（id是主键），数据库会选择 Index Scan：直接通过主键索引定位到单行，效率最高。
• 若查询 WHERE status = 'active' AND create_time > '2023-01-01'，且status和create_time都有索引，数据库可能选择 Bitmap Index Scan：
  1. 分别通过两个索引生成 “状态为active的行” 和 “创建时间符合条件的行” 的位图；
  2. 用BitmapAnd计算两个位图的交集（同时满足两个条件的行）；
  3. 按位图批量读取这些行，比两次Index Scan更高效。

3.3.4. 总结

• Index Scan 是 “点对点” 的精准访问，适合少量数据；
• Bitmap Index Scan 是 “批量” 的范围访问，适合多条件、中量数据。

Bitmap Index Scan 的设计初衷是通过批量访问表数据减少磁盘 IO 开销：

• 当结果集较大时，位图可以将分散的行位置 “聚合”，转化为连续的磁盘块访问（顺序 IO），比 Index Scan 的随机 IO 更高效。
• 但当结果集很小时（如仅匹配 10 行），即使生成位图，也难以形成连续的磁盘块访问，批量访问的优势完全无法体现。此时，Index Scan 直接通过索引定位单行的随机 IO 开销，反而比 “生成位图 + 批量访问” 的总开销更小。

3.3.5. 实际案例

plan A

SELECT COUNT(1) 
FROM RESOURCE_TRACES rt1
WHERE rt1.RESOURCE_ID = ANY (
    SELECT rt2.RESOURCE_ID 
    FROM RESOURCE_TRACES rt2
    WHERE rt2.RESOURCE_TYPE IN ('nat_gateway', 'public-nat-snat-rules', 'public-nat-dnat-rules')
      AND rt2.TIMESTAMP < (NOW() - 90)
      AND rt2.ACTION = 'delete'
    LIMIT 2000
);

Aggregate  (cost=7080.06..7080.07 rows=1 width=0) (actual time=563.674..563.674 rows=1 loops=1)
  Output: COUNT(1)
  Buffers: shared hit=150383 read=13
  ->  Nested Loop  (cost=4049.00..5918.89 rows=464467 width=0) (actual time=563.671..563.671 rows=0 loops=1)
        Buffers: shared hit=150383 read=13
        ->  

  (cost=4049.00..4053.15 rows=415 width=37) (actual time=563.670..563.670 rows=0 loops=1)
              Output: "ANY_subquery".RESOURCE_ID
              Buffers: shared hit=150383 read=13
              ->  Subquery Scan on "ANY_subquery"  (cost=0.01..4044.00 rows=2000 width=37) (actual time=563.666..563.666 rows=0 loops=1)
                    Output: "ANY_subquery".RESOURCE_ID, "ANY_subquery".RESOURCE_ID
                    Buffers: shared hit=150383 read=13
                    ->  Limit  (cost=0.01..4024.00 rows=2000 width=37) (actual time=563.664..563.664 rows=0 loops=1)
                          Output: PUBLIC.RESOURCE_TRACES.RESOURCE_ID
                          Buffers: shared hit=150383 read=13
                          ->  Index Scan using IX_RESOURCE_TRACES_RESOURCE_TYPE_TIMESTAMP on PUBLIC.RESOURCE_TRACES  (cost=0.01..143256.13 rows=71201 width=37) (actual time=563.663..563.663 rows=0 loops=1)
                                Output: PUBLIC.RESOURCE_TRACES.RESOURCE_ID
                                Index Cond: (((PUBLIC.RESOURCE_TRACES.RESOURCE_TYPE)::TEXT = ANY ('{nat_gateway,public-nat-snat-rules,public-nat-dnat-rules}'::TEXT[])) AND ((NOW() - 90::DOUBLE PRECISION) > PUBLIC.RESOURCE_TRACES."TIMESTAMP"))
                                Filter: ((PUBLIC.RESOURCE_TRACES.ACTION)::TEXT = 'delete'::TEXT)
                                Rows Removed by Filter: 124536
                                Buffers: shared hit=150383 read=13
        ->  Index Only Scan using IX_RESOURCE_TRACES_RESOURCE_ID on PUBLIC.RESOURCE_TRACES  (cost=0.00..4.45 rows=5 width=37) (never executed)
              Output: PUBLIC.RESOURCE_TRACES.RESOURCE_ID
              Index Cond: (PUBLIC.RESOURCE_TRACES.RESOURCE_ID = ("ANY_subquery".RESOURCE_ID)::TEXT)
              Heap Fetches: 0
Total runtime: 563.881 ms

plan B

SELECT COUNT(1) 
FROM RESOURCE_TRACES rt1
WHERE rt1.RESOURCE_ID = ANY (
    SELECT rt2.RESOURCE_ID 
    FROM RESOURCE_TRACES rt2
    WHERE rt2.RESOURCE_TYPE IN ('nat_gateway', 'public-nat-snat-rules', 'public-nat-dnat-rules')
      AND rt2.TIMESTAMP < (NOW() - 90)
      AND rt2.ACTION = 'delete'
);

Aggregate  (cost=191818.22..191818.23 rows=1 width=0) (actual time=1469.954..1469.954 rows=1 loops=1)
  Output: COUNT(1)
  Buffers: shared hit=5980 read=19165
  ->  Nested Loop  (cost=130613.08..189749.08 rows=827658 width=0) (actual time=1469.950..1469.950 rows=0 loops=1)
        Buffers: shared hit=5980 read=19165
        ->  HashAggregate  (cost=130613.08..130760.68 rows=14760 width=37) (actual time=1469.950..1469.950 rows=0 loops=1)
              Output: PUBLIC.RESOURCE_TRACES.RESOURCE_ID
              Buffers: shared hit=5980 read=19165
              ->  Bitmap Heap Scan on PUBLIC.RESOURCE_TRACES  (cost=59991.05..130435.08 rows=71201 width=37) (actual time=1469.919..1469.919 rows=0 loops=1)
                    Output: PUBLIC.RESOURCE_TRACES.RESOURCE_ID, PUBLIC.RESOURCE_TRACES.RESOURCE_ID
                    Recheck Cond: (((PUBLIC.RESOURCE_TRACES.RESOURCE_TYPE)::TEXT = ANY ('{nat_gateway,public-nat-snat-rules,public-nat-dnat-rules}'::TEXT[])) AND ((NOW() - 90::DOUBLE PRECISION) > PUBLIC.RESOURCE_TRACES."TIMESTAMP") AND ((PUBLIC.RESOURCE_TRACES.ACTION)::TEXT = 'delete'::TEXT))
                    Buffers: shared hit=5980 read=19165
                    ->  BitmapAnd  (cost=59991.05..59991.05 rows=71201 width=0) (actual time=1454.039..1454.039 rows=0 loops=1)
                          Buffers: shared hit=4139 read=18722
                          ->  Bitmap Index Scan on IX_RESOURCE_TRACES_RESOURCE_TYPE_TIMESTAMP  (cost=0.00..7105.65 rows=157120 width=0) (actual time=113.143..113.143 rows=149721 loops=1)
                                Index Cond: (((PUBLIC.RESOURCE_TRACES.RESOURCE_TYPE)::TEXT = ANY ('{nat_gateway,public-nat-snat-rules,public-nat-dnat-rules}'::TEXT[])) AND ((NOW() - 90::DOUBLE PRECISION) > PUBLIC.RESOURCE_TRACES."TIMESTAMP"))
                                Buffers: shared hit=4138 read=857
                          ->  Bitmap Index Scan on IX_RESOURCE_TRACES_ACTION  (cost=0.00..52849.55 rows=4735589 width=0) (actual time=1253.611..1253.611 rows=4897280 loops=1)
                                Index Cond: ((PUBLIC.RESOURCE_TRACES.ACTION)::TEXT = 'delete'::TEXT)
                                Buffers: shared hit=1 read=17865
        ->  Index Only Scan using IX_RESOURCE_TRACES_RESOURCE_ID on PUBLIC.RESOURCE_TRACES  (cost=0.00..3.95 rows=5 width=37) (never executed)
              Output: PUBLIC.RESOURCE_TRACES.RESOURCE_ID
              Index Cond: (PUBLIC.RESOURCE_TRACES.RESOURCE_ID = (PUBLIC.RESOURCE_TRACES.RESOURCE_ID)::TEXT)
              Heap Fetches: 0
Total runtime: 1471.042 ms

plan a中添加了limit，数据库引擎分析认为最终查询结果数量较少，选了直接使用index scan的方式。

plan b中Bitmap Index Scan 的核心步骤是先创建“resource_type和timestamp满足条件的行”以及“action满足条件的行”的位图（Bitmap），这个过程本身存在固定成本。action匹配了接近500w行，resource_type和timestamp匹配接近16万行，而最终BitmapAnd后的结果是零，这个生成位图的时间完全是浪费的。

• 即使最终只匹配少量行，也需要扫描索引中符合条件的条目，并为这些行在内存中标记对应的位图位置（例如，用二进制位表示 “第 N 行是否符合条件”）。
• 对位图的初始化、位运算（如单个条件的位图生成）需要消耗 CPU 和内存资源，而这些开销对于 “仅返回几行数据” 的场景来说，占比会非常高。

相比之下，Index Scan（索引扫描）可以直接定位到少量符合条件的行，无需生成位图，省去了这部分固定成本。Plan A中通过timestamp和resource_type所有过滤后只剩16w行数据，再实际回表过滤action（这个过程过滤掉了全部的数据）数据。可以考虑进行索引优化

-- 当前索引
IX_RESOURCE_TRACES_RESOURCE_TYPE_TIMESTAMP (RESOURCE_TYPE, TIMESTAMP)

-- 创建包含ACTION的复合索引
CREATE INDEX IX_RESOURCE_TRACES_OPTIMIZED 
ON RESOURCE_TRACES (RESOURCE_TYPE, ACTION, TIMESTAMP);

3.3.5.1. ANY_subquery

• "ANY_subquery"：PostgreSQL 内部给子查询分配的别名
• 当使用 = ANY (SELECT ...) 或 IN (SELECT ...) 时，优化器会创建这个临时的子查询扫描节点，这不是用户定义的别名，而是系统自动生成的
• plan a中的HashAggregate 的作用是：
  1. 对子查询结果去重：确保每个 RESOURCE_ID 只出现一次
  2. 优化连接操作：避免在 Nested Loop 中重复处理相同的值

4. HashAggregate

HashAggregate是 PostgreSQL 中用于执行聚合操作的一种算法，它使用哈希表来分组和计算聚合函数或实现去重目的等。

4.1. 基本工作原理

4.1.1. 哈希表结构

-- 假设查询
SELECT department, COUNT(*), AVG(salary) 
FROM employees 
GROUP BY department;

HashAggregate 会创建哈希表：

Hash Table:
Key (department) → Aggregated Values
"IT"            → {count: 15, sum: 750000}
"Sales"         → {count: 20, sum: 1200000}  
"HR"            → {count: 8,  sum: 320000}

4.1.2. 执行步骤

1. 扫描输入数据的每一行
2. 计算 GROUP BY 列的哈希值
3. 在哈希表中查找对应的组
4. 更新该组的聚合值
5. 最后输出所有组的结果

4.2. HashAggregate vs GroupAggregate

4.3. 实际执行计划示例

4.3.1. 示例1：基本 GROUP BY

EXPLAIN (ANALYZE, BUFFERS) 
SELECT department, COUNT(*), AVG(salary)
FROM employees 
GROUP BY department;

HashAggregate  (cost=180.00..185.00 rows=5 width=20) 
               (actual time=12.345..12.567 rows=5 loops=1)
  Output: department, count(*), avg(salary)
  Group Key: department
  Batches: 1  Memory Usage: 24kB
  Buffers: shared hit=100
  ->  Seq Scan on employees  (cost=0.00..150.00 rows=1000 width=20) 
                             (actual time=0.123..8.456 rows=1000 loops=1)
        Output: department, salary
        Buffers: shared hit=100

关键信息解读：

• Group Key: department - 分组的列
• Batches: 1 - 所有数据在一批处理（内存足够）
• Memory Usage: 24kB - 哈希表占用内存
• rows=5 - 最终输出5个不同的部门

4.3.2. 示例2：去重操作（DISTINCT）

EXPLAIN ANALYZE 
SELECT DISTINCT resource_id FROM resource_traces;

HashAggregate  (cost=4049.00..4053.15 rows=415 width=37) 
               (actual time=563.670..563.670 rows=0 loops=1)
  Output: resource_id
  Group Key: resource_id
  Buffers: shared hit=150383 read=13

这里 HashAggregate 用于：

• 去重操作：DISTINCT 本质上是按所有选择列分组
• Group Key: resource_id 用于去重的列
• rows=0: 输入数据为空，所以输出也为空

4.3.3. 示例3：内存不足时的批处理

HashAggregate  (cost=180.00..185.00 rows=50000 width=20) 
               (actual time=234.567..245.789 rows=50000 loops=1)
  Output: customer_id, sum(amount)
  Group Key: customer_id  
  Batches: 3  Memory Usage: 4096kB  Disk Usage: 15432kB
  ->  Seq Scan on orders

Batches > 1 说明：

• 内存不足以一次性处理所有分组
• 数据被分成3批处理
• 使用了 15MB 的临时磁盘空间

4.4. 不同聚合场景下的 HashAggregate

4.4.1. COUNT DISTINCT

SELECT COUNT(DISTINCT customer_id) FROM orders;

Aggregate  (actual time=45.123..45.123 rows=1 loops=1)
  Output: count(DISTINCT customer_id)
  ->  HashAggregate  (actual time=34.567..41.234 rows=5000 loops=1)
        Output: customer_id
        Group Key: customer_id  -- 先去重
        ->  Seq Scan on orders

4.4.2. 多列分组

SELECT region, department, COUNT(*) 
FROM employees 
GROUP BY region, department;

HashAggregate  (actual time=23.456..25.789 rows=15 loops=1)
  Output: region, department, count(*)
  Group Key: region, department  -- 复合分组键

4.4.3. HAVING 子句

SELECT department, COUNT(*) 
FROM employees 
GROUP BY department 
HAVING COUNT(*) > 10;

HashAggregate  (actual time=12.345..12.456 rows=3 loops=1)
  Output: department, count(*)
  Group Key: department
  Filter: (count(*) > 10)  -- HAVING 条件
  Rows Removed by Filter: 2  -- 2个组被HAVING过滤掉

4.5. 性能调优考虑

4.5.1. work_mem 设置

-- 检查当前设置
SHOW work_mem;  -- 默认通常是 4MB

-- 如果看到 Batches > 1，考虑增加 work_mem
SET work_mem = '64MB';

-- 或者针对特定会话
SET LOCAL work_mem = '128MB';

4.5.2. 何时选择 HashAggregate vs GroupAggregate

-- PostgreSQL 会基于以下因素选择：
-- 1. 数据量大小
-- 2. 是否已按 GROUP BY 列排序
-- 3. work_mem 设置
-- 4. 预估的分组数量

-- 强制使用 GroupAggregate（通过排序）
SELECT department, COUNT(*) 
FROM employees 
ORDER BY department  -- 强制排序，可能选择 GroupAggregate
GROUP BY department;

4.5.3. 监控指标

• Batches: 应该尽量为 1
• Memory Usage: 不应该超过 work_mem
• Disk Usage: 应该避免出现
• actual rows vs estimated rows: 估算准确性