PostgreSQL

Appearance

PostgreSQL 索引使用情况检查 ​

索引是 PostgreSQL 数据库性能优化的核心要素。虽然 PostgreSQL 的索引不需要频繁维护，但了解索引在实际查询中的使用情况对于数据库性能调优至关重要。本章将深入探讨如何检查和分析索引使用情况，帮助您制定有效的索引策略。

索引检查的重要性 ​

在生产环境中，我们经常面临以下挑战：

  • 查询性能下降，但不知道是否因为缺少索引
  • 创建了很多索引，但不确定哪些真正被使用
  • 数据量增长后，原有的索引策略是否仍然有效

INFO

索引检查不仅能帮助我们发现性能瓶颈，还能避免创建无用的索引，从而减少存储空间和维护开销。

索引检查方法概览 ​

1. 使用 EXPLAIN 命令分析单个查询 ​

基本 EXPLAIN 使用 ​

EXPLAIN 命令是分析查询执行计划的核心工具，它能显示 PostgreSQL 查询优化器选择的执行路径。

示例场景：电商订单查询分析

假设我们有一个电商系统的订单表：

sql
-- 创建示例表
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER,
    order_date DATE,
    total_amount DECIMAL(10,2),
    status VARCHAR(20)
);

-- 插入测试数据
INSERT INTO orders (customer_id, order_date, total_amount, status)
SELECT
    (random() * 10000)::INTEGER,
    CURRENT_DATE - (random() * 365)::INTEGER,
    (random() * 1000)::DECIMAL(10,2),
    CASE (random() * 4)::INTEGER
        WHEN 0 THEN 'pending'
        WHEN 1 THEN 'completed'
        WHEN 2 THEN 'cancelled'
        ELSE 'shipped'
    END
FROM generate_series(1, 100000);

问题陈述： 查询特定客户的所有订单，但查询性能较慢。

解决方案： 使用 EXPLAIN 分析查询计划

sql
-- 分析查询计划
EXPLAIN SELECT * FROM orders WHERE customer_id = 1001;

输出结果：

                          QUERY PLAN
--------------------------------------------------------------
 Seq Scan on orders  (cost=0.00..2334.00 rows=10 width=33)
   Filter: (customer_id = 1001)
(2 rows)

分析过程：

  1. 执行方式： Seq Scan（顺序扫描）表明 PostgreSQL 正在扫描整个表
  2. 成本估算： cost=0.00..2334.00 显示启动成本为 0，总成本为 2334
  3. 预期行数： rows=10 表示预计返回 10 行
  4. 性能问题： 顺序扫描 100,000 行来找到 10 行，效率低下

优化方案： 在 customer_id 列上创建索引

sql
-- 创建索引
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- 再次分析查询计划
EXPLAIN SELECT * FROM orders WHERE customer_id = 1001;

优化后的输出：

                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders  (cost=0.29..25.41 rows=10 width=33)
   Index Cond: (customer_id = 1001)
(2 rows)

改进效果分析：

  • 执行方式： 从 Seq Scan 变为 Index Scan
  • 成本降低： 从 2334.00 降低到 25.41，性能提升约 93%
  • 精确定位： 使用索引条件直接定位数据

EXPLAIN ANALYZE 实时性能测试 ​

EXPLAIN ANALYZE 不仅显示执行计划，还会实际执行查询并提供真实的性能数据。

sql
-- 实时分析查询性能
EXPLAIN ANALYZE SELECT * FROM orders
WHERE customer_id = 1001 AND order_date >= '2024-01-01';

输出结果：

                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders  (cost=0.29..28.45 rows=3 width=33) (actual time=0.045..0.067 rows=2 loops=1)
   Index Cond: (customer_id = 1001)
   Filter: (order_date >= '2024-01-01'::date)
   Rows Removed by Filter: 8
 Planning Time: 0.123 ms
 Execution Time: 0.089 ms
(6 rows)

关键指标解读：

指标说明值
actual time实际执行时间0.045..0.067 ms
rows实际返回行数2 行
loops执行循环次数1 次
Planning Time计划生成时间0.123 ms
Execution Time总执行时间0.089 ms

TIP

通过比较 estimated rows 和 actual rows，可以判断统计信息的准确性。如果差异很大，说明需要更新统计信息。

2. ANALYZE 命令：统计信息的重要性 ​

为什么需要 ANALYZE ​

