PostgreSQL 索引详解：如何将查询速度提升 10 倍

为什么你的查询很慢

一个从 1000 万行表中返回 10 行数据却耗时 2 秒的查询，几乎肯定存在索引问题。PostgreSQL 的任务是尽可能高效地找到你需要的行——但只有当你提供了正确的索引时，它才能高效地做到这一点。

本指南将解释 PostgreSQL 索引的工作原理、何时使用每种类型，以及如何诊断和修复慢查询。

什么是索引？

索引是 PostgreSQL 在表之外维护的一个独立数据结构，其组织方式允许通过特定列值进行快速查找。

没有索引时，通过 email 查找用户需要顺序扫描——读取表中的每一行：

Seq Scan on users (cost=0.00..184334 rows=1)
  Filter: (email = 'alice@example.com')
  Rows Removed by Filter: 9999999

有了 email 上的索引：

Index Scan on users using idx_users_email
  Index Cond: (email = 'alice@example.com')

区别：扫描 1000 万行 vs. 沿着 B-tree 在大约 20 步内找到一行。

EXPLAIN ANALYZE：诊断慢查询

在创建索引之前，始终从这里开始：

-- 添加 ANALYZE 以实际运行查询并获取真实时间
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.id, u.name, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.country = 'US'
  AND o.created_at > NOW() - INTERVAL '30 days'
  AND o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 20;

阅读 EXPLAIN 输出：

Sort  (cost=42156..42157 rows=1 width=24) (actual time=8234..8234 rows=20)
  Sort Key: o.created_at DESC
  ->  Hash Join  (cost=184334..184340 rows=1 width=24) (actual time=8200..8233 rows=20)
        Hash Cond: (o.user_id = u.id)
        ->  Seq Scan on orders o  (cost=0.00..156000 rows=50000 width=16) (actual time=0.08..7800 rows=50000)
              Filter: (status = 'pending' AND created_at > ...)
              Rows Removed by Filter: 950000
        ->  Hash  (cost=28000..28000 rows=100000 width=12) (actual time=380..380 rows=100000)
              ->  Seq Scan on users u  (cost=0.00..28000 rows=100000 width=12)
                    Filter: (country = 'US')
                    Rows Removed by Filter: 900000

两个顺序扫描都有大量的“Rows Removed by Filter”——这就是我们需要索引的地方。

B-tree 索引（默认，最常用）

最适合：等值、范围、排序、带前缀的 LIKE

-- 单列
CREATE INDEX idx_users_country ON users (country);
CREATE INDEX idx_orders_created_at ON orders (created_at);

-- 复合索引（列顺序很重要！）
-- 在 (user_id, created_at) 上的索引有助于：
-- WHERE user_id = X
-- WHERE user_id = X AND created_at > Y
-- 但不适用于：WHERE created_at > Y（user_id 必须在前）
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at);

-- 用于 WHERE + ORDER BY 方向一致的情况
CREATE INDEX idx_orders_status_created ON orders (status, created_at DESC);

-- 覆盖索引（完全避免表查找）
CREATE INDEX idx_users_country_covering ON users (country) 
  INCLUDE (id, name);
-- 现在：SELECT id, name FROM users WHERE country = 'US'
-- 仅使用索引，从不访问表

部分索引

仅索引满足条件的行——更小、更快：

-- 仅索引待处理订单（大多数查询可能关注这些）
CREATE INDEX idx_orders_pending ON orders (created_at)
  WHERE status = 'pending';

-- 此查询将使用部分索引：
SELECT * FROM orders WHERE status = 'pending' AND created_at > NOW() - INTERVAL '7 days';

-- 仅索引已验证用户
CREATE INDEX idx_users_verified_email ON users (email) 
  WHERE verified = true;

-- 仅索引未删除记录（软删除模式）
CREATE INDEX idx_products_active ON products (category_id, price)
  WHERE deleted_at IS NULL;

部分索引通常比完整索引小 80-95%，且更新更快。

GIN 索引：数组和全文搜索

