面试题：PostgreSQL执行计划之索引使用分析

Question

在PostgreSQL中，给定一张表'test_table'，包含字段'id'（主键），'name'（varchar类型），'age'（int类型）。有一条查询语句：SELECT * FROM test_table WHERE age > 30 AND name LIKE 'John%'。请分析这条查询可能使用的索引情况，并说明如何查看其实际执行计划以验证你的分析。

Answer 1

1. 索引情况分析：
   • 对于条件age > 30，如果在age字段上有索引，例如CREATE INDEX idx_age ON test_table (age);，PostgreSQL 可能会使用这个索引来快速定位满足age > 30条件的行。不过，由于是>比较，索引扫描可能无法充分利用索引的有序性，可能会进行部分索引扫描。
   • 对于条件name LIKE 'John%'，如果在name字段上有索引，例如CREATE INDEX idx_name ON test_table (name);，PostgreSQL 可以使用索引快速定位以John开头的行。这种前缀匹配（LIKE 'John%'）的情况能较好地利用索引。
   • 如果在(age, name)上有联合索引CREATE INDEX idx_age_name ON test_table (age, name);，PostgreSQL 可能会先利用索引筛选出age > 30的行，然后在这些行中再筛选出name LIKE 'John%'的行。
2. 查看实际执行计划： 在 PostgreSQL 中，可以使用EXPLAIN关键字来查看查询的执行计划。执行以下语句：

   EXPLAIN SELECT * FROM test_table WHERE age > 30 AND name LIKE 'John%';
   

   EXPLAIN会返回查询优化器计划如何执行查询的详细信息，包括是否使用了索引，使用的是哪些索引，以及表扫描的方式等。如果想要更详细的执行计划信息，例如实际执行的时间等，可以使用EXPLAIN ANALYZE：

   EXPLAIN ANALYZE SELECT * FROM test_table WHERE age > 30 AND name LIKE 'John%';
   

   这会实际执行查询，并返回带有实际执行时间等详细信息的执行计划。