# 步骤1：分析查询性能
– 识别慢查询
– 分析执行计划
– 确定性能瓶颈

# 步骤2：调整统计信息
– 更新统计信息
– 调整统计信息目标
– 创建扩展统计信息

# 步骤3：调整成本参数
– 根据存储类型调整random_page_cost
– 根据CPU性能调整cpu_tuple_cost等参数

# 步骤4：调整并行查询
– 根据CPU核心数调整并行度
– 为大型查询启用并行执行

# 步骤5：优化查询语句
– 重写查询语句
– 创建合适的索引
– 避免不必要的计算

# 步骤6：测试性能
– 比较优化前后的性能
– 验证优化效果

# 示例：查询优化器调优实施
— 分析慢查询
EXPLAIN ANALYZE SELECT * FROM fgedu_orders WHERE order_date BETWEEN ‘2024-01-01’ AND ‘2024-12-31’;

— 更新统计信息
ANALYZE fgedu_orders;

— 调整成本参数
ALTER SYSTEM SET random_page_cost = ‘1.1’;

— 创建索引
CREATE INDEX idx_fgedu_orders_order_date ON fgedu_orders(order_date);

— 测试性能
EXPLAIN ANALYZE SELECT * FROM fgedu_orders WHERE order_date BETWEEN ‘2024-01-01’ AND ‘2024-12-31’;

# 步骤1：修改全局配置
ALTER SYSTEM SET random_page_cost = ‘1.1’;
ALTER SYSTEM SET seq_page_cost = ‘1.0’;
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’;
ALTER SYSTEM SET default_statistics_target = ‘200’;
ALTER SYSTEM SET max_parallel_workers_per_gather = ‘4’;
SELECT pg_reload_conf();

# 步骤2：表级配置
ALTER TABLE fgedu_orders SET (
autovacuum_analyze_scale_factor = 0.025
);

# 步骤3：列级配置
ALTER TABLE fgedu_orders ALTER COLUMN order_date SET STATISTICS 1000;

# 步骤4：验证配置
SHOW random_page_cost;
SHOW default_statistics_target;
SHOW max_parallel_workers_per_gather;

# 结果示例
random_page_cost
—————–
1.1

default_statistics_target
—————————
200

max_parallel_workers_per_gather
——————————–
4

# 策略1：成本参数调优
– 根据硬件特性调整成本参数
– 对于SSD存储，降低random_page_cost
– 对于高性能CPU，降低cpu_tuple_cost

# 策略2：统计信息调优
– 增加统计信息目标
– 定期更新统计信息
– 使用扩展统计信息

# 策略3：并行查询调优
– 根据CPU核心数调整并行度
– 为大型查询启用并行执行
– 监控并行查询性能

# 策略4：索引调优
– 创建合适的索引
– 优化索引设计
– 定期维护索引

# 策略5：查询语句调优
– 重写查询语句
– 避免不必要的计算
– 使用合适的连接方式

# 示例：查询优化器调优策略
— 成本参数调优
ALTER SYSTEM SET random_page_cost = ‘1.1’;

— 统计信息调优
ALTER TABLE fgedu_orders ALTER COLUMN order_date SET STATISTICS 1000;
ANALYZE fgedu_orders;

— 并行查询调优
ALTER SYSTEM SET max_parallel_workers_per_gather = ‘4’;

— 索引调优
CREATE INDEX idx_fgedu_orders_customer_date ON fgedu_orders(customer_id, order_date);

— 查询语句调优
— 优化前
SELECT * FROM fgedu_orders WHERE customer_id = 1 AND order_date >= ‘2024-01-01’;

— 优化后
SELECT id, order_date, amount FROM fgedu_orders WHERE customer_id = 1 AND order_date >= ‘2024-01-01’;

# 技巧1：使用CTE优化复杂查询
– 将复杂查询拆分为多个CTE
– 提高查询可读性
– 帮助优化器生成更好的执行计划

# 技巧2：使用物化视图
– 对于复杂查询，使用物化视图
– 预计算结果，提高查询性能
– 定期刷新物化视图

# 技巧3：使用分区表
– 对于大型表，使用分区表
– 提高查询性能
– 便于管理数据

# 技巧4：使用并行查询
– 对于大型查询，启用并行执行
– 充分利用多核CPU
– 提高查询性能

# 技巧5：使用扩展统计信息
– 对于相关列，创建扩展统计信息
– 提高执行计划的准确性
– 优化复杂查询

# 示例：高级调优技巧
— 使用CTE
WITH customer_orders AS (
SELECT customer_id, SUM(amount) as total_amount
FROM fgedu_orders
GROUP BY customer_id
)
SELECT * FROM customer_orders WHERE total_amount > 1000;

— 使用物化视图
CREATE MATERIALIZED VIEW fgedu_orders_summary AS
SELECT
DATE_TRUNC(‘month’, order_date) as month,
SUM(amount) as total_amount,
COUNT(*) as order_count
FROM fgedu_orders
GROUP BY month;

— 刷新物化视图
REFRESH MATERIALIZED VIEW fgedu_orders_summary;

— 使用分区表
CREATE TABLE fgedu_orders_partitioned (
id SERIAL PRIMARY KEY,
customer_id INTEGER,
order_date DATE,
amount DECIMAL
)
PARTITION BY RANGE (order_date);

— 创建分区
CREATE TABLE fgedu_orders_2024 PARTITION OF fgedu_orders_partitioned
FOR VALUES FROM (‘2024-01-01’) TO (‘2025-01-01’);

— 使用扩展统计信息
CREATE STATISTICS fgedu_orders_stats (dependencies) ON customer_id, order_date FROM fgedu_orders;
ANALYZE fgedu_orders;