# 步骤1：安装LLVM
# Ubuntu/Debian
sudo apt update
sudo apt install -y llvm-14-dev clang-14

# CentOS/RHEL
sudo yum install -y llvm-devel clang

# 步骤2：编译PostgreSQL时启用JIT
./configure –with-llvm
make -j4
sudo make install

# 步骤3：启用JIT
ALTER SYSTEM SET jit = ‘on’;
SELECT pg_reload_conf();

# 步骤4：验证JIT是否启用
SHOW jit;

# 结果示例
jit
—–
on
(1 row)

# 步骤5：测试JIT
EXPLAIN ANALYZE SELECT
SUM(a),
AVG(b),
MAX(c),
MIN(d)
FROM (
SELECT
random() as a,
random() * 100 as b,
random() * 1000 as c,
random() * 10000 as d
FROM generate_series(1, 1000000)
) t;

# 步骤1：启用JIT
ALTER SYSTEM SET jit = ‘on’;
SELECT pg_reload_conf();

# 步骤2：调整JIT参数
ALTER SYSTEM SET jit_above_cost = ‘100000’;
ALTER SYSTEM SET jit_inline_above_cost = ‘500000’;
ALTER SYSTEM SET jit_optimize_above_cost = ‘500000’;
SELECT pg_reload_conf();

# 步骤3：测试JIT效果
— 测试查询1：复杂聚合查询
EXPLAIN ANALYZE SELECT
DATE_TRUNC(‘month’, order_date) as month,
SUM(amount) as total_amount,
COUNT(*) as order_count,
AVG(amount) as avg_amount,
MAX(amount) as max_amount,
MIN(amount) as min_amount
FROM fgedu_orders
GROUP BY month
ORDER BY month;

— 测试查询2：复杂条件查询
EXPLAIN ANALYZE SELECT
*
FROM fgedu_orders o
JOIN fgedu_customers c ON o.customer_id = c.id
WHERE o.order_date >= ‘2024-01-01’
AND o.amount > 1000
AND c.country = ‘China’
ORDER BY o.amount DESC
LIMIT 100;

# 步骤4：监控JIT使用情况
SELECT
queryid,
calls,
total_exec_time,
mean_exec_time,
jit_functions,
jit_generation_time,
jit_inlining_time,
jit_optimization_time,
jit_emission_time
FROM pg_stat_statements
WHERE jit_functions > 0
ORDER BY total_exec_time DESC
LIMIT 10;

# 策略1：启用JIT
– 对于复杂查询，启用JIT以提高性能
– 对于简单查询，禁用JIT以减少开销

# 策略2：调整JIT参数
– 根据查询特点调整jit_above_cost
– 调整jit_inline_above_cost和jit_optimize_above_cost
– 根据系统资源调整jit_max_size

# 策略3：监控JIT使用
– 监控JIT的使用频率和效果
– 分析JIT编译时间和内存使用
– 优化JIT配置

# 策略4：测试JIT效果
– 对比启用和禁用JIT的查询性能
– 找出适合JIT的查询类型
– 优化查询以充分利用JIT

# 示例：JIT使用策略
— 为复杂查询启用JIT
ALTER SYSTEM SET jit = ‘on’;
ALTER SYSTEM SET jit_above_cost = ‘50000’;
SELECT pg_reload_conf();

— 为简单查询禁用JIT
— 在会话级别禁用JIT
SET jit = ‘off’;

# 调优步骤
1. 分析查询性能瓶颈
2. 调整JIT参数
3. 测试JIT效果
4. 优化查询
5. 监控性能改进

# 调优建议
– 合理设置jit_above_cost：根据查询成本调整
– 调整jit_inline_above_cost：控制内联行为
– 调整jit_optimize_above_cost：控制优化级别
– 合理设置jit_max_size：根据系统内存调整
– 优化查询：减少查询复杂度

# 示例：JIT性能调优
— 调整JIT参数
ALTER SYSTEM SET jit_above_cost = ‘50000’;
ALTER SYSTEM SET jit_inline_above_cost = ‘250000’;
ALTER SYSTEM SET jit_optimize_above_cost = ‘250000’;
ALTER SYSTEM SET jit_max_size = ‘500000’;
SELECT pg_reload_conf();

— 监控JIT性能
SELECT
queryid,
query,
calls,
total_exec_time,
mean_exec_time,
jit_functions,
jit_generation_time,
jit_inlining_time,
jit_optimization_time,
jit_emission_time
FROM pg_stat_statements
WHERE jit_functions > 0
ORDER BY total_exec_time DESC
LIMIT 10;