postgesql索引计划

PostgreSQL 官方支持以下几种核心索引类型：

 
索引类型核心特点与适用场景
B-tree 默认索引，适合等值查询 (=)、范围查询 (>, <, BETWEEN)、排序 (ORDER BY) 以及前缀匹配 (LIKE 'abc%')。
Hash 仅支持等值查询 (=)，在等值查询场景下比 B-tree 更快。
GiST 一种通用索引框架，适用于几何/地理数据类型（如坐标、多边形）的邻近搜索，也支持全文搜索。
SP-GiST 类似 GiST，但适合非平衡数据结构，如平面上的点（四叉树）、网络地址等。
GIN 倒排索引，专为包含多个键值的数据类型设计。非常适合全文搜索和 JSONB/数组 的包含查询。
BRIN 块范围索引，适合超大型表，且索引列的值与物理存储顺序相关（如自增ID、时间戳），索引极小。
Bloom 通过布隆过滤器提供快速等值查询，适合有大量列且需要组合等值查询的场景。

此外，还有一些特殊类型的索引：

    • 唯一索引 (Unique Index)：确保索引列值的唯一性。目前只有 B-tree 索引可以声明为唯一。

    • 部分索引 (Partial Index)：只针对表中满足特定条件的行子集建立索引。

    • 表达式索引 (Expression Index)：建立在函数或表达式的结果上，而不是直接建立在列上。

    • 多列索引 (Multicolumn Index)：建立在多个列上，但只有 B-tree、GiST、GIN 和 BRIN 这四种类型支持。

      1. 常规场景首选 B-tree：对于 id、ban_id、gradeid 这类字段上的等值、范围或排序查询，默认的 B-tree 索引是最佳选择。

      2. 唯一性约束用唯一索引：如果需要保证 student.number（学号）唯一，应创建一个唯一索引。PostgreSQL 在执行 PRIMARY KEY 或 UNIQUE 约束时，会自动创建同名的唯一 B-tree 索引。

      3. 特殊数据类型选专用索引：如果未来有地理空间数据（如坐标），应使用 GiST 索引；如果需要进行全文检索，则应使用 GIN 索引。

      4. 超大型表考虑 BRIN：如果表数据量极大（如千万级以上），且查询字段与物理存储顺序相关（如自增的 id），BRIN 索引能以极小的空间开销提供不错的性能。

简单来说，对于大部分常规业务，默认的 B-tree 索引已经能满足 90% 以上的需求

 

 

图片

 explain select count(*) from student where ban_id=9;

这个查询的执行分为 两步，最终统计出 ban_id = 9 的学生总数：

  1. 索引扫描 → 2. 堆表扫描 → 3. 聚合计数

🔍 逐行解读

第一行（最顶层）：

text
Aggregate (cost=101.25..101.26 rows=1 width=8)

  • Aggregate：表示最终执行 count(*) 聚合操作。

  • cost=101.25..101.26：预估的启动成本 101.25，总成本 101.26（成本单位是“顺序页读取次数”，数值越小越快）。

  • rows=1：最终输出只有 1 行（即计数值）。

  • width=8：结果行的字节宽度（count 结果是 8 字节的 bigint）。

第二行（Bitmap Heap Scan）：

text
->  Bitmap Heap Scan on student  (cost=4.67..101.12 rows=50 width=0)
    Recheck Cond: (ban_id = 9)

  • Bitmap Heap Scan：从表中读取符合条件的行。这里的“Bitmap”意味着它通过一个位图快速定位到数据页。

  • cost=4.67..101.12：这一步的启动成本 4.67，总成本 101.12。

  • rows=50：预估会找到 50 行符合 ban_id = 9 的记录（这是优化器的估算值，实际可能不同）。

  • Recheck Cond：由于位图索引可能把一些不符合条件的页也标记了，所以在读取堆表时需要二次检查条件 ban_id = 9 确保精确。

第三行（Bitmap Index Scan）：

text
->  Bitmap Index Scan on student_ban_id_idx  (cost=0.00..4.66 rows=50 width=0)
    Index Cond: (ban_id = 9)

  • Bitmap Index Scan：在索引 student_ban_id_idx 上查找 ban_id = 9 的条目。它并不是直接取出行的物理地址，而是先在内存中建立一个位图，标记哪些数据页（或行）可能包含符合条件的记录。

  • cost=0.00..4.66：扫描索引的成本很低。

  • rows=50：索引扫描预估找到 50 行。

  • Index Cond：索引条件就是 ban_id = 9。

