-- 创建 B-Tree 索引（默认类型）
CREATE INDEX idx_emp_email ON employees (email);
-- 等价于
CREATE INDEX idx_emp_email ON employees USING BTREE (email);

-- 多列索引
CREATE INDEX idx_emp_dept_salary ON employees (department, salary);

-- 倒序索引
CREATE INDEX idx_emp_salary_desc ON employees (salary DESC);

-- 表达式索引
CREATE INDEX idx_emp_lower_email ON employees (LOWER(email));

-- 部分索引（带 WHERE 条件）
CREATE INDEX idx_emp_active ON employees (email) WHERE is_active = TRUE;

-- 覆盖索引（INCLUDE，PG 11+）
CREATE INDEX idx_emp_covering ON employees (department) INCLUDE (name, salary);
-- 查询 SELECT name, salary FROM employees WHERE department = '工程部' 可以只用索引

CREATE INDEX idx_emp_name_hash ON employees USING HASH (name);

-- 仅适用于等值查询
SELECT * FROM employees WHERE name = 'Alice';  -- ✅ 使用 Hash 索引
SELECT * FROM employees WHERE name > 'Alice';  -- ❌ 不使用 Hash 索引

-- 需要 btree_gist 扩展（排他约束用）
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- 几何数据索引
CREATE INDEX idx_geo ON places USING GIST (location);

-- Range 类型索引
CREATE INDEX idx_res_during ON reservations USING GIST (during);

-- 全文搜索索引
CREATE INDEX idx_fts ON articles USING GIST (to_tsvector('english', content));

-- 排他约束
CREATE TABLE rooms (
    id SERIAL PRIMARY KEY,
    room_no INT,
    during TSTZRANGE,
    EXCLUDE USING GIST (room_no WITH =, during WITH &&)
);

-- JSONB GIN 索引
CREATE INDEX idx_events_data ON events USING GIN (data);

-- 数组 GIN 索引
CREATE INDEX idx_products_tags ON products USING GIN (tags);

-- 全文搜索 GIN 索引
CREATE INDEX idx_articles_fts ON articles USING GIN (
    to_tsvector('simple', title || ' ' || content)
);

-- jsonb_path_ops（仅支持 @> 操作符，但更小更快）
CREATE INDEX idx_events_data_path ON events USING GIN (data jsonb_path_ops);

-- BRIN 索引
CREATE INDEX idx_logs_created ON access_logs USING BRIN (created_at)
    WITH (pages_per_range = 128);

-- 适合场景：时序数据、自增 ID、时间戳
-- 数据特征：物理顺序与逻辑顺序高度相关

-- 比较索引大小
SELECT
    indexname,
    pg_size_pretty(pg_relation_size(indexname::regclass)) AS index_size
FROM pg_indexes
WHERE tablename = 'access_logs';

-- 只索引活跃用户
CREATE INDEX idx_active_users ON users (email) WHERE is_active = TRUE;

-- 只索引未完成订单
CREATE INDEX idx_pending_orders ON orders (created_at)
WHERE status IN (0, 1, 2);

-- 使用部分索引的查询必须包含索引条件
SELECT * FROM users WHERE email = '[email protected]' AND is_active = TRUE;  -- ✅
SELECT * FROM users WHERE email = '[email protected]';  -- ❌ 可能不使用部分索引

-- 查看表的所有索引
\d+ employees

-- 查看所有索引及其大小
SELECT
    schemaname, tablename, indexname,
    pg_size_pretty(pg_relation_size(indexname::regclass)) AS size,
    idx_scan AS scans
FROM pg_indexes
JOIN pg_stat_user_indexes USING (schemaname, tablename, indexname)
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexname::regclass) DESC;

-- 重建索引（不阻塞查询）
REINDEX INDEX CONCURRENTLY idx_emp_email;

-- 重建表的所有索引
REINDEX TABLE CONCURRENTLY employees;

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

-- 创建索引时不阻塞写入（必须！）
CREATE INDEX CONCURRENTLY idx_new ON employees (department);

-- 查找从未使用过的索引
SELECT
    schemaname, relname AS table, indexrelname AS index,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS size,
    idx_scan AS scans
FROM pg_stat_user_indexes i
JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0
  AND NOT indisunique       -- 排除唯一索引
  AND NOT indisprimary      -- 排除主键
ORDER BY pg_relation_size(i.indexrelid) DESC;

-- 查找重复/冗余索引
SELECT
    a.indexrelid::regclass AS index_a,
    b.indexrelid::regclass AS index_b,
    pg_size_pretty(pg_relation_size(a.indexrelid)) AS size_a
FROM pg_index a
JOIN pg_index b ON a.indrelid = b.indrelid
    AND a.indexrelid <> b.indexrelid
    AND a.indkey::TEXT LIKE b.indkey::TEXT || '%'
WHERE a.indrelid::regclass::TEXT NOT LIKE 'pg_%';