PostgreSQL教程FG309-PostgreSQL查询优化
本文档风哥主要介绍PostgreSQL查询优化，包括执行计划分析、查询重写、查询提示等内容。风哥教程参考PostgreSQL官方文档Server Administration部分，适合DBA人员在生产环境中使用。
Part01-基础概念与理论知识
1.1 PostgreSQL查询优化概述
PostgreSQL查询优化是指通过各种技术手段，提高SQL查询的执行效率，减少查询响应时间和资源消耗。查询优化是数据库性能优化的重要组成部分，关系到系统的整体性能和用户体验。
- 提高查询响应速度：减少用户等待时间
- 降低系统资源消耗：减少CPU、内存和磁盘I/O使用
- 提高系统吞吐量：处理更多并发查询
- 优化数据库性能：提高整体系统性能
- 降低运维成本：减少硬件和人力资源投入
1.2 PostgreSQL执行计划
执行计划是PostgreSQL查询优化的核心概念，它描述了PostgreSQL如何执行一个SQL查询。执行计划包含了查询的执行步骤、使用的索引、连接方式等信息。
$ psql -U fgedu -d fgedudb -c “EXPLAIN ANALYZE SELECT * FROM fgedu_users WHERE email = ‘test@fgedu.net.cn’;”
# 输出示例
Nested Loop (cost=0.29..8.31 rows=1 width=44) (actual time=0.027..0.028 rows=1 loops=1)
-> Index Scan using fgedu_users_email_idx on fgedu_users (cost=0.29..4.30 rows=1 width=44) (actual time=0.020..0.021 rows=1 loops=1)
Index Cond: (email = ‘test@fgedu.net.cn’::text)
-> Seq Scan on fgedu_users (cost=0.00..4.01 rows=1 width=44) (actual time=0.006..0.006 rows=1 loops=1)
Filter: (email = ‘test@fgedu.net.cn’::text)
Rows Removed by Filter: 999
Planning Time: 0.084 ms
Execution Time: 0.045 ms
# 执行计划组件
1. 节点类型：如Seq Scan、Index Scan、Nested Loop等
2. 成本估算：cost=0.29..8.31，表示执行该节点的预计成本
3. 行数估算：rows=1，表示预计返回的行数
4. 实际执行时间：actual time=0.027..0.028，表示实际执行时间
5. 实际返回行数：rows=1 loops=1，表示实际返回的行数
1.3 PostgreSQL查询性能指标
PostgreSQL查询性能指标主要包括以下几个方面：
- 执行时间：查询的总执行时间
- 扫描行数：查询扫描的行数
- 使用的索引：查询使用的索引类型和名称
- 连接方式：查询使用的连接方式，如Nested Loop、Hash Join等
- 内存使用：查询使用的内存量
- I/O操作：查询产生的I/O操作次数
Part02-生产环境规划与建议
2.1 查询优化规划
在生产环境中，合理的查询优化规划是确保查询性能的关键：
1. 明确性能目标：确定查询的性能目标，如响应时间要求
2. 分析查询模式：了解系统的查询类型和频率
3. 识别性能瓶颈：找出查询中的性能瓶颈
4. 制定优化策略：根据性能瓶颈制定优化策略
5. 建立监控体系：建立查询性能监控系统，及时发现问题
6. 定期评估：定期评估查询性能，调整优化策略
# 查询优化规划示例
– 响应时间目标：95%的查询响应时间小于100ms
– 高频查询：优化访问频率最高的前10个查询
– 复杂查询：优化执行时间最长的前5个查询
– 资源消耗：优化CPU和I/O消耗最高的查询
2.2 查询需求分析
查询需求分析应包括以下内容：
- 业务需求：了解业务对查询的要求
- 数据量：评估查询涉及的数据量大小
- 查询频率：分析查询的执行频率
- 查询复杂度：评估查询的复杂程度
- 性能要求：确定查询的性能要求
2.3 查询调优策略
查询调优策略应考虑以下因素：
1. 执行计划分析：分析查询的执行计划，找出性能瓶颈
2. 索引优化：创建合适的索引，提高查询速度
3. 查询重写：重写查询语句，优化执行计划
4. 参数调整：调整PostgreSQL参数，优化查询性能
5. 表结构优化：优化表结构，提高查询效率
6. 统计信息更新：更新表的统计信息，提高执行计划的准确性
# 查询调优步骤
1. 收集查询信息：收集查询的执行计划和性能指标
2. 分析性能瓶颈：分析查询的性能瓶颈
3. 实施优化措施：根据性能瓶颈实施优化措施
4. 验证优化效果：验证优化措施的效果
5. 持续监控：持续监控查询性能，及时发现问题
Part03-生产环境项目实施方案
3.1 执行计划分析
3.1.1 查看执行计划
$ psql -U fgedu -d fgedudb -c “EXPLAIN SELECT * FROM fgedu_users WHERE email = ‘test@fgedu.net.cn’;”
# 查看详细执行计划（包含实际执行时间）
$ psql -U fgedu -d fgedudb -c “EXPLAIN ANALYZE SELECT * FROM fgedu_users WHERE email = ‘test@fgedu.net.cn’;”
# 查看执行计划的JSON格式
$ psql -U fgedu -d fgedudb -c “EXPLAIN (FORMAT JSON) SELECT * FROM fgedu_users WHERE email = ‘test@fgedu.net.cn’;”
# 查看执行计划的详细信息
$ psql -U fgedu -d fgedudb -c “EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT * FROM fgedu_users WHERE email = ‘test@fgedu.net.cn’;”
3.1.2 分析执行计划
$ psql -U fgedu -d fgedudb -c “EXPLAIN ANALYZE SELECT * FROM fgedu_users WHERE name LIKE ‘%test%’;”
# 输出示例
Seq Scan on fgedu_users (cost=0.00..4.01 rows=10 width=44) (actual time=0.015..0.015 rows=10 loops=1)
Filter: (name ~~ ‘%test%’::text)
Rows Removed by Filter: 990
Planning Time: 0.052 ms
Execution Time: 0.023 ms
# 执行计划分析
1. 节点类型：Seq Scan（顺序扫描），表示全表扫描
2. 成本估算：cost=0.00..4.01，表示执行成本较低
3. 行数估算：rows=10，表示预计返回10行
4. 实际执行时间：actual time=0.015..0.015，表示执行时间较短
5. 扫描行数：Rows Removed by Filter: 990，表示扫描了1000行，过滤了990行
# 优化建议
– 创建合适的索引，避免全表扫描
– 优化查询条件，减少扫描行数
3.2 查询重写
3.2.1 优化查询语句
SELECT * FROM fgedu_users WHERE name LIKE ‘%test%’;
# 优化后的查询
SELECT id, name, email FROM fgedu_users WHERE name LIKE ‘%test%’;
# 优化前的执行计划
Seq Scan on fgedu_users (cost=0.00..4.01 rows=10 width=44) (actual time=0.015..0.015 rows=10 loops=1)
Filter: (name ~~ ‘%test%’::text)
Rows Removed by Filter: 990
Planning Time: 0.052 ms
Execution Time: 0.023 ms
# 优化后的执行计划
Seq Scan on fgedu_users (cost=0.00..4.01 rows=10 width=36) (actual time=0.012..0.012 rows=10 loops=1)
Filter: (name ~~ ‘%test%’::text)
Rows Removed by Filter: 990
Planning Time: 0.048 ms
Execution Time: 0.019 ms
# 优化前的复杂查询
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;
3.3 查询提示
3.3.1 使用查询提示
$ psql -U fgedu -d fgedudb -c “EXPLAIN ANALYZE /*+ SeqScan(fgedu_users) */ SELECT * FROM fgedu_users WHERE name = ‘test’;”
# 输出示例
Seq Scan on fgedu_users (cost=0.00..4.01 rows=1 width=44) (actual time=0.010..0.010 rows=1 loops=1)
Filter: (name = ‘test’::text)
Rows Removed by Filter: 999
Planning Time: 0.032 ms
Execution Time: 0.015 ms
# 使用索引扫描提示
$ psql -U fgedu -d fgedudb -c “EXPLAIN ANALYZE /*+ IndexScan(fgedu_users fgedu_users_name_idx) */ SELECT * FROM fgedu_users WHERE name = ‘test’;”
# 输出示例
Index Scan using fgedu_users_name_idx on fgedu_users (cost=0.29..4.30 rows=1 width=44) (actual time=0.015..0.015 rows=1 loops=1)
Index Cond: (name = ‘test’::text)
Planning Time: 0.035 ms
Execution Time: 0.018 ms
Part04-生产案例与实战讲解
4.1 执行计划分析案例
4.1.1 执行计划分析实战
# 1. 查看执行计划
$ psql -U fgedu -d fgedudb -c “EXPLAIN ANALYZE SELECT * FROM fgedu_orders WHERE user_id = 1 AND created_at > ‘2026-04-01’;”
# 输出示例
Seq Scan on fgedu_orders (cost=0.00..4.01 rows=10 width=28) (actual time=0.015..0.015 rows=10 loops=1)
Filter: ((user_id = 1) AND (created_at > ‘2026-04-01’::date))
Rows Removed by Filter: 990
Planning Time: 0.052 ms
Execution Time: 0.023 ms
# 2. 分析执行计划
– 节点类型：Seq Scan（顺序扫描），表示全表扫描
– 成本估算：cost=0.00..4.01，表示执行成本较低
– 行数估算：rows=10，表示预计返回10行
– 实际执行时间：actual time=0.015..0.015，表示执行时间较短
– 扫描行数：Rows Removed by Filter: 990，表示扫描了1000行，过滤了990行
# 3. 优化措施
– 创建复合索引
$ psql -U fgedu -d fgedudb -c “CREATE INDEX fgedu_orders_user_id_created_at_idx ON fgedu_orders(user_id, 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
4.2 查询重写案例
4.2.1 查询重写实战
# 1. 优化前的查询
SELECT
u.id, u.name, u.email,
(SELECT COUNT(*) FROM fgedu_orders o WHERE o.user_id = u.id) as order_count,
(SELECT SUM(amount) FROM fgedu_orders o WHERE o.user_id = u.id) as total_amount
FROM
fgedu_users u
WHERE
u.active = true
ORDER BY
total_amount DESC
LIMIT 10;
# 执行计划
$ psql -U fgedu -d fgedudb -c “EXPLAIN ANALYZE SELECT u.id, u.name, u.email, (SELECT COUNT(*) FROM fgedu_orders o WHERE o.user_id = u.id) as order_count, (SELECT SUM(amount) FROM fgedu_orders o WHERE o.user_id = u.id) as total_amount FROM fgedu_users u WHERE u.active = true ORDER BY total_amount DESC LIMIT 10;”
# 输出示例
Limit (cost=0.29..401.29 rows=10 width=56) (actual time=0.123..0.124 rows=10 loops=1)
-> Sort (cost=0.29..401.29 rows=100 width=56) (actual time=0.123..0.124 rows=10 loops=1)
Sort Key: ((SubPlan 2)) DESC
Sort Method: top-N heapsort Memory: 25kB
-> Seq Scan on fgedu_users u (cost=0.00..400.00 rows=100 width=56) (actual time=0.015..0.110 rows=100 loops=1)
Filter: active
Rows Removed by Filter: 900
SubPlan 1
-> Aggregate (cost=2.00..2.01 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=100)
-> Seq Scan on fgedu_orders o (cost=0.00..2.00 rows=10 width=0) (actual time=0.000..0.000 rows=10 loops=100)
Filter: (user_id = u.id)
SubPlan 2
-> Aggregate (cost=2.00..2.01 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=100)
-> Seq Scan on fgedu_orders o_1 (cost=0.00..2.00 rows=10 width=8) (actual time=0.000..0.000 rows=10 loops=100)
Filter: (user_id = u.id)
Planning Time: 0.123 ms
Execution Time: 0.145 ms
# 2. 优化后的查询
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;
# 执行计划
$ psql -U fgedu -d fgedudb -c “EXPLAIN ANALYZE 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;”
# 输出示例
Limit (cost=16.02..16.05 rows=10 width=56) (actual time=0.092..0.093 rows=10 loops=1)
-> Sort (cost=16.02..16.27 rows=100 width=56) (actual time=0.092..0.093 rows=10 loops=1)
Sort Key: (COALESCE(order_stats.total_amount, ‘0’::numeric)) DESC
Sort Method: top-N heapsort Memory: 25kB
-> Hash Left Join (cost=12.02..15.02 rows=100 width=56) (actual time=0.067..0.084 rows=100 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.110 ms
4.3 查询优化案例
4.3.1 复杂查询优化
# 1. 原始查询
SELECT
c.id, c.name, c.email,
COUNT(o.id) as order_count,
SUM(o.amount) as total_amount,
MAX(o.created_at) as last_order_date
FROM
fgedu_customers c
LEFT JOIN
fgedu_orders o ON c.id = o.customer_id
WHERE
c.active = true
AND o.created_at >= ‘2026-01-01’
GROUP BY
c.id, c.name, c.email
HAVING
COUNT(o.id) > 5
ORDER BY
total_amount DESC
LIMIT 20;
# 2. 优化措施
# 创建索引
$ psql -U fgedu -d fgedudb -c “CREATE INDEX fgedu_orders_customer_id_created_at_idx ON fgedu_orders(customer_id, created_at);”
$ psql -U fgedu -d fgedudb -c “CREATE INDEX fgedu_customers_active_idx ON fgedu_customers(id) WHERE active = true;”
# 3. 优化后的查询
SELECT
c.id, c.name, c.email,
order_stats.order_count,
order_stats.total_amount,
order_stats.last_order_date
FROM
fgedu_customers c
LEFT JOIN (SELECT
customer_id,
COUNT(*) as order_count,
SUM(amount) as total_amount,
MAX(created_at) as last_order_date
FROM
fgedu_orders
WHERE
created_at >= ‘2026-01-01’
GROUP BY
customer_id
HAVING
COUNT(*) > 5
) as order_stats ON c.id = order_stats.customer_id
WHERE
c.active = true
ORDER BY
order_stats.total_amount DESC
LIMIT 20;
# 4. 验证优化效果
$ psql -U fgedu -d fgedudb -c “EXPLAIN ANALYZE SELECT c.id, c.name, c.email, order_stats.order_count, order_stats.total_amount, order_stats.last_order_date FROM fgedu_customers c LEFT JOIN (SELECT customer_id, COUNT(*) as order_count, SUM(amount) as total_amount, MAX(created_at) as last_order_date FROM fgedu_orders WHERE created_at >= ‘2026-01-01’ GROUP BY customer_id HAVING COUNT(*) > 5) as order_stats ON c.id = order_stats.customer_id WHERE c.active = true ORDER BY order_stats.total_amount DESC LIMIT 20;”
# 输出示例
Limit (cost=28.03..28.08 rows=20 width=68) (actual time=0.156..0.157 rows=20 loops=1)
-> Sort (cost=28.03..28.28 rows=100 width=68) (actual time=0.156..0.157 rows=20 loops=1)
Sort Key: order_stats.total_amount DESC
Sort Method: top-N heapsort Memory: 27kB
-> Hash Left Join (cost=24.03..27.03 rows=100 width=68) (actual time=0.112..0.141 rows=100 loops=1)
Hash Cond: (c.id = order_stats.customer_id)
-> Seq Scan on fgedu_customers c (cost=0.00..4.01 rows=100 width=28) (actual time=0.012..0.017 rows=100 loops=1)
Filter: active
Rows Removed by Filter: 900
-> Hash (cost=20.02..20.02 rows=100 width=44) (actual time=0.092..0.092 rows=100 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 14kB
-> HashAggregate (cost=16.01..20.02 rows=100 width=44) (actual time=0.075..0.084 rows=100 loops=1)
Group Key: o.customer_id
Filter: (count(*) > 5)
Rows Removed by Filter: 0
-> Seq Scan on fgedu_orders o (cost=0.00..4.01 rows=1000 width=16) (actual time=0.007..0.010 rows=1000 loops=1)
Filter: (created_at >= ‘2026-01-01’::date)
Planning Time: 0.187 ms
Execution Time: 0.175 ms
Part05-风哥经验总结与分享
5.1 查询优化最佳实践
PostgreSQL查询优化的最佳实践：
- 分析执行计划：使用EXPLAIN ANALYZE分析查询的执行计划
- 创建合适的索引：根据查询模式创建合适的索引
- 优化查询语句：重写查询语句，减少不必要的操作
- 更新统计信息：定期运行ANALYZE更新表的统计信息
- 使用合适的连接方式：根据数据量选择合适的连接方式
- 限制结果集大小：使用LIMIT限制返回的行数
- 避免全表扫描：尽量使用索引扫描，避免全表扫描
- 监控查询性能：建立查询性能监控系统，及时发现问题
5.2 风哥经验分享
1. 执行计划是关键：通过分析执行计划，可以找出查询的性能瓶颈
2. 索引设计要合理：根据查询模式创建合适的索引，避免过度索引
3. 查询重写要谨慎：重写查询语句时，要确保语义一致
4. 统计信息要及时：定期更新表的统计信息，提高执行计划的准确性
5. 监控系统要完善：建立查询性能监控系统，及时发现性能问题
6. 优化要循序渐进：逐步优化查询，避免一次性大幅修改
通过合理的查询优化，可以显著提高系统的性能和稳定性，为业务提供更好的支持。from PostgreSQL视频:www.itpux.com
5.3 常见问题与解决方案
PostgreSQL查询优化常见问题与解决方案：
症状：查询执行全表扫描，性能缓慢
解决方案：
– 创建合适的索引
– 优化查询条件，使索引生效
– 考虑使用部分索引
# 常见问题2：嵌套循环连接效率低
症状：查询使用嵌套循环连接，执行时间长
解决方案：
– 增加内存参数，如work_mem
– 考虑使用Hash Join或Merge Join
– 优化连接条件
# 常见问题3：排序操作耗时
症状：查询包含大量排序操作，执行时间长
解决方案：
– 增加work_mem参数
– 优化排序条件
– 考虑使用索引避免排序
# 常见问题4：子查询性能差
症状：查询包含子查询，执行时间长
解决方案：
– 重写子查询为JOIN
– 考虑使用CTE（Common Table Expressions）
– 优化子查询条件
# 常见问题5：统计信息过时
症状：执行计划不准确，查询性能差
解决方案：
– 定期运行ANALYZE更新统计信息
– 考虑使用autoanalyze
– 手动分析大型表
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息