-- 使用 tsvector 进行全文搜索
ALTER TABLE articles ADD COLUMN search_vector tsvector;
CREATE INDEX idx_articles_fts ON articles USING GIN (search_vector);

-- 更新搜索向量
UPDATE articles SET search_vector = 
  to_tsvector('english', title || ' ' || content);

-- 触发器保持更新
CREATE FUNCTION update_search_vector()
RETURNS trigger LANGUAGE plpgsql AS $
BEGIN
  NEW.search_vector := to_tsvector('english', 
    coalesce(NEW.title, '') || ' ' || coalesce(NEW.content, ''));
  RETURN NEW;
END;
$;

CREATE TRIGGER articles_search_vector_update
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION update_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
LIMIT 10;

-- 数组列索引
CREATE INDEX idx_products_tags ON products USING GIN (tags);
-- 查询：WHERE tags @> ARRAY['javascript', 'react']
-- 高效查找同时包含两个标签的产品

索引监控和维护

-- 查找缺失的索引（有顺序扫描的表）
SELECT schemaname, tablename, 
       seq_scan, seq_tup_read,
       idx_scan, idx_tup_fetch,
       seq_scan - idx_scan AS table_scans_vs_index_scans
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 20;

-- 查找未使用的索引（徒增写入开销）
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexname NOT LIKE '%pkey%'  -- 排除主键
ORDER BY schemaname, tablename;

-- 索引大小
SELECT indexname, 
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

-- 查找慢查询（需要 pg_stat_statements 扩展）
SELECT query, 
       calls,
       total_exec_time / calls AS avg_time_ms,
       rows / calls AS avg_rows
FROM pg_stat_statements
WHERE calls > 100
ORDER BY total_exec_time DESC
LIMIT 20;

无停机创建索引

-- 默认：在创建期间锁定表（对大表有问题）
CREATE INDEX idx_users_email ON users (email);

-- CONCURRENTLY：无表锁，生产环境安全
-- 耗时更长，不能在事务中运行
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

-- 检查 CONCURRENTLY 索引构建进度：
SELECT phase, blocks_done, blocks_total, 
       round(100.0 * blocks_done / blocks_total, 1) AS pct_done
FROM pg_stat_progress_create_index;

常见索引错误

-- ❌ 在低基数列上创建索引（不同值很少）
CREATE INDEX idx_users_active ON users (is_active);
-- 如果 95% 的用户是活跃的，这个索引几乎没有帮助
-- PostgreSQL 可能仍然选择顺序扫描

-- ❌ 在函数中使用的列上创建索引——不会被使用！
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';
-- idx_users_email 在这里帮不上忙

-- ✅ 修复：表达式索引
CREATE INDEX idx_users_lower_email ON users (LOWER(email));

-- ❌ 复合索引顺序错误
-- 查询：WHERE status = 'active' AND created_at > '2026-01-01'
CREATE INDEX idx_wrong ON orders (created_at, status);
-- PostgreSQL 无法高效地使用这个索引来查找 status = 'active'

-- ✅ 将等值列放在前面，范围列放在后面
CREATE INDEX idx_correct ON orders (status, created_at);

-- ❌ 过度索引——索引过多会损害写入性能
-- 每个 INSERT/UPDATE/DELETE 都必须维护所有索引
-- 经验法则：OLTP 表需要 < 5-6 个索引

使用 PgBouncer 进行连接池管理

即使有完美的索引，过多的连接也会降低性能：

# pgbouncer.ini
[databases]
myapp = host=localhost port=5432 dbname=myapp

[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1

; 事务池（最高效，但不能使用会话级功能）
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20   ; 实际数据库连接数

; 根据 postgres max_connections 调整
; PostgreSQL 可以很好地处理大约 100-300 个实际连接

// 连接到 PgBouncer 而不是直接连接 PostgreSQL
const pool = new Pool({
  host: 'localhost',
  port: 6432,  // PgBouncer 端口
  database: 'myapp',
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  max: 10,     // 每个 Node 进程的连接池大小
});

→ 使用 SQL 格式化工具 格式化并美化你的 SQL 查询。