索引类型：PostgreSQL 为什么有六种索引

CREATE TABLE episodes (
  id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title        TEXT NOT NULL,
  synopsis     TEXT,                          -- 简介，全文搜索用
  play_count   BIGINT NOT NULL DEFAULT 0,
  published_at TIMESTAMPTZ NOT NULL,
  tags         TEXT[] NOT NULL DEFAULT '{}'
);

CREATE TABLE listeners (
  id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  nickname TEXT NOT NULL,
  last_ip  INET
);

CREATE TABLE listen_events (                  -- 收听流水，十亿行级别
  listener_id BIGINT NOT NULL,
  episode_id  BIGINT NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_ep_published ON episodes (published_at);
CREATE INDEX idx_ep_plays     ON episodes (play_count);

-- 等值
SELECT * FROM episodes WHERE id = 42;
-- 范围（索引范围扫描）
SELECT * FROM episodes
WHERE published_at >= '2026-09-01' AND published_at < '2026-10-01';
-- 排序（直接按索引顺序读，免排序）
SELECT title FROM episodes ORDER BY play_count DESC LIMIT 10;
-- 前缀 LIKE（等值于一个范围扫描）
SELECT * FROM listeners WHERE nickname LIKE '夜航%';

CREATE INDEX idx_ep_title_hash ON episodes USING hash (title);
SELECT * FROM episodes WHERE title = '第 41 期：一个程序员的退休计划';

-- 1. 空间数据（PostGIS）：找附近的听友会
CREATE INDEX idx_venue_geom ON venues USING gist (geom);
SELECT name FROM venues
WHERE ST_DWithin(geom, ST_MakePoint(116.4, 39.9)::geography, 3000);

-- 2. 范围类型：会场档期是否冲突（GiST 还能当约束用）
--    排除约束里 text 的等值判断需要 btree_gist 扩展
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE bookings (room text, during tstzrange);
CREATE INDEX idx_bookings_during ON bookings USING gist (room, during);
ALTER TABLE bookings
  ADD CONSTRAINT no_overlap EXCLUDE USING gist
    (room WITH =, during WITH &&);   -- 同一间房档期不得相交，数据库层硬保证

-- 3. KNN 近邻：<-> 距离排序走 GiST
SELECT name FROM venues
ORDER BY geom <-> ST_MakePoint(116.4, 39.9) LIMIT 5;

-- 1. 全文检索（推荐配置，表达式必须与查询侧一致）
CREATE INDEX idx_ep_fts ON episodes
  USING gin (to_tsvector('simple', synopsis));
SELECT title FROM episodes
WHERE to_tsvector('simple', synopsis) @@ to_tsquery('simple', '咖啡 & 定价');

-- 2. 数组：打了一个标签就算命中
CREATE INDEX idx_ep_tags ON episodes USING gin (tags);
SELECT title FROM episodes WHERE tags @> ARRAY['AI', '创业'];

-- 3. JSONB：@> 与 ? 系列查询
CREATE INDEX idx_events_payload ON device_events USING gin (payload);

CREATE INDEX idx_ep_tags_fast ON episodes USING gin (tags)
  WITH (fastupdate = on, gin_pending_list_limit = 4096);   -- 单位 KB

-- 用户名前缀补全（text 默认操作符类就是 radix tree 语义）
CREATE INDEX idx_listener_nick ON listeners USING spgist (nickname);
SELECT nickname FROM listeners WHERE nickname ^@ '夜航';   -- 前缀匹配走索引

-- 网络地址：IP 白名单包含判断
CREATE INDEX idx_listener_ip ON listeners USING spgist (last_ip inet_ops);
SELECT * FROM listeners WHERE last_ip << '192.168.0.0/16';

CREATE INDEX idx_le_created ON listen_events USING brin (created_at)
  WITH (pages_per_range = 32);

-- 1. 无阻塞创建/删除：CONCURRENTLY 不拿排他锁，线上建索引的标准动作
CREATE INDEX CONCURRENTLY idx_ep_title ON episodes (title);
DROP INDEX CONCURRENTLY IF EXISTS idx_ep_title;

-- 2. 重建：膨胀或损坏后重建；REINDEX 也有 CONCURRENTLY 版本（PG 12+）
REINDEX INDEX CONCURRENTLY idx_ep_title;

-- 3. 盘点：一个表上到底有什么索引
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'episodes';

-- 4. 用量审计：找出从没被用过的索引（先重置统计再观察一周）
SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE relname = 'episodes' AND idx_scan = 0;