当设置了限制时,Postgres会变慢:除了添加一个伪“ORDER BY”之外,如何修复?

在Postgres中,一些查询在添加 LIMIT

询问:

SELECT * FROM review WHERE clicker_id=28 ORDER BY done DESC LIMIT 4; -- 51 sec
SELECT * FROM review WHERE clicker_id=28 ORDER BY id, done DESC LIMIT 4; -- 0.020s
SELECT * FROM review WHERE clicker_id=28 LIMIT 4; -- 0.007s
SELECT * FROM review WHERE clicker_id=28 ORDER BY id; -- 0.007s

如您所见,我需要在 ORDER BY

跑步 EXPLAIN 在他们身上:

EXPLAIN SELECT * FROM review WHERE clicker_id=28 ORDER BY done DESC LIMIT 4;
EXPLAIN SELECT * FROM review WHERE clicker_id=28 ORDER BY id, done DESC LIMIT 4;
EXPLAIN SELECT * FROM review WHERE clicker_id=28 LIMIT 4;
EXPLAIN SELECT * FROM review WHERE clicker_id=28 ORDER BY id;

给出:

EXPLAIN SELECT * FROM review WHERE clicker_id=28 ORDER BY done DESC LIMIT 4
Limit  (cost=0.44..249.76 rows=4 width=56)
  ->  Index Scan using review_done on review  (cost=0.44..913081.13 rows=14649 width=56)
        Filter: (clicker_id = 28)


EXPLAIN SELECT * FROM review WHERE clicker_id=28 ORDER BY id, done DESC LIMIT 4
Limit  (cost=11970.75..11970.76 rows=4 width=56)
  ->  Sort  (cost=11970.75..12007.37 rows=14649 width=56)
        Sort Key: id, done DESC
        ->  Index Scan using review_clicker_id on review  (cost=0.44..11751.01 rows=14649 width=56)
              Index Cond: (clicker_id = 28)


EXPLAIN SELECT * FROM review WHERE clicker_id=28 LIMIT 4
Limit  (cost=0.44..3.65 rows=4 width=56)
  ->  Index Scan using review_clicker_id on review  (cost=0.44..11751.01 rows=14649 width=56)
        Index Cond: (clicker_id = 28)


EXPLAIN SELECT * FROM review WHERE clicker_id=28 ORDER BY id
Sort  (cost=12764.61..12801.24 rows=14649 width=56)
  Sort Key: id
  ->  Index Scan using review_clicker_id on review  (cost=0.44..11751.01 rows=14649 width=56)
        Index Cond: (clicker_id = 28)

数据库:

• 这个 review
  • 包含2200多万行。
    • 一个给定的用户最多可以得到7066行。
    • 测试中的那个(id 28)当时有288个。
  • 具有以下结构:
    • id:bigint自动递增[nextval('public.review_id_seq')]
    • 类型:审查类型为空
    • 迭代:smallint NULL
    • 到期:timestamptz空
    • 完成:timestamptz NULL
    • clicker_id:bigint空
    • 独白:bigint空
  • • 独特类型,点击者id,独白id,迭代
    • 索引单击器id
    • 索引完成,到期,独白
    • 索引id
    • 索引完成描述

其他详细信息:

环境:

• 将查询运行到生产环境(Heroku Postgres,9.6.16版)中,差异不大,但仍然不大:缓慢的查询可能需要600毫秒。

变速:

• 有时,相同的查询(无论是完全相同的,还是不同的点击器id)运行得更快(不到1秒),但我不明白为什么。我需要他们 一贯地 快。
• 如果我用 LIMIT 288

在我想到使用假人之前 订货人

• 重新导入数据库。
• analyze review;
• 为done to DESC设置索引(以前设置为default/ASC.)[当时的挑战是 no proper way to check if/when the index is done rebuilding

没有帮助。

我的问题本身已经解决了,但我不满意:

• 有没有 此“模式”的名称 订货人
• 我怎么能发现 将来会有这样的问题吗?(这花了很长时间才想出来。)除非我漏掉了什么,否则 解释 不是很有用:
• 备选方案: 还有别的办法处理吗? (因为这个解决方案感觉像是一个黑客。)

类似问题:

• PostgreSQL query very slow with limit 1