面试题：PostgreSQL执行计划相关配置项调优之中等难度题

Question

在PostgreSQL中，简述`shared_buffers`配置项对执行计划的影响以及通常该如何调整？

Answer 1

shared_buffers对执行计划的影响

1. 数据缓存：shared_buffers是PostgreSQL用于缓存数据库页面（包括表数据、索引等）的内存区域。如果shared_buffers设置过小，数据库可能频繁从磁盘读取数据，使得执行计划在成本估算时，会将磁盘I/O成本考虑在内，导致选择更偏向于减少I/O的执行路径。例如，可能会优先选择使用索引扫描而非全表扫描，即使全表扫描在理论上数据量较小时更高效，但因磁盘I/O成本高而不被选择。
2. 查询优化：当shared_buffers足够大，许多常用数据和索引可以常驻内存，执行计划器在估算成本时，会将内存访问成本视为较低值。这可能会改变执行计划的选择，比如对于一些数据量适中的表，可能会选择全表扫描，因为内存访问速度快，全表扫描的成本在这种情况下可能低于索引扫描（索引扫描还涉及额外的索引查找开销）。

通常的调整方法

1. 初始设置：一般建议shared_buffers设置为系统总内存的25%左右，但这只是一个经验值，具体设置需根据服务器的实际用途和硬件情况调整。例如，对于专门运行PostgreSQL的服务器，可适当提高该比例；若服务器还运行其他应用程序，则需合理分配内存，降低此比例。
2. 监控与调整：通过监控工具（如pg_stat_activity视图查看查询活动，pg_stat_statements扩展查看SQL语句统计信息）观察查询性能。如果发现频繁的磁盘I/O（可通过系统监控工具如iostat确认），且数据库性能瓶颈明显，可适当增加shared_buffers大小。每次调整后，重新测试性能相关的关键查询，观察执行计划和响应时间的变化，逐步找到最优配置。同时，注意增加shared_buffers可能会影响操作系统和其他应用程序的可用内存，需平衡考虑系统整体性能。