-- 查看查询执行计划
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
GROUP BY u.name
HAVING COUNT(o.id) > 5;

-- 问题 1: 隐式类型转换导致索引失效
SELECT * FROM users WHERE phone = 13800138000;  -- phone 是 varchar
-- 修复：
SELECT * FROM users WHERE phone = '13800138000';

-- 问题 2: LIKE 前缀通配符无法使用索引
SELECT * FROM users WHERE name LIKE '%张';
-- 修复：使用 pg_trgm 扩展
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_users_name_trgm ON users USING gin (name gin_trgm_ops);

-- 问题 3: OR 条件导致索引失效
SELECT * FROM users WHERE email = 'a@b.com' OR phone = '13800138000';
-- 修复：使用 UNION
SELECT * FROM users WHERE email = 'a@b.com'
UNION
SELECT * FROM users WHERE phone = '13800138000';

-- 单列索引
CREATE INDEX idx_users_email ON users(email);

-- 复合索引（注意列顺序）
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- 部分索引（只索引满足条件的行）
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';

-- 覆盖索引（包含额外列，避免回表）
CREATE INDEX idx_users_email_name ON users(email) INCLUDE (name);

-- 全文搜索
CREATE INDEX idx_posts_content ON posts USING gin(to_tsvector('chinese', content));

-- JSON 字段
CREATE INDEX idx_users_metadata ON users USING gin(metadata);

-- 查询
SELECT * FROM posts WHERE to_tsvector('chinese', content) @@ to_tsquery('数据库');
SELECT * FROM users WHERE metadata @> '{"role": "admin"}';

-- 适合时间序列等自然有序的大表
CREATE INDEX idx_logs_created_brin ON logs USING brin(created_at);

# postgresql.conf

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

# 工作内存（每个排序/哈希操作的内存）
work_mem = 64MB

# 维护操作内存
maintenance_work_mem = 1GB

# 有效缓存大小（告诉优化器系统缓存大小）
effective_cache_size = 12GB

# WAL 缓冲区
wal_buffers = 64MB

# 检查点间隔
checkpoint_completion_target = 0.9
max_wal_size = 4GB
min_wal_size = 1GB

# 最大连接数
max_connections = 200

# 使用连接池（推荐 PgBouncer）
# 应用层连接数可以更高

-- 按月分区
CREATE TABLE orders (
    id BIGSERIAL,
    user_id BIGINT,
    amount DECIMAL(10, 2),
    created_at TIMESTAMP
) PARTITION BY RANGE (created_at);

-- 创建分区
CREATE TABLE orders_2024_01 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE orders_2024_02 PARTITION OF orders
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- 自动创建分区（使用 pg_partman）
CREATE EXTENSION pg_partman;

SELECT partman.create_parent(
    'public.orders',
    'created_at',
    'native',
    'monthly'
);

# pgbouncer.ini
[databases]
mydb = host=localhost dbname=mydb

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 50
reserve_pool_size = 10

-- 启用 pg_stat_statements
CREATE EXTENSION pg_stat_statements;

-- 查看最慢的查询
SELECT
    query,
    calls,
    total_time,
    mean_time,
    rows
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 20;

-- 查看未使用的索引
SELECT
    schemaname,
    relname,
    indexrelname,
    idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- 手动真空
VACUUM ANALYZE users;

-- 查看需要真空的表
SELECT
    relname,
    n_dead_tup,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

-- 调整自动真空参数
ALTER TABLE users SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_analyze_scale_factor = 0.005
);