Ubuntu PostgreSQL索引优化建议

1. 选择合适的索引类型
PostgreSQL 提供多种索引类型，需根据查询场景选择：

B-Tree（默认）：适用于等值查询（=、IN）、范围查询（<、BETWEEN）、排序（ORDER BY）和前缀匹配（LIKE ‘abc%’），是通用性最强的索引，支持多列组合。创建语法：CREATE INDEX idx_users_name ON users (last_name);。
Hash：仅支持等值查询（=），性能略优于 B-Tree，但不支持范围查询或排序，适合高频等值热点列（如身份证号、唯一标识符）。创建语法：CREATE INDEX idx_orders_order_no_hash ON orders USING hash (order_no);。
GIN（广义倒排索引）：专为多值字段设计（如数组、JSONB、全文搜索），支持“包含”查询（@>），适合写少读多的场景（如全文搜索、数组包含查询）。创建语法：CREATE INDEX idx_gin ON articles USING GIN (tags);。
BRIN（块范围索引）：适用于超大表（TB 级别）的范围查询（如时间序列数据），索引体积小（比 B-Tree 小几十倍），依赖数据物理顺序（需定期 VACUUM 保持准确性）。创建语法：CREATE INDEX idx_brin ON logs USING BRIN (timestamp_column);。

2. 优化多列索引设计

列顺序：多列索引中，将区分度高、过滤性强的列放在前面（如 status = 'completed' 的区分度高于 create_time），能显著提高索引利用率。
组合索引：为经常一起出现在 WHERE 子句中的列创建组合索引（如 (user_id, order_date)），避免单独为每个列建索引（减少索引维护开销）。

3. 利用高级索引特性减少冗余

部分索引：仅对满足特定条件的行创建索引（如 WHERE status = 'completed'），减少索引大小，提高查询性能。示例：CREATE INDEX idx_completed_orders ON orders (order_date) WHERE status = 'completed';。
覆盖索引：通过 INCLUDE 子句将查询所需的其他列包含在索引中，实现“仅索引扫描”（无需回表访问表数据）。示例：CREATE INDEX idx_covering ON users (username) INCLUDE (email, phone);。
表达式索引：对计算后的表达式建立索引（如 LOWER(name)），支持基于表达式的查询优化。示例：CREATE INDEX idx_expr ON users ((LOWER(name)));。

4. 定期维护索引以保持性能

在线建索引：对大表使用 CONCURRENTLY 选项建索引，避免锁表影响业务（如 CREATE INDEX CONCURRENTLY idx_users_email ON users (email);）。
重建索引：随着数据增删改，索引可能膨胀（碎片化），使用 REINDEX 命令重建索引（如 REINDEX INDEX idx_users_name;），或在 VACUUM 阶段自动维护。
监控索引使用：通过 pg_stat_user_indexes 视图监控索引使用情况（如 idx_scan 列表示索引扫描次数），识别未被使用的索引（idx_scan = 0）并删除，减少维护开销。

5. 避免索引滥用以平衡性能

最新问答