PostgreSQL 部分索引与表达式索引优化

部分索引（Partial Indexes）

概念与原理

部分索引是只包含表中满足特定条件的行的索引，通过 WHERE 子句来定义索引的适用范围。

核心优势

• 减少索引大小，降低存储开销
• 提高查询性能，减少索引扫描范围
• 降低写入操作的索引维护成本
• 支持更精确的查询优化

创建语法

CREATE INDEX index_name ON table_name (columns)
WHERE condition;

适用场景

1. 频繁查询特定状态的数据

示例：订单表中频繁查询未完成订单

-- 表结构
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    status VARCHAR(20) NOT NULL,
    order_date TIMESTAMP NOT NULL,
    total_amount NUMERIC(10,2) NOT NULL
);

-- 创建部分索引
CREATE INDEX idx_orders_pending ON orders (order_date DESC) 
WHERE status = 'pending';

-- 查询使用索引
EXPLAIN ANALYZE 
SELECT * FROM orders 
WHERE status = 'pending' 
ORDER BY order_date DESC 
LIMIT 10;

2. 优化 NULL 值处理

示例：用户表中查询未验证邮箱的用户

CREATE INDEX idx_users_unverified_email ON users (created_at)
WHERE email_verified = false;

3. 分区表替代方案

对于数据分布极不均匀的表，部分索引可以作为轻量级分区表替代方案。

版本差异

• PostgreSQL 9.0+：完全支持部分索引
• PostgreSQL 10+：支持在表达式索引上创建部分索引
• PostgreSQL 12+：支持在分区表上创建部分索引

最佳实践

1. 选择高选择性条件：条件过滤后的数据量应显著小于表总量（建议<20%）
2. 考虑写入频率：部分索引只在满足条件的行上维护，适合写入不频繁的场景
3. 避免复杂条件：索引条件应简单且易于评估
4. 结合统计信息：定期更新表统计信息，确保查询优化器正确选择索引
5. 测试验证：使用 EXPLAIN ANALYZE 验证索引效果

常见问题

Q1: 部分索引的条件是否会影响查询计划？

A: 是的，查询优化器会检查查询条件是否与部分索引的 WHERE 子句匹配，只有匹配时才会考虑使用该索引。

Q2: 部分索引是否支持并发创建？

A: 是的，使用 CREATE INDEX CONCURRENTLY 可以并发创建部分索引，减少锁等待。

Q3: 部分索引能否用于 ORDER BY 操作？

A: 是的，只要 ORDER BY 列包含在索引中，且查询条件匹配部分索引的 WHERE 子句。

表达式索引（Expression Indexes）

概念与原理

表达式索引是基于表中列的计算结果创建的索引，允许在索引中存储表达式的预计算值。

核心优势

• 加速基于函数或表达式的查询
• 减少查询时的计算开销
• 支持复杂数据类型的高效查询
• 提高查询性能的同时保持数据灵活性

创建语法

CREATE INDEX index_name ON table_name (expression);

适用场景

1. 字符串处理查询

示例：不区分大小写的用户名查询

-- 表结构
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

-- 创建表达式索引
CREATE INDEX idx_users_username_lower ON users (LOWER(username));

-- 查询使用索引
EXPLAIN ANALYZE 
SELECT * FROM users 
WHERE LOWER(username) = 'john';

2. 日期时间处理

示例：按年份查询销售数据

CREATE INDEX idx_sales_year ON sales (EXTRACT(YEAR FROM sale_date));

-- 查询使用索引
SELECT EXTRACT(YEAR FROM sale_date) AS year, SUM(amount) 
FROM sales 
GROUP BY EXTRACT(YEAR FROM sale_date);

3. JSONB 数据查询

示例：JSONB 字段中特定键的查询

-- 表结构
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    data JSONB NOT NULL
);

-- 创建表达式索引
CREATE INDEX idx_products_category ON products ((data->>'category'));

-- 查询使用索引
EXPLAIN ANALYZE 
SELECT * FROM products 
WHERE data->>'category' = 'electronics';

版本差异

• PostgreSQL 8.0+：支持基本表达式索引
• PostgreSQL 9.3+：支持 JSON/JSONB 表达式索引
• PostgreSQL 12+：支持覆盖索引中的表达式

最佳实践

1. 表达式一致性：查询中使用的表达式必须与索引中完全一致
2. 函数确定性：索引表达式必须是确定性函数（相同输入产生相同输出）
3. 考虑计算开销：表达式计算不应过于复杂，避免影响索引维护性能
4. 结合部分索引：对于特定条件下的表达式查询，可结合部分索引使用
5. 监控索引大小：表达式索引可能比普通索引大，需监控存储使用

