PostgreSQL 高级索引：超越基本 B 树索引

大多数开发者对外键和 WHERE 子句列建立索引。但 PostgreSQL 的索引能力远不止基本的 B 树索引。正确的索引类型可以将 10 秒的查询变为 10 毫秒。

理解索引内部

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM orders
WHERE user_id = 12345 AND status = 'pending'
AND created_at > NOW() - INTERVAL '7 days';

关键指标：

• 实际行数 vs 计划行数：差异大 = 统计信息过时
• 缓冲区命中 vs 读取：缓存命中率
• 实际总时间：真实执行成本

部分索引：你的秘密武器

仅索引行的子集——体积更小、速度更快：

-- 95% 的订单是 'completed'，因此只索引 pending 的订单
-- 结果：索引大小缩小 20 倍

CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';
-- 对于 5000 万行，约 10MB vs 约 500MB 的全索引

-- 查询必须匹配索引谓词
SELECT * FROM orders
WHERE status = 'pending'
AND created_at < NOW() - INTERVAL '1 hour';

-- 软删除模式
CREATE INDEX idx_users_active ON users(email)
WHERE deleted_at IS NULL;

覆盖索引：消除堆获取

-- 查询需要：id, email, name
SELECT id, email, name
FROM users
WHERE email LIKE 'john%' AND status = 'active';

-- 覆盖索引：INCLUDE 非过滤列
CREATE INDEX idx_users_covering ON users(email, status)
INCLUDE (id, name);
-- 结果：仅索引扫描（0 次堆获取！）
-- 比常规索引扫描快 3-10 倍

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, email, name FROM users
WHERE email LIKE 'john%' AND status = 'active';
-- 查找："Index Only Scan" + "Heap Fetches: 0"

表达式索引

-- 不区分大小写的电子邮件搜索
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- 按天截断的时间序列
CREATE INDEX idx_events_date ON events(DATE_TRUNC('day', created_at));
SELECT DATE_TRUNC('day', created_at) as day, COUNT(*)
FROM events
WHERE DATE_TRUNC('day', created_at) >= '2026-01-01'
GROUP BY 1;

-- JSON 字段索引
CREATE INDEX idx_orders_priority ON orders((metadata->>'priority'));
SELECT * FROM orders WHERE metadata->>'priority' = 'high';

GIN 索引：全文搜索和 JSONB

-- 全文搜索设置
ALTER TABLE articles ADD COLUMN search_vector tsvector;

CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);

-- 排序搜索
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles,
     to_tsquery('english', 'postgresql & indexing') query
WHERE search_vector @@ query
ORDER BY rank DESC;

-- JSONB 包含
CREATE INDEX idx_products_attrs ON products USING GIN(attributes);
SELECT * FROM products WHERE attributes @> '{"color": "blue"}';

-- 数组重叠
CREATE INDEX idx_articles_tags ON articles USING GIN(tags);
SELECT * FROM articles WHERE tags && ARRAY['postgresql', 'performance'];

BRIN 索引：时间序列

块范围索引：对于有序数据，比 B 树小 500 倍：

-- 1 亿行仅需 1MB（而 B 树需要 500MB）
CREATE INDEX idx_events_brin ON events
USING BRIN(created_at) WITH (pages_per_range = 32);

-- 对仅追加的时间序列进行高效范围扫描
SELECT * FROM events
WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31';
-- BRIN 仅在数据物理有序时有效！

GiST 索引：空间和范围

-- PostGIS 空间查询
CREATE INDEX idx_stores_location ON stores USING GIST(location);
SELECT name FROM stores
WHERE ST_DWithin(location::geography,
  ST_MakePoint(-122.4194, 37.7749)::geography, 10000);

-- 使用排除约束自动防止冲突
CREATE TABLE reservations (
  room_id int,
  during daterange,
  EXCLUDE USING GIST (room_id WITH =, during WITH &&)
);
-- 在数据库层面防止重复预订！

监控

-- 查找未使用的索引
SELECT tablename, indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size,
  idx_scan AS scans
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- 无锁重建
REINDEX INDEX CONCURRENTLY idx_orders_status;

决策指南

每个索引都会减慢写入速度。在过度索引之前，先测量实际的查询模式。