PostgreSQL 索引

概述

索引通过加速数据检索来提高查询性能，其工作原理类似于书籍的目录索引。

创建索引

-- 简单索引
CREATE INDEX idx_users_email ON users(email);

-- 唯一索引
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- 复合索引
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

索引类型

B-tree 索引

-- 默认索引类型
CREATE INDEX idx_users_name ON users USING btree (name);

Hash 索引

-- 仅适用于等值比较
CREATE INDEX idx_users_email ON users USING hash (email);

GIN 索引

-- 用于数组、全文搜索、JSONB
CREATE INDEX idx_products_tags ON products USING gin (tags);
CREATE INDEX idx_content ON articles USING gin (to_tsvector('english', content));

GiST 索引

-- 用于几何数据、全文搜索
CREATE INDEX idx_locations ON locations USING gist (coordinates);

部分索引

-- 仅索引活跃用户
CREATE INDEX idx_active_users_email ON users(email) WHERE status = 'active';

表达式索引

-- 基于表达式的索引
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- 使用表达式索引
SELECT * FROM users WHERE LOWER(email) = 'john@example.com';

管理索引

-- 重命名索引
ALTER INDEX idx_users_name RENAME TO idx_users_email_name;

-- 删除索引
DROP INDEX IF EXISTS idx_users_name;

-- 重建索引
REINDEX TABLE users;

总结

索引要点：

• B-tree（默认）、Hash、GIN、GiST
• 部分索引和表达式索引
• 提高查询性能
• 会增加写入操作的开销