PostgreSQL 查询优化器依赖统计信息来制定执行计划。没有准确的统计信息，优化器可能做出错误的决策。

问题场景： 新导入数据后查询性能下降

sql
-- 模拟大量数据导入
INSERT INTO orders (customer_id, order_date, total_amount, status)
SELECT
    (random() * 50000)::INTEGER,  -- 扩大客户ID范围
    CURRENT_DATE - (random() * 30)::INTEGER,  -- 最近30天的订单
    (random() * 5000)::DECIMAL(10,2),
    'completed'
FROM generate_series(1, 500000);  -- 新增50万条记录

解决方案： 运行 ANALYZE 更新统计信息

sql
-- 更新表的统计信息
ANALYZE orders;

-- 查看统计信息更新前后的差异
SELECT
    schemaname,
    tablename,
    n_tup_ins,
    n_tup_upd,
    n_tup_del,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE tablename = 'orders';

输出示例：

 schemaname | tablename | n_tup_ins | n_tup_upd | n_tup_del |         last_analyze          |      last_autoanalyze
------------+-----------+-----------+-----------+-----------+-------------------------------+----------------------------
 public     | orders    |    600000 |         0 |         0 | 2024-06-03 10:30:15.123456+08 | 2024-06-03 09:15:22.987654+08

自动与手动 ANALYZE 的选择 ​

手动 ANALYZE 的适用场景：

sql
-- 大批量导入后立即更新统计信息
COPY orders FROM '/path/to/data.csv' WITH CSV HEADER;
ANALYZE orders;
sql
-- 只分析特定列，提高效率
ANALYZE orders (customer_id, order_date);
sql
-- 更新整个数据库的统计信息
ANALYZE;

3. 真实数据实验的重要性 ​

测试数据 vs 生产数据 ​

使用不合适的测试数据进行索引测试可能导致错误的结论。

错误示例：小数据集测试

sql
-- 创建小测试表（仅100行）
CREATE TABLE orders_test_small AS
SELECT * FROM orders LIMIT 100;

-- 在小表上查询
EXPLAIN ANALYZE SELECT * FROM orders_test_small WHERE customer_id = 1001;

输出：

                                               QUERY PLAN
--------------------------------------------------------------------------------------------------------
 Seq Scan on orders_test_small  (cost=0.00..3.25 rows=1 width=33) (actual time=0.015..0.018 rows=0 loops=1)
   Filter: (customer_id = 1001)
 Planning Time: 0.067 ms
 Execution Time: 0.021 ms

正确示例：合理规模的测试数据

sql
-- 创建合理规模的测试表
CREATE TABLE orders_test_large AS
SELECT * FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL '90 days';

-- 创建索引并测试
CREATE INDEX idx_test_customer_id ON orders_test_large(customer_id);
ANALYZE orders_test_large;

EXPLAIN ANALYZE SELECT * FROM orders_test_large WHERE customer_id = 1001;

数据分布对索引效果的影响 ​

场景分析：不同数据分布下的索引表现

sql
-- 创建均匀分布的测试数据
CREATE TABLE orders_uniform AS
SELECT
    row_number() OVER () as order_id,
    (row_number() OVER () % 1000) + 1 as customer_id,  -- 1-1000均匀分布
    CURRENT_DATE - (random() * 365)::INTEGER as order_date,
    (random() * 1000)::DECIMAL(10,2) as total_amount
FROM generate_series(1, 100000);

CREATE INDEX idx_uniform_customer ON orders_uniform(customer_id);
ANALYZE orders_uniform;
sql
-- 创建偏斜分布的测试数据（模拟真实业务场景）
CREATE TABLE orders_skewed AS
SELECT
    row_number() OVER () as order_id,
    CASE
        WHEN random() < 0.8 THEN (random() * 100)::INTEGER + 1  -- 80%的订单来自前100个客户
        ELSE (random() * 9900)::INTEGER + 101  -- 20%的订单来自其他客户
    END as customer_id,
    CURRENT_DATE - (random() * 365)::INTEGER as order_date,
    (random() * 1000)::DECIMAL(10,2) as total_amount
FROM generate_series(1, 100000);

CREATE INDEX idx_skewed_customer ON orders_skewed(customer_id);
ANALYZE orders_skewed;

对比分析：

sql
-- 查询热门客户（数据偏斜场景中的常见查询）
EXPLAIN ANALYZE SELECT COUNT(*) FROM orders_uniform WHERE customer_id <= 10;
EXPLAIN ANALYZE SELECT COUNT(*) FROM orders_skewed WHERE customer_id <= 10;

