索引能提高排序性能吗？

Question

我有一个相当复杂的查询，其中包括一个ORDER BY和一个LIMIT子句。当ORDER BY使用主键时，查询只需要不到5毫秒的时间。然而，如果我改变查询，使ORDER BY由不同类型（FLOAT）的列进行排序，则响应时间会增加到50秒以上（高出四个数量级！）。

现在，我认为问题在于按主键排序的查询执行索引扫描，而按浮点列排序的查询执行顺序扫描并需要在最后进行排序。

我认为只需在浮点列上添加索引即可让Postgresql以更智能的方式规划此查询。但显然我错了。我可能漏掉了什么？

编辑：在发布问题之前，我确实运行了EXPLAIN ANALYZE。因此，我的推断不仅仅是一种猜测；但是，由于EXPLAIN ANALYZE的输出超过30行，不清楚为什么一个查询使用索引而另一个查询必须对所有行进行排序。

- Jon Smark

---

浮点数比较比整数比较要慢得多。我认为你的PK可能是一个误导。 - KingCronus

为什么要假设，当你可以运行EXPLAIN ANALYZE时呢？ - Daniel Vérité

@KingCronus . . . 你能否记录下浮点数比较会比整数比较慢3600倍的地方？我从未经历过这种程度的性能下降。 - Gordon Linoff

@GordonLinoff 这是一个很好的观点。我们这里大概有多少行数据？ - KingCronus

请通过 http://explain.depesz.com/ 共享 EXPLAIN (ANALYZE, BUFFERS)。 - vyegorov

Answer 1

1. 在查询上运行explain analyze - 这样你就不必猜测发生了什么。
2. 要优化查询，通常需要阅读explain analyze输出、查询语句，然后找出最佳操作方式。有时候是添加索引，有时候是重写查询语句。但无法确定哪种方式对你的情况最好，因为我们没有看到explain或query。

- user80168

Answer 2

很难在没有查询语句的情况下解释发生了什么。我猜测查询计划能够基于具有主键的表进行连接，保持数据的正确顺序。然后，查询计划基本上是获取一行数据，查找其他表中的值，对其进行处理，并按顺序返回这些值。处理的范围取决于limit的设置。

当您将此替换为order by中的另一列时，所有行都必须被处理。这些行会被排序并返回。这可能是底层表的大小或结果集的大小导致处理时间更长的原因。但根本原因是需要生成所有行。

- Gordon Linoff

Answer 3

对于返回许多行的查询，数据库使用非覆盖索引是不寻常的。从索引到表数据的表查找成本太高了。相反，将使用表扫描。

例如，

select name from people where name > 'N' order by birthdate

数据库会使用 (birthday) 索引吗？优点是行将按正确顺序返回。缺点是每一行都需要进行 name 列的表查找，这样会更加昂贵，因此索引不会被使用。

(birthday, name) 上的索引是不同的。它包括名称，因此不需要表查找。数据库可以使用索引快速返回正确顺序的行。

包含查询所需的所有列的索引称为覆盖索引。确保您的索引包括查询使用的所有列，然后再试一次。

- Andomar

---

Postgres没有聚集索引，自9.2版本以来只能使用索引扫描（“覆盖索引”）。 - user330315

@a_horse_with_no_name：没错，Postgres集群化似乎是一次性的，因此它有助于磁盘读取，但不能保证排序 http://www.postgresql.org/docs/current/static/sql-cluster.html - Andomar

那不是“聚集索引”。它只是根据索引重新组织表。而且这不会自动维护。 - user330315

此外：如果一个查询返回了大量行（相对于表中的总行数），实际上很常见根本不使用索引（例如，在Postgres中进行“序列扫描”，在Oracle中进行“表扫描”）。 - user330315

@a_horse_with_no_name：这正是我在这个答案中试图表达的确切观点。实际上，这并不是很多行。对于仅有100行的数据，SQL Server更喜欢使用表扫描而不是索引查找和查找。 （在SQL Server中，表扫描称为“聚集索引扫描”）。 - Andomar