PostgreSQL 索引类型深度实战：B-tree 原理、组合索引、GiST/GIN/BRIN 与索引维护

-- 观察索引被使用的情况
EXPLAIN ANALYZE
SELECT * FROM orders WHERE order_id = 12345;
-- 输出中若出现 Index Scan using orders_pkey，说明命中主键索引
-- 若出现 Bitmap Heap Scan，说明使用了 bitmap 扫描策略

CREATE INDEX idx_orders_user_time
  ON orders (user_id, created_at DESC);

-- ✅ 走完整索引：user_id 等值 + created_at 范围
EXPLAIN SELECT * FROM orders
WHERE user_id = 1001 AND created_at > '2026-01-01';

-- ✅ 走索引，但只用于 user_id 过滤，created_at 部分退化
EXPLAIN SELECT * FROM orders
WHERE user_id > 1001 AND created_at > '2026-01-01';

-- ❌ 完全无法使用 idx_orders_user_time
EXPLAIN SELECT * FROM orders WHERE created_at > '2026-01-01';

-- PostgreSQL 对 DISTINCT 的 loose scan（跳过重复键值）
EXPLAIN SELECT DISTINCT user_id FROM orders WHERE user_id < 1000;
-- 输出：Index Only Scan using idx_orders_user_time ... (skip duplicate)

-- 1. 范围类型：会议室预订时间不冲突
CREATE TABLE meeting_rooms (
  room_id INT,
  reserved_range TSRANGE NOT NULL,
  EXCLUDE USING GIST (room_id WITH =, reserved_range WITH &&)
);

-- 2. 空间索引：查找附近门店
CREATE INDEX idx_stores_loc ON stores USING GIST (location);

-- 3. 范围重叠查询
SELECT * FROM meeting_rooms
WHERE reserved_range && tsrange('2026-09-27 10:00', '2026-09-27 12:00');

-- 1. 全文搜索（tsvector）
CREATE INDEX idx_articles_tsv ON articles USING GIN (tsv);
SELECT * FROM articles WHERE tsv @@ to_tsquery('postgresql & performance');

-- 2. JSONB 包含查询（默认 jsonb_ops 操作符类）
CREATE INDEX idx_users_tags ON users USING GIN (tags);
SELECT * FROM users WHERE tags @> '["vip"]';

-- 3. 数组包含查询
CREATE INDEX idx_orders_skus ON orders USING GIN (sku_array);
SELECT * FROM orders WHERE sku_array @> ARRAY['SKU-1001'];

-- 显式指定操作符类
CREATE INDEX idx_jsonb ON docs USING GIN (data jsonb_ops);
-- 更小更快的 jsonb_path_ops（仅支持 @>，不支持 ? 系列）
CREATE INDEX idx_jsonb_path ON docs USING GIN (data jsonb_path_ops);

-- 全文搜索的 fastupdate（延迟合并 posting list，降低写放大）
CREATE INDEX idx_tsv ON articles USING GIN (tsv)
  WITH (fastupdate = on, gin_pending_list_limit = 4096);

-- 时序日志表：created_at 随物理写入顺序单调递增
CREATE TABLE access_logs (
  id BIGSERIAL,
  created_at TIMESTAMPTZ NOT NULL,
  ip INET,
  url TEXT
);

-- 在时间列上建 BRIN
CREATE INDEX idx_logs_time ON access_logs USING BRIN (created_at);

-- ✅ 高效：WHERE 条件命中时间范围，BRIN 快速跳过大部分块
EXPLAIN SELECT count(*) FROM access_logs
WHERE created_at > NOW() - INTERVAL '1 hour';

-- ❌ 低效：查询覆盖全表，BRIN 每个块范围都要访问
EXPLAIN SELECT count(*) FROM access_logs WHERE ip = '192.168.1.1';

-- 调整块范围大小（每页 8KB，pages_per_range=128 即约 1MB）
CREATE INDEX idx_logs_time ON access_logs USING BRIN (created_at)
  WITH (pages_per_range = 64);

-- 只索引活跃订单（status = 'active'）
CREATE INDEX idx_orders_active ON orders (created_at)
  WHERE status = 'active';

-- 查询必须带上相同谓词才能命中
EXPLAIN SELECT * FROM orders
WHERE status = 'active' AND created_at > '2026-09-01';
-- ✅ Bitmap Index Scan on idx_orders_active

-- 邮箱登录不区分大小写
CREATE INDEX idx_users_lower_email ON users (lower(email));

-- 命中表达式索引
EXPLAIN SELECT * FROM users WHERE lower(email) = 'admin@example.com';
-- ✅ Index Scan using idx_users_lower_email

-- 普通索引：查询 SELECT id, user_id, created_at 需回表
CREATE INDEX idx_orders_uid ON orders (user_id);

-- 覆盖索引：把 created_at 也塞进索引叶子
CREATE INDEX idx_orders_uid_cover ON orders (user_id) INCLUDE (created_at);

EXPLAIN SELECT user_id, created_at FROM orders WHERE user_id = 1001;
-- 输出：Index Only Scan using idx_orders_uid_cover
-- 注意：Heap Fetches 应为 0 或接近 0

-- 方式 1：pgstatindex 扩展（统计每个索引的页与空余率）
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT * FROM pgstatindex('idx_orders_uid_cover');

-- 普通 REINDEX 会持有写锁，阻塞业务
-- REINDEX CONCURRENTLY 不阻塞读写（PG 12+，失败会残留无效索引）
REINDEX INDEX CONCURRENTLY idx_orders_uid_cover;

-- 重建表上全部索引
REINDEX TABLE CONCURRENTLY orders;

-- 控制并行 worker 数（依据表大小，PostgreSQL 自动并行）
SET maintenance_work_mem = '2GB';
SET max_parallel_maintenance_workers = 4;

CREATE INDEX CONCURRENTLY idx_orders_time ON orders (created_at DESC);

-- 找出从未被扫描过的"僵尸索引"
SELECT
  schemaname, relname, indexrelname,
  idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND NOT indisunique
ORDER BY pg_relation_size(indexrelid) DESC;

-- 每个表的索引数量与总大小
SELECT
  t.relname AS table_name,
  count(i.indexrelid) AS index_count,
  pg_size_pretty(sum(pg_relation_size(i.indexrelid))) AS total_idx_size
FROM pg_class t
JOIN pg_index i ON i.indrelid = t.oid
WHERE t.relkind = 'r'
GROUP BY t.relname
ORDER BY sum(pg_relation_size(i.indexrelid)) DESC
LIMIT 20;