常见问题

Q1: 表达式索引支持哪些函数？

A: 支持所有确定性函数，包括内置函数和用户自定义函数（需标记为 IMMUTABLE）。

Q2: 表达式索引会影响写入性能吗？

A: 是的，每次写入时都需要计算表达式值并更新索引，应谨慎使用。

Q3: 如何查看表达式索引的使用情况？

A: 使用 pg_stat_user_indexes 视图查看索引扫描次数，或使用 EXPLAIN ANALYZE 分析查询计划。

部分索引与表达式索引的结合使用

适用场景

当需要同时满足特定条件和表达式查询时，可以结合使用部分索引和表达式索引。

示例：电商订单查询优化

需求：频繁查询最近 30 天的未完成订单，按创建时间倒序

-- 表结构
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    total_amount NUMERIC(10,2) NOT NULL
);

-- 创建复合索引
CREATE INDEX idx_orders_recent_pending ON orders (
    created_at DESC,
    total_amount DESC
) WHERE status = 'pending' AND created_at > NOW() - INTERVAL '30 days';

-- 查询使用索引
EXPLAIN ANALYZE 
SELECT * FROM orders 
WHERE status = 'pending' 
AND created_at > NOW() - INTERVAL '30 days' 
ORDER BY created_at DESC, total_amount DESC 
LIMIT 20;

性能对比

索引类型	存储大小	写入开销	查询性能	适用场景
普通索引	大	高	中	通用查询
部分索引	小	低	高	特定条件查询
表达式索引	中	中	高	函数/表达式查询
复合索引	中	中	极高	特定条件+表达式查询

生产环境案例分析

案例：社交媒体平台消息表优化

背景：消息表包含 1 亿条记录，频繁查询用户未读消息

优化前：

SELECT * FROM messages 
WHERE user_id = 123 AND is_read = false 
ORDER BY created_at DESC;

优化方案：创建部分索引

CREATE INDEX idx_messages_unread ON messages (user_id, created_at DESC)
WHERE is_read = false;

优化效果：

• 查询时间从 500ms 降低到 10ms
• 索引大小仅为普通索引的 15%
• 写入开销降低 85%

案例：电商平台搜索优化

背景：产品表包含 500 万条记录，支持按名称模糊搜索

优化前：

SELECT * FROM products 
WHERE LOWER(name) LIKE '%iphone%' 
ORDER BY price DESC;

优化方案：创建表达式索引结合 GIN 索引

-- 安装 pg_trgm 扩展
CREATE EXTENSION pg_trgm;

-- 创建 GIN 表达式索引
CREATE INDEX idx_products_name_trgm ON products 
USING GIN (LOWER(name) gin_trgm_ops);

优化效果：

• 模糊搜索性能提升 100 倍
• 支持更灵活的搜索模式
• 索引维护开销可控

监控与维护

索引使用监控

-- 查看索引使用情况
SELECT 
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes 
WHERE relname IN ('orders', 'users', 'products') 
ORDER BY idx_scan DESC;

索引膨胀检查

-- 安装 pgstattuple 扩展
CREATE EXTENSION pgstattuple;

-- 检查索引膨胀
SELECT 
    pg_size_pretty(pg_relation_size('idx_orders_pending')) AS index_size,
    (stats.index_scan / stats.idx_scan)::numeric(5,2) AS bloat_ratio
FROM pgstatindex('idx_orders_pending') stats;

定期维护建议

1. 监控索引使用率：移除长期未使用的索引
2. 更新统计信息：使用 ANALYZE 或 VACUUM ANALYZE 更新表统计信息
3. 重建膨胀索引：使用 REINDEX 或 CREATE INDEX CONCURRENTLY 重建膨胀严重的索引
4. 审查索引设计：定期审查索引设计，根据业务变化调整

总结

部分索引和表达式索引是 PostgreSQL 中强大的性能优化工具，能够在特定场景下显著提升查询性能并降低存储开销。在实际生产环境中，应根据业务需求和数据分布特点，合理选择索引类型，并结合监控和维护策略，确保索引始终保持高效状态。

关键要点

1. 部分索引：适用于特定条件下的查询，减少索引大小和维护成本
2. 表达式索引：加速基于函数或表达式的查询，提高查询灵活性
3. 结合使用：对于复杂查询场景，可结合使用部分索引和表达式索引
4. 版本兼容性：注意不同 PostgreSQL 版本的功能差异
5. 监控维护：定期监控索引使用情况，及时调整和维护

通过合理设计和使用部分索引与表达式索引，可以在不增加过多存储和维护成本的情况下，显著提升 PostgreSQL 数据库的查询性能，为生产环境提供更高效的数据访问支持。