在偏斜分布的数据中，查询热门客户时，PostgreSQL 可能选择顺序扫描而不是索引扫描，因为需要访问大量数据。

4. 强制索引使用测试 ​

运行时参数控制 ​

有时需要强制使用或禁用某些执行计划类型来测试索引效果。

测试场景：评估索引的真实价值

sql
-- 正常情况下的查询计划
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 1001;

-- 禁用顺序扫描，强制使用索引
SET enable_seqscan = OFF;
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 1001;

-- 禁用索引扫描，强制使用顺序扫描
SET enable_indexscan = OFF;
SET enable_seqscan = ON;
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 1001;

-- 恢复默认设置
RESET enable_seqscan;
RESET enable_indexscan;

参数控制对比测试 ​

参数作用适用场景
enable_seqscan控制顺序扫描测试索引必要性
enable_indexscan控制索引扫描评估顺序扫描效率
enable_nestloop控制嵌套循环连接测试连接策略
enable_hashjoin控制哈希连接连接算法比较

实验流程：

5. 成本估算调优 ​

理解成本模型 ​

PostgreSQL 使用基于成本的优化器，了解成本计算有助于调优索引使用。

成本计算公式：

  • 顺序扫描成本 = (页面数 × seq_page_cost) + (行数 × cpu_tuple_cost)
  • 索引扫描成本 = (索引页面数 × random_page_cost) + (行数 × cpu_index_tuple_cost)

示例：调整成本参数

sql
-- 查看当前成本参数
SHOW seq_page_cost;
SHOW random_page_cost;
SHOW cpu_tuple_cost;
SHOW cpu_index_tuple_cost;

-- 对于SSD存储，可以降低随机访问成本
SET random_page_cost = 1.1;  -- 默认值通常是4.0

-- 重新分析查询计划
EXPLAIN SELECT * FROM orders WHERE customer_id = 1001;

成本参数调优指南 ​

Details

不同存储类型的建议参数

传统机械硬盘 (HDD):

sql
SET seq_page_cost = 1.0;
SET random_page_cost = 4.0;
SET cpu_tuple_cost = 0.01;
SET cpu_index_tuple_cost = 0.005;

固态硬盘 (SSD):

sql
SET seq_page_cost = 1.0;
SET random_page_cost = 1.1;  -- 显著降低随机访问成本
SET cpu_tuple_cost = 0.01;
SET cpu_index_tuple_cost = 0.005;

高性能 NVMe SSD:

sql
SET seq_page_cost = 1.0;
SET random_page_cost = 1.0;  -- 接近顺序访问成本
SET cpu_tuple_cost = 0.01;
SET cpu_index_tuple_cost = 0.005;

6. 统计信息调优 ​

统计信息收集参数 ​

准确的统计信息是正确执行计划的基础。

sql
-- 查看表的统计信息详情
SELECT
    tablename,
    attname,
    n_distinct,
    most_common_vals,
    most_common_freqs,
    histogram_bounds
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'customer_id';

调整统计信息收集精度：

sql
-- 增加统计信息样本大小
ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 1000;  -- 默认100

-- 重新收集统计信息
ANALYZE orders;

多维统计信息 ​

对于相关列，可以创建扩展统计信息：

sql
-- 创建多列统计信息
CREATE STATISTICS orders_customer_date_stats
ON customer_id, order_date
FROM orders;

-- 更新统计信息
ANALYZE orders;

-- 查看扩展统计信息
SELECT
    schemaname,
    tablename,
    attnames,
    n_distinct,
    dependencies,
    most_common_vals
FROM pg_stats_ext
WHERE tablename = 'orders';

7. 监控索引使用情况 ​

系统级索引统计 ​

通过系统视图监控索引的实际使用情况：

sql
-- 查看索引使用统计
SELECT
    schemaname,
    tablename,
    indexname,
    idx_tup_read,      -- 索引读取的元组数
    idx_tup_fetch,     -- 通过索引获取的表行数
    idx_scan,          -- 索引扫描次数
    idx_blks_read,     -- 索引块读取数
    idx_blks_hit       -- 索引块缓存命中数
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan DESC;

输出示例：

 schemaname | tablename |      indexname       | idx_tup_read | idx_tup_fetch | idx_scan | idx_blks_read | idx_blks_hit
