# 生产环境服务器参数优化

# 编辑postgresql.conf文件
$ sudo vi /postgresql/fgdata/postgresql.conf

# 内存参数
shared_buffers = 8GB
work_mem = 64MB
maintenance_work_mem = 2GB
autovacuum_work_mem = 512MB

# 连接参数
max_connections = 1000

# 写入性能参数
wal_buffers = 64MB
synchronous_commit = on
commit_delay = 0
commit_siblings = 5

# 查询优化参数
effective_cache_size = 24GB
random_page_cost = 1.1
seq_page_cost = 1.0

# 自动清理参数
autovacuum = on
autovacuum_max_workers = 4
autovacuum_naptime = 10s
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.025

# 重启PostgreSQL
$ sudo systemctl restart postgresql

# 验证参数配置
$ psql -U fgedu -d fgedudb -c “SHOW shared_buffers;”
shared_buffers
—————-
8GB

$ psql -U fgedu -d fgedudb -c “SHOW work_mem;”
work_mem
———-
64MB

$ psql -U fgedu -d fgedudb -c “SHOW effective_cache_size;”
effective_cache_size
———————-
24GB

# 优化前的复杂查询
SELECT
u.id, u.name, u.email,
COUNT(o.id) as order_count,
SUM(o.amount) as total_amount
FROM
fgedu_users u
LEFT JOIN
fgedu_orders o ON u.id = o.user_id
WHERE
u.active = true
GROUP BY
u.id, u.name, u.email
ORDER BY
total_amount DESC
LIMIT 10;

# 优化后的查询
SELECT
u.id, u.name, u.email,
COALESCE(order_stats.order_count, 0) as order_count,
COALESCE(order_stats.total_amount, 0) as total_amount
FROM
fgedu_users u
LEFT JOIN (
SELECT
user_id,
COUNT(*) as order_count,
SUM(amount) as total_amount
FROM
fgedu_orders
GROUP BY
user_id
) as order_stats ON u.id = order_stats.user_id
WHERE
u.active = true
ORDER BY
total_amount DESC
LIMIT 10;

# 执行计划对比
# 优化前
HashAggregate (cost=23.01..25.01 rows=100 width=56) (actual time=0.123..0.124 rows=10 loops=1)
Group Key: u.id, u.name, u.email
-> Hash Left Join (cost=8.01..18.01 rows=1000 width=28) (actual time=0.034..0.076 rows=1000 loops=1)
Hash Cond: (u.id = o.user_id)
-> Seq Scan on fgedu_users u (cost=0.00..4.01 rows=100 width=20) (actual time=0.010..0.015 rows=100 loops=1)
Filter: active
Rows Removed by Filter: 900
-> Hash (cost=4.01..4.01 rows=1000 width=12) (actual time=0.018..0.018 rows=1000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 44kB
-> Seq Scan on fgedu_orders o (cost=0.00..4.01 rows=1000 width=12) (actual time=0.006..0.009 rows=1000
loops=1)
Planning Time: 0.123 ms
Execution Time: 0.145 ms

# 优化后
Hash Left Join (cost=12.02..16.02 rows=100 width=56) (actual time=0.067..0.070 rows=10 loops=1)
Hash Cond: (u.id = order_stats.user_id)
-> Seq Scan on fgedu_users u (cost=0.00..4.01 rows=100 width=20) (actual time=0.011..0.016 rows=100 loops=1)
Filter: active
Rows Removed by Filter: 900
-> Hash (cost=8.01..8.01 rows=100 width=40) (actual time=0.048..0.048 rows=100 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 12kB
-> HashAggregate (cost=4.01..8.01 rows=100 width=40) (actual time=0.036..0.041 rows=100 loops=1)
Group Key: o.user_id
-> Seq Scan on fgedu_orders o (cost=0.00..4.01 rows=1000 width=12) (actual time=0.007..0.009 rows=1000
loops=1)
Planning Time: 0.156 ms
Execution Time: 0.092 ms

# 索引设计与优化

# 1. 分析查询模式
$ psql -U fgedu -d fgedudb -c “SELECT * FROM pg_stat_user_tables WHERE schemaname = ‘public’;”

# 2. 识别频繁查询的列
$ psql -U fgedu -d fgedudb -c “SELECT * FROM pg_stat_user_indexes WHERE schemaname = ‘public’;”

# 3. 创建合适的索引
# 为频繁查询的列创建索引
$ psql -U fgedu -d fgedudb -c “CREATE INDEX fgedu_orders_user_id_idx ON fgedu_orders(user_id);”

# 为复合查询创建复合索引
$ psql -U fgedu -d fgedudb -c “CREATE INDEX fgedu_orders_user_id_created_at_idx ON fgedu_orders(user_id,
created_at);”

# 为范围查询创建索引
$ psql -U fgedu -d fgedudb -c “CREATE INDEX fgedu_orders_created_at_idx ON fgedu_orders(created_at);”

# 4. 验证索引使用情况
$ psql -U fgedu -d fgedudb -c “EXPLAIN ANALYZE SELECT * FROM fgedu_orders WHERE user_id = 1 AND created_at >
‘2026-04-01’;”

# 输出示例
Index Scan using fgedu_orders_user_id_created_at_idx on fgedu_orders (cost=0.29..8.31 rows=10 width=28)
(actual time=0.020..0.021 rows=10 loops=1)
Index Cond: ((user_id = 1) AND (created_at > ‘2026-04-01’::date))
Planning Time: 0.084 ms
Execution Time: 0.032 ms

# 5. 清理无用索引
$ psql -U fgedu -d fgedudb -c “DROP INDEX IF EXISTS fgedu_orders_old_idx;”