🤔 为什么是 Bitmap Scan 而不是普通的 Index Scan？

PostgreSQL 的优化器判断：如果符合条件的数据可能分布在多个数据页上，使用 位图扫描 会更高效。它这样做：

    1. 先扫描索引，构建一个位图，标记出所有包含目标行的数据页。

    2. 然后按照位图顺序读取这些数据页，每个页上的行再过滤一遍。

 

 

图片

 

第一行（顶层聚合）：

text
Aggregate (cost=101.25..101.26 rows=1 width=8) 
          (actual time=0.071..0.072 rows=1 loops=1)

  • actual time=0.071..0.072：这一步实际用了 0.071 毫秒启动，0.072 毫秒完成。非常快，说明内存处理。

  • rows=1：最终输出 1 行（就是 count 总数）。

  • loops=1：该步骤只执行了 1 次。

第二行（Bitmap Heap Scan - 从表中读取数据）：

text
->  Bitmap Heap Scan on student (cost=4.67..101.12 rows=50 width=0) 
    (actual time=0.050..0.062 rows=100 loops=1)
    Recheck Cond: (ban_id = 9)
    Heap Blocks: exact=2

这是最关键的信息差异：

  • 预估 rows=50，但实际 rows=100 —— 说明优化器的统计信息不够新，实际匹配的行数是 100 行，而之前 ANALYZE 未执行时，优化器预估只有 50 行。

  • actual time=0.050..0.062：这步实际用了 0.05 毫秒启动，0.062 毫秒完成，效率极高。

  • Heap Blocks: exact=2：需要从磁盘/缓存中读取 2 个数据页（每个数据页通常 8KB），而且全部命中（exact 表示没有误判需要重新检查的页）。这 2 个页里包含了所有 100 条符合条件的记录。

第三行（Bitmap Index Scan - 扫描索引）：

text
->  Bitmap Index Scan on student_ban_id_idx (cost=0.00..4.66 rows=50 width=0) 
    (actual time=0.036..0.036 rows=100 loops=1)
    Index Cond: (ban_id = 9)

  • 实际 rows=100，与上面的堆扫描一致，说明索引中确实有 100 条 ban_id=9 的记录。

  • actual time=0.036..0.036：仅用 0.036 毫秒就完成了索引扫描。

  • Bitmap Index Scan 不会直接访问数据行，而是先在内存中构建一个位图（标记哪些数据页包含目标行），然后交给 Bitmap Heap Scan 去读取。

额外信息（计划与执行总耗时）：

text
Planning Time: 0.144 ms    -- 生成执行计划花了 0.144 毫秒
Execution Time: 0.120 ms   -- 实际执行查询花了 0.120 毫秒

总响应时间约 0.26 毫秒，非常快。

  1. 索引生效且高效：查询走了索引 student_ban_id_idx，没有全表扫描，总执行时间仅 0.12 毫秒。

  2. 实际行数与预估不符：优化器预估值是 50，实际是 100。这说明表的统计信息可能过期了（比如插入大量数据后没执行 ANALYZE）。虽然不影响结果，但精确的统计能帮助优化器在复杂查询中做出更好的决策。

  3. I/O 开销极小：只读了 2 个数据页，说明 ban_id=9 的数据物理存储很紧凑。

 

 

 

 

 

再看下多字段检索 索引计划

gradeid 与ban_id  都是单独加的索引
explain select
	s.id,
	s."name",
	s.age,
	s.gradeid,
	s.ban_id
from
	public.student s
inner join public.banji b on
	s.ban_id = b.id
	
left join public.geade g on
	s.gradeid = g.id
where
	s.ban_id = 9
	and s.gradeid = 1;

 

图片

 

一、整体结构（由下往上读）

