Holly

Postgres 有哪些索引？怎么用

9 min read

索引，数据库最重要的部分之一，它决定你的查询是起飞，还是卡死（决定你是走 O(log N) 的快速查找，还是 O(N) 的全表扫描“。PostgreSQL 常用的索引类型：

  • B-Tree：默认索引，处理等值、范围、排序（99% 场景用它）
  • GIN：倒排索引，处理 JSONB、数组、全文搜索
  • BRIN：块范围索引，处理超大表（日志、订单等按时间写入的表）

B-Tree 索引

PostgreSQL 的默认索引。想象你查字典：不会从第一页翻到最后，而是先看中间（M 开头），发现 A 在前面，再跳到前半部分中间，反复二分，很快定位。

B-Tree 就是这个思路：一棵多路平衡搜索树。每个节点有多个孩子，节点里的键值有序，从根到叶子的路径长度相同。

查找 movie_id = 3：
         [2 | 5]           ← 根：<2 走左，2~5 走中，>5 走右
        /   |   \
    [1]   [3|4]   [6]      ← 中间层：命中 3~4 区间
           |
        [3] → 行指针       ← 叶子：找到 key=3，取到数据

从根开始，每次比较节点里的键，决定走哪个指针，直到叶子。整个过程约 log N 次 I/O。千万行的表，3-4 次就够了。

B-Tree 处理：

  • 等值查询：WHERE user_id = 123
  • 范围查询：WHERE created_at > '2024-01-01'、BETWEEN
  • 排序：ORDER BY created_at（索引本身有序）
  • 前缀匹配：LIKE 'foo%'（但 LIKE '%foo' 不行）
-- 默认就是 B-Tree
CREATE INDEX idx_users_email ON users (email);
 
-- 多列复合索引
CREATE INDEX idx_orders_status_created ON orders (status, created_at);
 
-- 降序索引
CREATE INDEX idx_movies_rating ON movies (rating DESC);

GIN 索引（倒排索引）

GIN (Generalized Inverted Index) 是”倒排索引”，像书本末尾的索引页。传统索引记录”第 5 页有哪些词”，GIN 反过来记录“‘PostgreSQL’ 这个词出现在哪些页”。

假设商品表的 tags 列是数组 ['科幻', '太空', '未来']，你想查”包含’太空’的所有商品”：

WHERE tags @> ARRAY['太空']

B-Tree 没法处理「一列里有多个值，查某个值在不在里面」的场景。GIN 为每个标签建立条目，记录”哪些行包含这个标签”。查询时直接定位”太空”对应的行列表。

适合场景：

  • 数组：tags @> ARRAY['太空']
  • JSONB：attributes @> '{"color": "red"}'
  • 全文检索：to_tsvector(content) @@ to_tsquery('PostgreSQL')
-- 数组
CREATE INDEX idx_products_tags ON products USING GIN (tags);
 
-- JSONB
CREATE INDEX idx_products_attrs ON products USING GIN (attributes);
 
-- JSONB（只支持 @> 查询，但更小更快）
CREATE INDEX idx_products_attrs_ops ON products USING GIN (attributes jsonb_path_ops);
 
-- 全文搜索
CREATE INDEX idx_articles_search ON articles 
USING GIN (to_tsvector('english', title || ' ' || body));

常见错误：

-- ❌ B-Tree 不支持 @> 操作符，全表扫描
CREATE INDEX products_attrs_idx ON products (attributes);
SELECT * FROM products WHERE attributes @> '{"color": "red"}';
 
-- ✅ 必须用 GIN
CREATE INDEX products_attrs_idx ON products USING GIN (attributes);

BRIN 索引（块范围索引）

BRIN (Block Range INdex) 是”摘要索引”。不记录每一行，只记录”这堆数据块里，最小值是 1，最大值是 100”。

假设你有 10TB 的日志表，按时间顺序插入。查 WHERE created_at > '2023-01-01'：

  • B-Tree 会建几百 GB 的索引
  • BRIN 只记录每 128 个块的 min/max 时间戳，索引可能只有几 MB

查询时 BRIN 看到”前 10000 个块 max 都是 2022 年”，直接跳过；从第 10001 个块开始扫。

适合场景：超大表 + 按某列顺序插入（时间、自增 ID）

  • ✅ 日志表（按时间）
  • ✅ 订单表（按 order_id 自增）
  • ❌ 经常更新/删除的表（物理上不再有序）

特点：

  • 索引极小（可能 1/100 于 B-Tree）
  • 查询速度介于全表扫和 B-Tree 之间
  • 写入快
