id   | name       | type_1   | type_2   | generation | is_legendary | base_attack
1    | Bulbasaur  | Grass    | Poison   | 1          | false        | 49
4    | Charmander | Fire     | NULL     | 1          | false        | 52
25   | Pikachu    | Electric | NULL     | 1          | false        | 55
150  | Mewtwo     | Psychic  | NULL     | 1          | true         | 110

name          row
Bulbasaur   → 1
Charmander  → 4
Mewtwo      → 150
Pikachu     → 25

在宝可梦表上建 type_1 和 type_2 的复合索引：

CREATE INDEX ON pokemon (type_1, type_2);

这个索引对以下查询有效：

SELECT * FROM pokemon WHERE type_1 = 'Water';
SELECT * FROM pokemon WHERE type_1 = 'Water' AND type_2 = 'Flying';

但对这个查询无效：

SELECT * FROM pokemon WHERE type_2 = 'Flying';

原因在索引的排序结构。复合索引 (type_1, type_2) 先按 type_1 排，再在每个 type_1 组内按 type_2 排：

Bug      → Flying   → [Butterfree, Beedrill, ...]
         → Poison   → [Venonat, Spinarak, ...]
Electric → NULL     → [Pikachu, Raichu, ...]
         → Flying   → [Zapdos, ...]
Fire     → NULL     → [Charmander, Vulpix, ...]
         → Flying   → [Charizard, Moltres, ...]
Grass    → Poison   → [Bulbasaur, Oddish, ...]
Water    → NULL     → [Squirtle, Psyduck, ...]
         → Flying   → [Wingull, Pelipper, ...]
         → Ground   → [Wooper, ...]

"Flying"散落在 Bug、Electric、Fire、Water 下面，没有统一入口。数据库无法跳到某个位置一次取完所有 Flying 记录。

如果经常单独查 type_2 ，就需要再加一个 type_2 的独立索引。

不区分大小写的搜索很常见：

SELECT * FROM pokemon WHERE lower(name) = 'pikachu';

lower(name) 看起来跟查 name 差不多，但索引是按 name 原始值排序的（Bulbasaur、Charmander...），不是按 lower(name) 排序的。数据库找不到现成的排序结构，只好回退全表扫描。

这适用于任何包裹列的函数。只要比较的左边不是原始的索引列，索引就不参与。隐式类型转换也算—— text 和 integer 比较时会触发静默转换，效果等同于包了一层函数。

解决办法是建函数索引（见下文）。

Postgres 提供了 EXPLAIN ，在任何 SELECT 前加上就能看到执行计划：

EXPLAIN SELECT * FROM pokemon WHERE name = 'Pikachu';

Index Scan using pokemon_name_idx on pokemon
  Index Cond: (name = 'Pikachu'::text)

Index Scan 说明走了索引。再看问题查询：

EXPLAIN SELECT * FROM pokemon WHERE lower(name) = 'pikachu';

Seq Scan on pokemon
  Filter: (lower(name) = 'pikachu'::text)

Seq Scan 就是全表扫描。加上 ANALYZE 可以拿到实际执行时间：

EXPLAIN ANALYZE SELECT * FROM pokemon WHERE name = 'Pikachu';

前面的 lower(name) 问题，解法是直接对表达式建索引：

CREATE INDEX ON pokemon (lower(name));

任何确定性、不可变表达式都可以索引：

CREATE INDEX ON users ((created_at::date));
CREATE INDEX ON pokemon ((base_attack * 2));

但要注意：如果频繁查 lower(name) ，也许该考虑直接把数据存成小写，而不是靠函数索引绕路。函数索引是手段，不是目的。

普通索引覆盖全表所有行。但如果只查其中一小部分，索引其余行就是浪费。

宝可梦大约 1000 种，传说宝可梦只有约 80 种（不到 10%）。如果应用有个"显示所有传说宝可梦"的功能，建 is_legendary 和 name 的复合索引虽然能用，但索引里 920 条非传说记录纯粹是摆设。

部分索引只包含满足条件的行：

CREATE INDEX ON pokemon (name) WHERE is_legendary = true;

索引从 1000 条缩减到 80 条。查 WHERE is_legendary = true 走索引，查 WHERE is_legendary = false 走全表扫描——正好，因为 false 匹配几乎所有行，索引帮不上忙。

另一个典型场景是软删除：

CREATE INDEX ON users (email) WHERE deleted_at IS NULL;

软删除的行几乎不会被查询，不把它们排除出索引只是白占空间。

数据库用索引找到行后，还要去表中取查询需要的其他列——两次查找。但如果索引里已经包含了查询需要的所有列，就不需要访问表了。这就是覆盖索引，在 EXPLAIN 中显示为 Index Only Scan 。

前面的部分索引恰好就是一个覆盖索引：

SELECT name FROM pokemon WHERE is_legendary = true;

索引在 name 上，查询也只要 name ，全部命中。

也可以用 INCLUDE 显式构建覆盖索引：

CREATE INDEX ON pokemon (name) INCLUDE (base_attack);

这样以下查询可以纯索引完成：

SELECT name, base_attack FROM pokemon WHERE name = 'Pikachu';

为什么不直接把 base_attack 放进索引列？因为索引列是用来排序和搜索的。把 base_attack 加进索引列意味着每次写入都要按 (name, base_attack) 排序，但你从来不按 base_attack 搜索，这排序就是白做的。 INCLUDE 的意思是"带上这列，但别排序"。

INCLUDE 更实际的用途有两个：

1. 数据类型不支持 B-tree 操作符的列（如 box 类型），不能作为索引键列，但可以 INCLUDE
2. 唯一索引中附加列而不改变唯一性语义： CREATE UNIQUE INDEX ON users (email) INCLUDE (user_id) 只在 email 上强制唯一，同时覆盖需要 user_id 的查询