# 场景：数据仓库复杂查询

# 优化前
– 表连接数：15个
– 规划时间：5秒
– 执行时间：10秒
– 使用传统优化器

# 优化后
– 表连接数：15个
– 规划时间：0.5秒
– 执行时间：8秒
– 使用GEQO

# 优化步骤
1. 启用GEQO
2. 调整GEQO参数
3. 测试性能
4. 微调参数

# 示例：复杂查询优化
— 启用GEQO
ALTER SYSTEM SET geqo = ‘on’;
ALTER SYSTEM SET geqo_threshold = ’10’;
ALTER SYSTEM SET geqo_effort = ‘7’;
SELECT pg_reload_conf();

— 执行复杂查询
EXPLAIN ANALYZE SELECT
c.customer_name,
p.product_name,
SUM(o.amount) as total_amount,
COUNT(o.order_id) as order_count
FROM fgedu_customers c
JOIN fgedu_orders o ON c.customer_id = o.customer_id
JOIN fgedu_order_items oi ON o.order_id = oi.order_id
JOIN fgedu_products p ON oi.product_id = p.product_id
JOIN fgedu_categories cat ON p.category_id = cat.category_id
JOIN fgedu_suppliers s ON p.supplier_id = s.supplier_id
JOIN fgedu_shipping_methods sm ON o.shipping_method_id = sm.shipping_method_id
JOIN fgedu_payment_methods pm ON o.payment_method_id = pm.payment_method_id
WHERE o.order_date BETWEEN ‘2024-01-01’ AND ‘2024-12-31’
GROUP BY c.customer_name, p.product_name
ORDER BY total_amount DESC
LIMIT 10;

# 结果示例
QUERY PLAN
——————————————————————–
Limit (cost=1000.00..12345.67 rows=10 width=50)
-> Sort (cost=1000.00..12345.67 rows=10000 width=50)
Sort Key: sum(o.amount) DESC
-> HashAggregate (cost=1000.00..11345.67 rows=10000 width=50)
Group Key: c.customer_name, p.product_name
-> Nested Loop (cost=1000.00..9345.67 rows=100000 width=40)
-> Nested Loop (cost=1000.00..8345.67 rows=100000 width=36)
-> Nested Loop (cost=1000.00..7345.67 rows=100000 width=32)
-> Nested Loop (cost=1000.00..6345.67 rows=100000 width=28)
-> Nested Loop (cost=1000.00..5345.67 rows=100000 width=24)
-> Nested Loop (cost=1000.00..4345.67 rows=100000 width=20)
-> Nested Loop (cost=1000.00..3345.67 rows=100000 width=16)
-> Seq Scan on fgedu_orders o (cost=0.00..1000.00 rows=100000 width=12)
Filter: ((order_date >= ‘2024-01-01’::date) AND (order_date <= '2024-12-31'::date)) -> Index Scan using fgedu_customers_pkey on fgedu_customers c (cost=0.29..0.02 rows=1 width=8)
Index Cond: (customer_id = o.customer_id)
-> Index Scan using fgedu_order_items_order_id_idx on fgedu_order_items oi (cost=0.29..0.01 rows=1 width=8)
Index Cond: (order_id = o.order_id)
-> Index Scan using fgedu_products_pkey on fgedu_products p (cost=0.29..0.01 rows=1 width=8)
Index Cond: (product_id = oi.product_id)
-> Index Scan using fgedu_categories_pkey on fgedu_categories cat (cost=0.29..0.01 rows=1 width=4)
Index Cond: (category_id = p.category_id)
-> Index Scan using fgedu_suppliers_pkey on fgedu_suppliers s (cost=0.29..0.01 rows=1 width=4)
Index Cond: (supplier_id = p.supplier_id)
-> Index Scan using fgedu_shipping_methods_pkey on fgedu_shipping_methods sm (cost=0.29..0.01 rows=1 width=4)
Index Cond: (shipping_method_id = o.shipping_method_id)
-> Index Scan using fgedu_payment_methods_pkey on fgedu_payment_methods pm (cost=0.29..0.01 rows=1 width=4)
Index Cond: (payment_method_id = o.payment_method_id)
Planning Time: 0.456 ms
Execution Time: 8.123 ms

# 基本用法
EXPLAIN ANALYZE SELECT *
FROM fgedu_table1 t1
JOIN fgedu_table2 t2 ON t1.id = t2.table1_id
JOIN fgedu_table3 t3 ON t2.id = t3.table2_id
JOIN fgedu_table4 t4 ON t3.id = t4.table3_id
JOIN fgedu_table5 t5 ON t4.id = t5.table4_id
WHERE t1.value > 500;

# 结果示例
QUERY PLAN
——————————————————————–
Nested Loop (cost=0.86..37.90 rows=500 width=20)
-> Nested Loop (cost=0.69..25.40 rows=500 width=16)
-> Nested Loop (cost=0.52..16.90 rows=500 width=12)
-> Nested Loop (cost=0.35..10.40 rows=500 width=8)
-> Seq Scan on fgedu_table1 t1 (cost=0.00..3.00 rows=500 width=4)
Filter: (value > 500)
-> Index Scan using fgedu_table2_table1_id_idx on fgedu_table2 t2 (cost=0.35..0.01 rows=1 width=4)
Index Cond: (table1_id = t1.id)
-> Index Scan using fgedu_table3_table2_id_idx on fgedu_table3 t3 (cost=0.17..0.01 rows=1 width=4)
Index Cond: (table2_id = t2.id)
-> Index Scan using fgedu_table4_table3_id_idx on fgedu_table4 t4 (cost=0.17..0.01 rows=1 width=4)
Index Cond: (table3_id = t3.id)
-> Index Scan using fgedu_table5_table4_id_idx on fgedu_table5 t5 (cost=0.17..0.01 rows=1 width=4)
Index Cond: (table4_id = t4.id)
Planning Time: 0.234 ms
Execution Time: 0.567 ms

# 查看GEQO使用情况
— 检查是否使用GEQO
EXPLAIN (ANALYZE, VERBOSE) SELECT *
FROM fgedu_table1 t1
JOIN fgedu_table2 t2 ON t1.id = t2.table1_id
JOIN fgedu_table3 t3 ON t2.id = t3.table2_id
JOIN fgedu_table4 t4 ON t3.id = t4.table3_id
JOIN fgedu_table5 t5 ON t4.id = t5.table4_id
WHERE t1.value > 500;

# 症状：GEQO生成的执行计划质量不如传统优化器

# 解决方法
– 增加geqo_effort
– 增加geqo_pool_size和geqo_generations
– 调整geqo_selection_bias
– 考虑使用传统优化器

# 常见问题2：规划时间过长

# 症状：GEQO规划时间过长

# 解决方法
– 减少geqo_effort
– 减少geqo_pool_size和geqo_generations
– 提高geqo_threshold
– 考虑使用传统优化器

# 常见问题3：GEQO未启用

# 症状：复杂查询仍使用传统优化器

# 解决方法
– 检查geqo参数是否正确设置
– 检查geqo_threshold是否过低
– 确认查询表连接数是否超过geqo_threshold

# 常见问题4：性能不稳定

# 症状：GEQO生成的执行计划性能不稳定

# 解决方法
– 设置固定的geqo_seed
– 调整GEQO参数
– 测试不同参数组合
– 考虑使用传统优化器

# 常见问题5：内存使用高

# 症状：GEQO使用过多内存

# 解决方法
– 减少geqo_pool_size
– 减少geqo_generations
– 提高geqo_threshold
– 考虑使用传统优化器