-- 日志表
CREATE INDEX idx_logs_created ON logs USING BRIN (created_at);
 
-- 订单表
CREATE INDEX idx_orders_id ON orders USING BRIN (id);
 
-- 调整块范围（默认 128 页）
CREATE INDEX idx_logs_created ON logs USING BRIN (created_at) WITH (pages_per_range = 256);

WHERE 和 JOIN 列要有索引

没索引就全表扫描，一行行检查。有索引，直接跳到符合条件的行。大表上能快 100–1000 倍。

-- ❌ 外键无索引，JOIN 和约束检查都慢
CREATE TABLE orders (
  id bigint PRIMARY KEY,
  customer_id bigint REFERENCES customers(id)
);
 
-- ✅ 外键建索引
CREATE INDEX orders_customer_id_idx ON orders (customer_id);

WHERE 和 JOIN 里的列，外键列，都要建索引。

复合索引：顺序很重要

多列条件别建多个单列索引，优化器往往只用一个。建复合索引，把等值列放前面，范围/排序列放后面。

-- ❌ 两个单列索引，只能用一个
CREATE INDEX orders_status_idx ON orders (status);
CREATE INDEX orders_created_idx ON orders (created_at);
 
-- ✅ 复合索引
CREATE INDEX orders_status_created_idx ON orders (status, created_at);

最左前缀原则：索引 (a, b, c) 能用于 WHERE a = 1、WHERE a = 1 AND b = 2、WHERE a = 1 AND b = 2 AND c > 3，但用不了 WHERE b = 2 或 WHERE c > 3（跳过了前面的列）。

上面的索引能加速：

  • WHERE status = 'pending'
  • WHERE status = 'pending' AND created_at > '2024-01-01'

加速不了：

  • WHERE created_at > '2024-01-01'（没 status）

覆盖索引：避免回表

索引命中后，如果查询的列不在索引里，数据库还要回表取数据（额外 I/O）。覆盖索引 (Covering Index) 把查询需要的列都放进索引，数据库只扫索引不回表，能快 2–5 倍。

PostgreSQL 用 INCLUDE 把额外的列附加到索引里。

-- ❌ name 要回表
CREATE INDEX users_email_idx ON users (email);
SELECT email, name FROM users WHERE email = 'a@b.com';
 
-- ✅ INCLUDE name，不回表
CREATE INDEX users_email_idx ON users (email) INCLUDE (name);

部分索引：只索引部分行

只关心部分行（未删除、待处理等）？用部分索引 (Partial Index)：CREATE INDEX ... WHERE 条件。

索引更小（可能 1/5 到 1/20），写入更快，查询更快。

-- ❌ 包含已删除的
CREATE INDEX users_email_idx ON users (email);
 
-- ✅ 只索引未删除
CREATE INDEX users_active_email_idx ON users (email) WHERE deleted_at IS NULL;

常见场景：WHERE deleted_at IS NULL、WHERE status = 'pending'、WHERE phone IS NOT NULL。

表达式索引：对付函数包列

查询条件里对列做了函数（LOWER(email)、DATE(created_at)），普通索引失效。解决办法：对表达式建索引。

-- 查询做了 LOWER
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
 
-- 对 LOWER(email) 建索引
CREATE INDEX idx_lower_email ON users(LOWER(email));

常见场景：LOWER(column)、DATE(created_at)、(attributes->>'color')。

索引维护

更新统计信息：优化器选索引靠统计信息，数据变化后定期更新，否则可能选错。

ANALYZE users;   -- 单表
ANALYZE;         -- 所有表

清理无用索引：索引不免费，每次写操作都要更新所有索引。定期检查未使用的：

SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 
  AND indexname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

idx_scan = 0 说明从未被扫描过。

常见陷阱：

条件里对列做运算 → 索引失效：

-- ❌ 索引失效
SELECT * FROM orders WHERE created_at + INTERVAL '1 day' > NOW();
 
-- ✅ 改写条件
SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '1 day';

SELECT * 难用覆盖索引，尽量只 SELECT 需要的列。

JSONB/数组必须用 GIN，B-Tree 不支持 @> 等操作。

场景速查

场景索引
WHERE 等值/范围、JOINB-Tree
JSONB、数组、全文搜索GIN
超大表按时间/自增写入BRIN
多列条件复合索引（等值在前）
只查部分行部分索引 + WHERE
避免回表覆盖索引 (INCLUDE)
函数包列表达式索引

Graph View