面试题：PostgreSQL执行计划基础与简单I/O优化

Question

请描述如何在PostgreSQL中获取一条SQL语句的执行计划？如果发现执行计划中扫描全表导致磁盘I/O过高，有哪些常见的优化手段？

Answer 1

获取SQL语句执行计划

在PostgreSQL中，可以使用 EXPLAIN 关键字来获取一条SQL语句的执行计划。语法如下：

EXPLAIN your_sql_statement;

如果想要获取更详细的执行计划信息，包括实际执行时间、返回的行数等，可以使用 EXPLAIN ANALYZE：

EXPLAIN ANALYZE your_sql_statement;

优化全表扫描导致磁盘I/O过高的常见手段

1. 添加合适的索引
   • 如果查询条件中有某个列或列组合，可以为这些列创建索引。例如，如果查询 SELECT * FROM your_table WHERE column1 = 'value';，可以创建 CREATE INDEX idx_column1 ON your_table (column1);
   • 复合索引适用于多个列的联合查询条件，如 SELECT * FROM your_table WHERE column1 = 'value1' AND column2 = 'value2';，创建 CREATE INDEX idx_column1_column2 ON your_table (column1, column2);
2. 分区表
   • 对于非常大的表，可以根据某个列（如时间列）进行分区。例如，按月份对销售记录表进行分区，每个月的数据存放在不同的分区中。这样在查询特定时间段的数据时，只需要扫描相关的分区，而不是全表。
   • 创建分区表需要一定的规划和操作步骤，如定义分区键、创建分区表结构等。
3. 查询重写
   • 优化SQL语句的逻辑，避免不必要的子查询或复杂的连接。例如，将子查询改写为连接查询，有时候可以获得更好的执行计划。
   • 确保查询条件的写法是最有效的，例如避免在查询条件中对列使用函数，因为这可能会导致索引失效。
4. 调整数据库参数
   • 适当调整 shared_buffers 参数，增加数据库缓存数据的能力，减少磁盘I/O。该参数决定了PostgreSQL用于缓存数据库页面的内存量。
   • 调整 work_mem 参数，该参数影响排序操作和哈希表构建等操作时使用的内存量。如果设置过小，可能会导致这些操作频繁使用磁盘临时文件，增加I/O。