java - 使用 postgres 索引选择从 offset 到 limit 的所有记录

Question

我想从大约 40 列和 1.000.000 行的表中获取从偏移量到限制的所有数据。我尝试通过 postgres 索引 id 列，并通过 java 和实体管理器获取选择查询的结果。

我的查询大约需要 1 分钟才能得到结果，这有点太长了。我尝试使用不同的索引，并将查询限制为 100，但这次仍然需要。我该如何修复它？我需要更好的索引还是我的代码有问题？

CriteriaQuery<T> q = entityManager.getCriteriaBuilder().createQuery(Entity.class);
TypedQuery<T> query = entityManager.createQuery(q);
List<T> entities = query.setFirstResult(offset).setMaxResults(limit).getResultList();

Answer 1

现在您可能根本不使用该索引。 hibernate 限制/偏移量如何转换为数据库操作存在一些模糊性(对于 postgres，请参阅 this comment)。这可能意味着开销，如对 this post 的回复中详细描述的那样。 .

如果偏移量和限制与 id 列的值有直接关系，则可以在表单查询中使用它

SELECT e
FROM Entity
WHERE id >= offset and id < offset + limit

鉴于请求的记录数明显小于表中的记录总数，数据库将使用索引。

接下来的事情是，40 列已经相当多了。如果您实际上需要的数量要少得多，您可以定义一个仅包含所需属性的受限实体，并查询该实体。这应该会节省一些更多的开销。

如果您仍然达不到性能要求，您可以选择采用 jdbc 连接/查询，而不是使用 hibernate。

顺便说一句。您可以记录 jpa/hibernate 发出的实际 sql 并使用它来获取 execution plan从 postgress 中，这将向您显示查询的实际情况以及是否会使用索引。此外，您还可以监视数据库的查询执行时间，以了解哪一部分处理时间是由数据库消耗的，哪一部分是由 Java 客户端加上数据传输开销消耗的。