面试题：PostgreSQL查询性能瓶颈之索引相关分析

Question

在PostgreSQL中，假设你有一个包含百万条记录的表，表结构为`employees (id serial primary key, name varchar(100), department varchar(50), salary numeric(10, 2))`，查询语句为`SELECT * FROM employees WHERE department = 'HR' AND salary > 50000;`。现在查询性能不佳，你如何通过分析索引来定位和解决性能瓶颈？

Answer 1

1. 分析现有索引：
   • 使用EXPLAIN命令分析查询计划，例如：EXPLAIN SELECT * FROM employees WHERE department = 'HR' AND salary > 50000;。EXPLAIN会展示PostgreSQL如何执行查询，包括是否使用了索引以及扫描的方式等。
   • 查看是否有适合的索引被使用。对于department和salary列，如果没有索引，可能会导致全表扫描，从而性能不佳。
2. 创建复合索引：
   • 根据查询条件，可以考虑创建一个复合索引。语法如下：

   CREATE INDEX idx_employees_department_salary ON employees (department, salary);
   

   • 复合索引中列的顺序很重要。在这个例子中，将department放在前面，因为查询首先筛选出department = 'HR'的记录，然后再筛选salary > 50000的记录。如果查询条件顺序经常变化，可以考虑创建多个复合索引，但要注意过多索引会增加写入成本。
3. 重新分析查询：
   • 创建索引后，再次使用EXPLAIN命令分析查询计划：EXPLAIN SELECT * FROM employees WHERE department = 'HR' AND salary > 50000;。
   • 确认查询计划是否使用了新创建的索引，并且扫描方式是否从全表扫描变为索引扫描，若变为索引扫描，性能应该会有显著提升。
4. 索引维护：
   • 随着数据的插入、更新和删除，索引可能会碎片化。定期使用VACUUM命令对表和索引进行维护，例如：VACUUM ANALYZE employees;。VACUUM可以回收空间并更新统计信息，有助于查询优化器生成更优的查询计划。