┌─────────────────────────────────────────────────────────────┐
│                       索引工作原理                            │
├─────────────────────────────────────────────────────────────┤
│  无索引:  全表扫描 → O(n)                                    │
│  B-Tree: 二叉搜索 → O(log n)                                │
│  Hash:   哈希查找 → O(1)                                     │
└─────────────────────────────────────────────────────────────┘

-- 创建 B-Tree 索引（默认类型）
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_status ON users(status);

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

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

-- 部分索引
CREATE INDEX idx_orders_completed ON orders(created_at) 
WHERE status = 'completed';

-- 查看索引使用情况
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- 创建 Hash 索引
CREATE INDEX idx_sessions_token ON sessions USING HASH (token);

-- 查看索引类型
\d sessions

-- 空间数据索引
CREATE EXTENSION IF NOT EXISTS postgis;

-- 创建 GiST 索引用于地理位置
CREATE INDEX idx_locations_geo ON locations USING GIST (geolocation);

-- 范围类型索引
CREATE INDEX idx_reservations_period ON reservations USING GIST (stay_period);

-- JSONB 索引
CREATE INDEX idx_products_attrs ON products USING GIN (attributes);

-- 数组索引
CREATE INDEX idx_tags ON posts USING GIN (tags);

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

-- BRIN 索引（块范围索引）
CREATE INDEX idx_logs_created ON logs USING BRIN (created_at);

-- 适合大表且数据有序存储的情况
CREATE INDEX idx_logs_created ON logs USING BRIN (created_at) 
WITH (pages_per_range = 128);

-- 单列索引
CREATE INDEX idx_table_column ON table_name(column);

-- 复合索引
CREATE INDEX idx_table_cols ON table_name(column1, column2);

-- 唯一索引
CREATE UNIQUE INDEX idx_user_email ON users(email);

-- 条件索引（部分索引）
CREATE INDEX idx_active_users ON users(last_login) 
WHERE status = 'active';

-- 表达式索引
CREATE INDEX idx_users_name ON users(UPPER(name));

-- 在大数据表上并行创建索引
CREATE INDEX CONCURRENTLY idx_large_table_date 
ON large_table(created_at);

-- 重建索引
REINDEX INDEX CONCURRENTLY idx_users_email;

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

-- 查看索引大小
SELECT 
    indexname,
    pg_size_pretty(pg_relation_size(indexname))
FROM pg_indexes
WHERE tablename = 'users';

-- 重命名索引
ALTER INDEX old_index_name RENAME TO new_index_name;

-- 删除索引
DROP INDEX idx_unused_index;

-- 检查未使用的索引
SELECT 
    schemaname || '.' || relname AS table,
    indexrelname AS index,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
    idx_scan AS index_scans
FROM pg_stat_user_indexes ui
JOIN pg_index i ON ui.indexrelid = i.indexrelid
WHERE idx_scan = 0
ORDER BY pg_relation_size(i.indexrelid) DESC;

-- 基本分析
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- 分析并显示实际执行信息
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM users WHERE email = 'test@example.com';

-- JSON 格式输出
EXPLAIN (FORMAT JSON)
SELECT * FROM orders WHERE status = 'completed';

-- Seq Scan：全表扫描
EXPLAIN SELECT * FROM users WHERE age > 30;

-- Index Scan：索引扫描
EXPLAIN SELECT * FROM users WHERE id = 1;

-- Index Only Scan：仅索引扫描
EXPLAIN SELECT email FROM users WHERE email LIKE 'a%';

-- Nested Loop：嵌套循环连接
EXPLAIN SELECT * FROM orders o, users u WHERE o.user_id = u.id;

-- Hash Join：哈希连接
EXPLAIN SELECT * FROM orders o JOIN users u ON o.user_id = u.id;

-- Merge Join：归并连接
EXPLAIN SELECT * FROM orders o JOIN users u ON o.user_id = u.id ORDER BY u.id;

-- 查看最近执行的慢查询
SELECT 
    pid,
    now() - query_start AS duration,
    usename,
    query,
    state
FROM pg_stat_activity
WHERE state != 'idle'
    AND now() - query_start > interval '5 minutes';

-- 终止慢查询
SELECT pg_cancel_backend(pid);  -- 优雅终止
SELECT pg_terminate_backend(pid); -- 强制终止

-- 使用 LIMIT 避免全表扫描
SELECT * FROM large_table LIMIT 10;

-- 避免 SELECT *
SELECT user_id, email FROM users WHERE id = 1;

-- 使用 LIMIT OFFSET 分页（大数据量时效率低）
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10000;

-- 使用游标分页（推荐）
SELECT * FROM users WHERE id > 10000 ORDER BY id LIMIT 10;

-- 使用 EXISTS 代替 IN
SELECT * FROM users WHERE EXISTS (
    SELECT 1 FROM orders WHERE user_id = users.id AND total > 100
);

-- 批量插入
INSERT INTO users (username, email) VALUES
    ('user1', 'user1@example.com'),
    ('user2', 'user2@example.com'),
    ('user3', 'user3@example.com');

-- 使用 COPY 进行高效导入
COPY users(username, email) FROM '/path/to/users.csv' WITH (FORMAT csv);

-- 批量更新
UPDATE orders SET status = 'completed', updated_at = NOW()
WHERE id IN (1, 2, 3, 4, 5);

-- 分批更新大表
UPDATE orders SET status = 'completed'
WHERE id IN (SELECT id FROM orders WHERE status = 'pending' LIMIT 1000);

-- 确保连接列有索引
-- orders.user_id 和 users.id 都需要有索引

-- 优先过滤小表
SELECT u.username, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'  -- 过滤小结果集
ORDER BY o.total DESC;

-- 优化多表连接顺序
EXPLAIN 
SELECT *
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id
WHERE o.created_at > '2024-01-01';

# postgresql.conf

# 共享缓冲区（建议为系统内存的 25%）
shared_buffers = 4GB

# 单次排序内存
work_mem = 64MB

# 维护操作内存（VACUUM、CREATE INDEX 等）
maintenance_work_mem = 512MB

# 有效缓存大小（用于查询规划）
effective_cache_size = 12GB

# 最大并行 worker 数
max_parallel_workers_per_gather = 4

# 维护操作的并行度
max_parallel_workers_maintenance_workers = 4

# 总并行 worker 数
max_worker_processes = 16

# WAL 缓冲区
wal_buffers = 16MB

# 异步提交
async_commit = on

# 检查点配置
checkpoint_timeout = 15min
max_wal_size = 2GB
min_wal_size = 1GB

-- 手动 VACUUM
VACUUM ANALYZE users;

-- VACUUM 详细输出
VACUUM VERBOSE ANALYZE users;

-- 只回收空间不更新统计信息
VACUUM users;

-- 截断表末尾空白页
VACUUM FULL users;

# postgresql.conf

# 启用自动清理
autovacuum = on

# 自动清理触发阈值
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50

# 垃圾比例阈值
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05

# 最大工作进程数
autovacuum_max_workers = 4