text
Nested Loop (cost=9.57..15.85 rows=1 width=35)
  -> Bitmap Heap Scan on student s  (内层）
  -> Seq Scan on banji b            （外层）

这是一个 嵌套循环连接（Nested Loop）：

  • 外层：顺序扫描 banji 表（Seq Scan on banji），找到满足 id = 9 的班级。

  • 内层：对 banji 的每一行（这里只有 1 行），在 student 表上执行一次 位图堆扫描，查找同时满足 ban_id = 9 和 gradeid = 1 的学生。

二、内层：student 表的检索方式

text
Bitmap Heap Scan on student s
  Recheck Cond: ((ban_id = 9) AND (gradeid = 1))
  -> BitmapAnd (cost=9.57..9.57 rows=1 width=0)
      -> Bitmap Index Scan on student_ban_id_idx
          Index Cond: (ban_id = 9)
      -> Bitmap Index Scan on student_gradeid_idx
          Index Cond: (gradeid = 1)

1. 两个独立的索引扫描

  • 先分别扫描 student_ban_id_idx（查找 ban_id=9）和 student_gradeid_idx（查找 gradeid=1）。

  • 每个索引扫描都会生成一个 位图（bitmap），标记哪些数据页包含符合条件的行。

2. BitmapAnd 操作

  • 将两个位图进行 与（AND）运算，得到同时满足两个条件的数据页集合。

  • 这个操作的成本为 cost=9.57，预估返回 rows=1 行（实际可能是 0 或少量）。

3. Bitmap Heap Scan

  • 根据 BitmapAnd 产生的位图，去读取对应的数据页。

  • 由于位图是“页级别”的，每个页上可能有一些不符合条件的行，所以需要 Recheck Cond 进行二次过滤，确保最终结果完全符合 (ban_id=9 AND gradeid=1)。

三、外层：banji 表

text
Seq Scan on banji b
  Filter: (id = 9)

  • 因为 banji 表很小（只有 100 条），优化器选择 全表扫描，然后逐行过滤 id = 9。

  • 成本很低（cost=0.00..2.25），这是合理的。

四、连接过程

text
Nested Loop (cost=9.57..15.85 rows=1 width=35)

  • 外层扫描 banji，得到 id=9 的那一行（只有 1 行）。

  • 然后用这一行去驱动内层的 Bitmap Heap Scan，即对于这个 banji 行，在 student 表中查找所有 ban_id=9 AND gradeid=1 的记录。

  • 最后输出两表连接的结果（width=35 表示结果行的宽度）。

 

 

使用了索引，没有全表扫描 student。 BitmapAnd 需要读取两个索引，并做位图运算，成本略高于单个组合索引。
banji 是全表扫描，但表很小，影响可忽略。 预估 rows=1，但若实际符合条件的 student 行数很多（比如上百行），嵌套循环的效率会下降。
整体成本不到 16，响应应该很快。 如果经常出现 ban_id=9 AND gradeid=1 这种查询，组合索引会更优。

给ban_id, gradeid加上组合索引

CREATE INDEX student_ban_grade_idx ON student (ban_id, gradeid);

图片

 

新旧计划对比

 
对比项旧计划（两个单列索引）新计划（组合索引）
student 表访问方式 Bitmap Heap Scan + BitmapAnd 直接 Index Scan
索引使用 先后扫描两个单列索引，做位图与运算 直接使用一个组合索引，一步到位
成本估算 cost=9.57..13.59（内层） cost=0.29..8.30（内层），明显更低
额外操作 有 Recheck Cond 二次检查 无需二次检查，索引直接包含条件

新计划逐层解析

text
Nested Loop (cost=0.29..10.56 rows=1 width=35)
  -> Index Scan using student_ban_grade_idx on student s  (cost=0.29..8.30 rows=1 width=35)
        Index Cond: ((ban_id = 9) AND (gradeid = 1))
  -> Seq Scan on banji b  (cost=0.00..2.25 rows=1 width=8)
        Filter: (id = 9)

内层：student 表

  • Index Scan using student_ban_grade_idx：直接通过组合索引 (ban_id, gradeid) 定位到满足两个条件的具体行。

  • 索引条件（Index Cond）：ban_id = 9 AND gradeid = 1，完全匹配索引的前两列，因此索引可以高效地直接返回结果行的 TID（物理地址），然后回表读取完整行。

  • 无 Bitmap 中间步骤：避免了构建位图和位图与运算的开销，也无需二次过滤（Recheck 消失）。

外层：banji 表

  • Seq Scan on banji：依然全表扫描，但 banji 仅有 100 行，全表扫描成本仅 0.00..2.25，是合理的选择。

  • 连接顺序：优化器将 banji 作为外表，student 作为内表。因为 banji 结果只有一行（id=9），所以 Nested Loop 只需执行一次内层索引扫描，性能极佳。

  • 单一索引访问路径：一个 B-tree 索引可以直接处理多列等值条件，无需多次读取索引页。

  • 减少 I/O：旧计划需要读取两个索引页并写入临时位图，新计划只需一次索引查找，减少了内存和磁盘操作。

  • 统计信息更准确：组合索引的统计信息（如 n_distinct 和 most_common_vals）更精确地反映多列联合分布，帮助优化器更准估算行数。

posted @ 2026-07-10 22:37  余生请多指教ANT  阅读(2)  评论(0)    收藏  举报