PostgreSQL索引的设计原则和最佳实践

create index idx_orders_user_id on orders(user_id);

create index idx_users_email_hash on users using hash(email);

-- jsonb 索引
create index idx_products_attrs_gin on products using gin(attributes);
-- 全文检索
create index idx_articles_fts on articles using gin(to_tsvector('english', content));
-- 模糊搜索（需 pg_trgm 扩展）
create extension if not exists pg_trgm;
create index idx_users_name_trgm on users using gin(name gin_trgm_ops);

-- 全文检索（gist 版本）
create index idx_articles_fts_gist on articles using gist(to_tsvector('english', content));
-- ltree 路径索引
create index idx_categories_path on categories using gist(path);

-- 时间序列表
create index idx_logs_created_brin on logs using brin(created_at);

-- 启用 pg_stat_statements
create extension pg_stat_statements;
-- 查看最耗时的查询
select query, calls, total_exec_time, rows
from pg_stat_statements
order by total_exec_time desc
limit 10;

-- 查询：select * from orders where user_id = 123 and status = 'paid';
-- 推荐索引：
create index idx_orders_user_status on orders(user_id, status);

-- 查询：select order_id, total from orders where user_id = 123;
-- 普通索引：
create index idx_orders_user_id on orders(user_id);  -- 需回表取 total
-- 覆盖索引：
create index idx_orders_user_id_covering on orders(user_id) include (total);
-- 执行计划：index only scan（无需回表）

查询条件中有哪些列？
├─ 全是等值（=） → 任意顺序（建议高选择性列在前）
├─ 含范围（>, <, between） → 等值列在前，范围列在后
└─ 含排序（order by） → 将排序列放在最后（若前面是等值）

-- 查询：where user_id = 123 and created_at > '2026-01-01'
-- 正确顺序：(user_id, created_at)
create index idx_orders_user_created on orders(user_id, created_at);
-- 错误顺序：(created_at, user_id) → created_at 范围扫描后仍需过滤 user_id

-- 查询：where status = 'paid' order by created_at desc limit 10
-- 推荐索引：(status, created_at desc)
create index idx_orders_status_created on orders(status, created_at desc);
-- 可实现 index scan + limit，避免 sort

-- 场景：90% 的订单是 'completed'，但常查 'pending'
create index idx_orders_pending on orders(user_id)
where status = 'pending';
-- 查询必须包含相同条件才能使用索引
select * from orders where user_id = 123 and status = 'pending';

-- 查看从未使用的索引
select schemaname, tablename, indexname, idx_scan
from pg_stat_user_indexes
where idx_scan = 0
order by schemaname, tablename;
-- 查看低效索引（扫描次数远低于表大小）
select
    schemaname,
    tablename,
    indexname,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexname::regclass)) as index_size
from pg_stat_user_indexes
where idx_scan < 100  -- 阈值根据业务调整
order by pg_relation_size(indexname::regclass) desc;

-- 手动更新统计信息（大批量导入后执行）
analyze table_name;
-- 调整自动分析阈值
alter table orders set (
    autovacuum_analyze_scale_factor = 0.05,  -- 默认 0.1
    autovacuum_analyze_threshold = 500       -- 默认 50
);

-- ssd 随机读接近顺序读，降低 random_page_cost
set random_page_cost = 1.1;  -- 默认 4.0（机械盘）
-- 若内存充足，可降低 cpu_tuple_cost
set cpu_tuple_cost = 0.005;  -- 默认 0.01

-- 性别只有 'm'/'f'，索引几乎无效
create index idx_users_gender on users(gender);

-- 索引
create index idx_users_upper_email on users(upper(email));
-- 查询必须完全一致
select * from users where upper(email) = 'alice@example.com';  -- ✅
select * from users where upper(email) = lower('alice@example.com');  -- ❌ 不匹配

-- 原查询（可能无法使用索引）
select * from users where email = 'a' or name = 'alice';
-- 优化后（每个分支独立使用索引）
select * from users where email = 'a'
union
select * from users where name = 'alice';

-- 原查询
select * from logs where date(created_at) = '2026-01-25';
-- 优化后（使用范围）
select * from logs 
where created_at >= '2026-01-25' 
  and created_at < '2026-01-26';

-- 原查询（需回表）
select user_id, count(*) from orders group by user_id;
-- 若 orders 表很大，可建覆盖索引
create index idx_orders_user_covering on orders(user_id) include (order_id);
-- 执行计划：index only scan + groupaggregate