sql - 使用 order by 时在大表上查询速度慢

Question

我有一张包含约 3000 万个元组的表。该表如下所示:

id | first_name | last_name | email
-----------------------------------------
1  | foo        | bar       | foo@bar.com

还有一个索引(btree 索引)用于 first_name 和其他用于 last_name。

下面的查询需要大约 200 毫秒才能返回结果:

SELECT  
  * 
FROM my_table 
 WHERE (first_name ILIKE 'a%') 
 LIMIT 10 OFFSET 0

但是下一个用了大概15秒(adding order by)

SELECT  
  * 
FROM my_table 
 WHERE (first_name ILIKE 'a%') 
 ORDER BY last_name asc, first_name asc 
 LIMIT 10 OFFSET 0 

我可以做些什么来提高上次查询的性能？

Answer 1

对于此查询，您有两种索引选择:

SELECT t.* 
FROM my_table 
WHERE first_name ILIKE 'a%'
ORDER BY last_name asc, first_name asc 
LIMIT 10 OFFSET 0 ;

一个用于 WHERE 子句。最好的索引是 my_table(first_name)。第二种可能性是为 ORDER BY 使用索引，my_table(last_name, first_name)。

哪个更好取决于您拥有的数据。如果以整体性能为目标，您可能想尝试两者看看哪个效果更好。

最后，计算索引可能是最好的方法。对于您的情况，将查询编写为:

SELECT t.* 
FROM my_table 
WHERE lower(substr(first_name, 1, 1)) = 'a'
ORDER BY last_name asc, first_name asc 
LIMIT 10 OFFSET 0 ;

然后，您想要的索引是 mytable(lower(substr(first_name, 1, 1)), last_name, first_name)。此索引可用于 WHERE 和 ORDER BY，这对于此查询应该是最佳的。