sql - PostgreSql Select 查询性能问题

Question

我有一个简单的选择查询:

SELECT * FROM entities WHERE entity_type_id = 1 ORDER BY entity_id

然后我想得到前100个结果，所以我用这个:

SELECT * FROM entities WHERE entity_type_id = 1 ORDER BY entity_id LIMIT 100

问题是第二个查询比第一个查询慢得多。执行第一个查询需要不到一秒钟，而执行第二个查询需要一分多钟。

这些是查询的执行计划:

没有限制:

Sort  (cost=26201.43..26231.42 rows=11994 width=72)
  Sort Key: entity_id
  ->  Index Scan using entity_type_id_idx on entities  (cost=0.00..24895.34 rows=11994 width=72)
        Index Cond: (entity_type_id = 1)

有限制:

Limit  (cost=0.00..8134.39 rows=100 width=72)
  ->  Index Scan using xpkentities on entities  (cost=0.00..975638.85 rows=11994 width=72)
        Filter: (entity_type_id = 1)

我不明白为什么这两个计划如此不同以及为什么性能下降如此之多。我应该如何调整第二个查询以使其更快地运行？

我使用 PostgreSql 9.2。

Answer 1

您需要 100 个最小的 entity_id 匹配您的条件。现在 - 如果这些是数字 1..100 那么显然使用 entity_id 索引是处理这个问题的最佳方式 - 一切都是预先排序的。事实上，如果您想要的 100 在 1..200 范围内，那么它仍然有意义。大概 1..1000 会。

所以 - PostgreSQL 认为它会在表的“开始”找到很多 entity_type_id=1 值。它估计按类型过滤然后排序的成本为 8134 与 26231。在你的情况下这是错误的。

现在 - 要么存在一些不明显的相关性(这很糟糕 - 我们目前无法告诉规划者)，要么我们没有最新或足够的统计数据。

ANALYZE entities 有什么区别吗？您可以通过阅读 planner-stats page in the manuals 查看规划器知道哪些值.