PostgreSQL：使用主键作为排序键进行ORDER BY非常缓慢

Question

PostgreSQL：使用主键作为排序键进行ORDER BY非常缓慢

8

我有一个像这样的模型

enter image description here

使用以下表格大小：

+------------------+-------------+
| Table            |    Records  |
+------------------+-------------+
| JOB              |         8k  |
| DOCUMENT         |       150k  |
| TRANSLATION_UNIT |      14,5m  |
| TRANSLATION      |      18,3m  |
+------------------+-------------+

现在是以下查询

select translation.id
from "TRANSLATION" translation
   inner join "TRANSLATION_UNIT" unit
     on translation.fk_id_translation_unit = unit.id
   inner join "DOCUMENT" document
     on unit.fk_id_document = document.id     
where document.fk_id_job = 11698
order by translation.id asc
limit 50 offset 0

需要大约90秒才能完成。当我删除ORDER BY和LIMIT子句时，只需要19.5秒。在执行查询之前，ANALYZE已在所有表上运行。

对于这个特定的查询，满足条件的记录数量如下：

+------------------+-------------+
| Table            |     Records |
+------------------+-------------+
| JOB              |          1  |
| DOCUMENT         |       1200  |
| TRANSLATION_UNIT |    210,000  |
| TRANSLATION      |    210,000  |
+------------------+-------------+

查询计划：

enter image description here

没有ORDER BY和LIMIT的修改查询计划在这里。

数据库参数：

PostgreSQL 9.2

shared_buffers = 2048MB
effective_cache_size = 4096MB
work_mem = 32MB

Total memory: 32GB
CPU: Intel Xeon X3470 @ 2.93 GHz, 8MB cache

有人能看出这个查询有什么问题吗？

更新: 没有ORDER BY的相同查询的查询计划（但仍带有LIMIT子句）。

- twoflower

Postgre的优化器是如何工作的？例如，您可以从您的选择中选择，并按照您的方式排序，而不需要优化器参与吗？ - user1945782

只是猜测而已。你可以尝试将where子句移动到join中吗？在这种情况下，只需用“and”替换单词“where”。 - foibs

@foibs：这不会有任何影响。Postgres优化器足够聪明，可以检测到两个版本是相同的。 - user330315

@twoflower：你能否发布没有 order by 的执行计划（那个快的）？最好上传到http://explain.depesz.com，这样更易读。 - user330315

你最近运行过ANALYZE吗？链接的计划表明一些行数估计不准确。 - bma

显示剩余8条评论

3个回答

1

如果有人遇到相同的问题，我曾经遇到过这个问题，通过将索引更改为有序索引来解决它。索引扩展了列ID（PK列）和排序方向。

就像这样：

create index index_name on SCHEMA.TABLE (id asc, (sent_time IS NULL), some_id_ref, type);

- Bartek K

1

你是否已经在翻译表上(fk_id_translation_unit, id)建立了复合索引？在我看来，这将有助于避免通过表格访问translation.id的需求。

- David Aldridge

你是指将 fk_id_translation_unit 和 id 列组合成复合索引吗？我并没有这样做，但可以尝试一下。 - twoflower

我看到的好处是，为了检索所需的数据，不需要访问翻译表本身。 - David Aldridge

好的，确实需要这样做，因为我需要在结果集中包含 TRANSLATION.id。一个在 PostgreSQL 性能论坛上的人建议对数据库进行反规范化，并直接将 fk_id_job 添加到 TRANSLATION 中。 - twoflower

这应该可以从索引中访问，而无需访问表格。 - David Aldridge

我明白了。实际查询会从“TRANSLATION”中获取所有列。 - twoflower

啊，因此描述你试图解决的实际问题的重要性就在于此；然而，复合索引将允许排序在检索实际数据之前进行--如果这不发生，则查询可能被重写以促进概念优化器。你尝试过先添加该索引吗？ - David Aldridge

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Gordon Linoff · Accepted Answer

这段内容对于评论来说有点长了。当你移除 order by 子句时，你在比较苹果和橙子。没有 order by，查询的处理部分只需要生成 50 行。

有了 order by，所有行都需要在排序和选择前生成。如果你同时移除 order by 和 limit 子句，查询需要多长时间呢？

translation.id 是主键并不重要，因为处理需要通过几个连接（过滤结果）。

编辑：

我想知道使用 CTE 先创建表，然后再进行排序和获取结果会怎样：

with CTE as (
     select translation.id
     from "TRANSLATION" translation
          inner join "TRANSLATION_UNIT" unit
          on translation.fk_id_translation_unit = unit.id
          inner join "DOCUMENT" document
          on unit.fk_id_document = document.id     
     where document.fk_id_job = 11698
    )
select *
from CTE
order by translation.id asc
limit 50 offset 0;