# 步骤1：修改统计信息配置
ALTER SYSTEM SET autovacuum_analyze_scale_factor = ‘0.05’;
ALTER SYSTEM SET autovacuum_analyze_threshold = ’50’;
SELECT pg_reload_conf();

# 步骤2：手动执行ANALYZE
ANALYZE fgedu_fgedus;
ANALYZE fgedu_orders;

# 步骤3：查看统计信息
SELECT
relname,
n_live_tup,
n_dead_tup,
last_analyze,
last_autoanalyze
FROM pg_stat_fgedu_tables
WHERE relname LIKE ‘fgedu_%’;

# 结果示例
-[ RECORD 1 ]——+————————
relname | fgedu_fgedus
n_live_tup | 1000
n_dead_tup | 100
last_analyze | 2024-01-01 12:00:00
last_autoanalyze | 2024-01-01 11:00:00

# 步骤4：使用扩展统计信息
CREATE STATISTICS fgedu_fgedus_stats (dependencies) ON fgeduname, email FROM fgedu_fgedus;
ANALYZE fgedu_fgedus;

# 步骤1：分析查询
EXPLAIN ANALYZE SELECT * FROM fgedu_fgedus WHERE email = ‘fgedu@fgedu.net.cn’;

# 步骤2：创建索引
CREATE INDEX idx_fgedu_fgedus_email ON fgedu_fgedus(email);

# 步骤3：验证索引使用
EXPLAIN ANALYZE SELECT * FROM fgedu_fgedus WHERE email = ‘fgedu@fgedu.net.cn’;

# 结果示例
QUERY PLAN
——————————————————————–
Bitmap Heap Scan on fgedu_fgedus (cost=4.29..14.58 rows=1 width=100)
Recheck Cond: (email = ‘fgedu@fgedu.net.cn’)
-> Bitmap Index Scan on idx_fgedu_fgedus_email (cost=0.00..4.29 rows=1 width=0)
Index Cond: (email = ‘fgedu@fgedu.net.cn’)
Planning Time: 0.103 ms
Execution Time: 0.234 ms

# 步骤4：维护索引
REINDEX INDEX idx_fgedu_fgedus_email;

# 策略1：选择合适的索引类型
– B-tree：适用于等值查询和范围查询
– Hash：适用于等值查询
– GiST：适用于几何数据和全文搜索
– GIN：适用于数组和JSONB
– BRIN：适用于大型表的范围查询

# 策略2：创建复合索引
– 考虑查询条件的顺序
– 前缀匹配原则
– 避免过多的列

# 策略3：使用部分索引
– 只索引常用的行
– 减少索引大小
– 提高查询性能

# 示例：索引策略
— 创建B-tree索引
CREATE INDEX idx_fgedu_fgedus_email ON fgedu_fgedus(email);

— 创建复合索引
CREATE INDEX idx_fgedu_orders_customer_date ON fgedu_orders(customer_id, order_date);

— 创建部分索引
CREATE INDEX idx_fgedu_fgedus_active ON fgedu_fgedus(email) WHERE active = true;

# 策略1：优化WHERE子句
– 使用索引列
– 避免使用函数
– 避免使用LIKE ‘%…’

# 策略2：优化JOIN操作
– 使用合适的连接类型
– 小表驱动大表
– 使用索引连接

# 策略3：优化聚合操作
– 使用索引
– 考虑部分聚合
– 避免不必要的聚合

# 示例：查询策略
— 优化WHERE子句
— 优化前
SELECT * FROM fgedu_fgedus WHERE LOWER(email) = ‘fgedu@fgedu.net.cn’;

— 优化后
SELECT * FROM fgedu_fgedus WHERE email = ‘fgedu@fgedu.net.cn’;

— 优化JOIN操作
— 优化前
SELECT * FROM fgedu_orders o JOIN fgedu_fgedus u ON o.customer_id = u.id;

— 优化后
SELECT o.order_id, o.amount, u.fgeduname FROM fgedu_orders o JOIN fgedu_fgedus u ON o.customer_id = u.id;

# 存储类型调优
– HDD：random_page_cost = 4.0
– SSD：random_page_cost = 1.1
– 内存：random_page_cost = 1.0

# CPU调优
– cpu_tuple_cost：CPU处理每个元组的成本
– cpu_index_tuple_cost：CPU处理每个索引元组的成本
– cpu_operator_cost：CPU处理每个操作符的成本

# 缓存调优
effective_cache_size：系统可用的缓存大小

# 示例：成本参数调优
— SSD存储
ALTER SYSTEM SET random_page_cost = ‘1.1’;

— 调整缓存大小
ALTER SYSTEM SET effective_cache_size = ‘8GB’;

— 调整CPU成本
ALTER SYSTEM SET cpu_tuple_cost = ‘0.01’;
ALTER SYSTEM SET cpu_index_tuple_cost = ‘0.005’;
ALTER SYSTEM SET cpu_operator_cost = ‘0.0025’;

SELECT pg_reload_conf();

# 并行度调优
max_parallel_workers_per_gather：每个Gather节点的最大并行工作线程数
max_parallel_workers：系统的最大并行工作线程数
max_parallel_maintenance_workers：维护操作的最大并行工作线程数

# 表级并行
ALTER TABLE fgedu_fgedus SET (parallel_workers = 4);

# 示例：并行查询调优
— 调整并行度
ALTER SYSTEM SET max_parallel_workers_per_gather = ‘4’;
ALTER SYSTEM SET max_parallel_workers = ‘8’;

— 启用表级并行
ALTER TABLE fgedu_fgedus SET (parallel_workers = 4);

— 测试并行查询
EXPLAIN ANALYZE SELECT * FROM fgedu_fgedus WHERE age > 30;

# 结果示例
QUERY PLAN
——————————————————————–
Gather (cost=1000.00..1234.56 rows=1000 width=100)
Workers Planned: 4
Workers Launched: 4
-> Parallel Seq Scan on fgedu_fgedus (cost=0.00..1134.56 rows=250 width=100)
Filter: (age > 30)
Rows Removed by Filter: 750
Planning Time: 0.103 ms
Execution Time: 5.234 ms