------------+-----------+----------------------+--------------+---------------+----------+---------------+--------------
 public     | orders    | idx_orders_customer_id |        15420 |         15420 |     1542 |           458 |         7832
 public     | orders    | orders_pkey          |          892 |           892 |       89 |            23 |          156

无用索引识别 ​

识别很少使用或从未使用的索引：

sql
-- 查找未使用的索引
SELECT
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelname::regclass)) as index_size,
    idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelname::regclass) DESC;

清理建议：

WARNING

删除索引前，请确认：

  1. 索引确实未被使用（监控时间足够长）
  2. 不是用于唯一性约束的索引
  3. 不是外键约束所需的索引

8. 实战案例：电商系统索引优化 ​

业务场景 ​

某电商平台的订单查询系统存在性能问题，主要查询模式包括：

  • 按客户查询订单历史
  • 按时间范围查询订单
  • 按状态筛选订单
  • 复合条件查询

问题诊断 ​

步骤 1：收集慢查询

sql
-- 开启慢查询日志
SET log_min_duration_statement = 1000;  -- 记录超过1秒的查询

-- 模拟典型业务查询
SELECT COUNT(*) FROM orders
WHERE customer_id = 1001
  AND order_date >= '2024-01-01'
  AND status = 'completed';

步骤 2：分析执行计划

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE customer_id = 1001
  AND order_date >= '2024-01-01'
  AND status = 'completed';

原始执行计划（无合适索引）：

                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Seq Scan on orders  (cost=0.00..15834.00 rows=3 width=33) (actual time=23.456..156.789 rows=15 loops=1)
   Filter: ((customer_id = 1001) AND (order_date >= '2024-01-01'::date) AND ((status)::text = 'completed'::text))
   Rows Removed by Filter: 599985
   Buffers: shared hit=8334
 Planning Time: 0.123 ms
 Execution Time: 156.834 ms
(6 rows)

索引优化方案 ​

方案 1：单列索引组合

sql
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_date ON orders(order_date);
CREATE INDEX idx_orders_status ON orders(status);

ANALYZE orders;

方案 2：复合索引（推荐）

sql
-- 删除单列索引
DROP INDEX IF EXISTS idx_orders_customer;
DROP INDEX IF EXISTS idx_orders_date;
DROP INDEX IF EXISTS idx_orders_status;

-- 创建复合索引，注意列的顺序
CREATE INDEX idx_orders_composite ON orders(customer_id, order_date, status);

ANALYZE orders;

优化后的执行计划：

                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_orders_composite on orders  (cost=0.42..23.67 rows=15 width=33) (actual time=0.034..0.089 rows=15 loops=1)
   Index Cond: ((customer_id = 1001) AND (order_date >= '2024-01-01'::date))
   Filter: ((status)::text = 'completed'::text)
   Rows Removed by Filter: 8
   Buffers: shared hit=5
 Planning Time: 0.156 ms
 Execution Time: 0.112 ms
(7 rows)

性能提升分析：

指标优化前优化后提升率
执行时间156.834 ms0.112 ms99.93%
扫描行数600,0002399.996%
缓存块访问8,334599.94%

验证和监控 ​

持续监控索引效果：

sql
-- 创建监控视图
CREATE OR REPLACE VIEW index_usage_summary AS
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch,
    pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
    ROUND(idx_tup_fetch::NUMERIC / NULLIF(idx_tup_read, 0) * 100, 2) as hit_ratio
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan DESC;

-- 定期检查索引使用情况
SELECT * FROM index_usage_summary;

最佳实践总结 ​

索引检查流程 ​

关键原则 ​

TIP

索引检查的关键原则

  1. 始终先运行 ANALYZE

    • 确保统计信息是最新的
    • 新数据导入后必须更新统计信息

  2. 使用真实数据进行测试

    • 避免使用过小的测试数据集
    • 确保数据分布接近生产环境

  3. 理解成本模型

    • 根据存储类型调整成本参数
    • 定期验证成本估算的准确性

  4. 持续监控索引使用

    • 定期检查索引使用统计
    • 及时清理无用索引

  5. 实验驱动的优化

    • 使用强制参数测试不同方案
    • 量化性能提升效果

通过系统性的索引检查和优化，我们能够确保 PostgreSQL 数据库在各种查询模式下都能提供最佳性能。记住，索引优化是一个持续的过程，需要根据业务发展和数据变化不断调整。