代码之家 › 专栏 › 技术社区 › olivierr91

存在的地方很简单…ORDER BY…在PostrgeSQL中查询速度非常慢

partial-index postgres-9.6 sql-execution-plan entity-framework-core postgresql

2

olivierr91 · 技术社区 · 7 年前

我有一个非常简单的查询,由我的ORM(实体框架核心)生成:

SELECT *
 FROM "table1" AS "t1"
 WHERE EXISTS (
     SELECT 1
     FROM "table2" AS "t2"
     WHERE ("t2"."is_active" = TRUE) AND ("t1"."table2_id" = "t2"."id"))
 ORDER BY "t1"."table2_id"

有2条“is\U活动”记录。其他相关列(“id”)是主键。查询只返回4行。
此查询中涉及的3列被索引(is\u active、id、table2\u id)。
SET STATISTICS 10000
VACUUM FULL ANALYZE

ORDER BY 子句中,查询将在几毫秒内返回,并且我不希望返回4条记录。解释输出:

Nested Loop  (cost=1.13..13.42 rows=103961024 width=121)
  ->  Index Scan using table2_is_active_idx on table2  (cost=0.56..4.58 rows=1 width=8)
        Index Cond: (is_active = true)
        Filter: is_active
  ->  Index Scan using table1_table2_id_fkey on table1 t1 (cost=0.57..8.74 rows=10 width=121)
        Index Cond: (table2_id = table1.id)

和 订购人

Merge Semi Join  (cost=10.95..4822984.67 rows=103961040 width=121)
  Merge Cond: (t1.table2_id = t2.id)
  ->  Index Scan using table1_table2_id_fkey on table1 t1  (cost=0.57..4563070.61 rows=103961040 width=121)
  ->  Sort  (cost=4.59..4.59 rows=2 width=8)
        Sort Key: t2.id
        ->  Index Scan using table2_is_active_idx on table2 a  (cost=0.56..4.58 rows=2 width=8)
              Index Cond: (is_active = true)
              Filter: is_active

内部第一次索引扫描应返回不超过2行。然后,外部的第二个索引扫描没有任何意义,其成本为4563070和103961040行。它只需匹配中的2行 table2 table1 !

3 回复 | 直到 7 年前

1

3

olivierr91 7 年前

好的,我以最意想不到的方式解决了我的问题。我将Postgresql从9.6.1升级到了9.6.3。就这样。重新启动服务后,解释计划现在看起来很好,查询这次运行得很好。我没有改变任何东西,没有新的索引,什么也没有。我能想到的唯一解释是9.6.1中有一个查询规划器错误,在9.6.3中解决了。谢谢大家的回答!

2

3

Dima Pavlov 7 年前

添加索引:

CREATE INDEX _index 
ON table2 
USING btree (id) 
WHERE is_active IS TRUE;

SELECT table1.*
FROM table2
INNER JOIN table1 ON (table1.table2_id = table2.id)
WHERE table2.is_active IS TRUE 
ORDER BY table2.id

有必要以不同的方式考虑PostgreSQL的“is\u active is TRUE”和“is\u active=TRUE”过程。因此,索引谓词和查询中的表达式必须匹配。

如果无法重写查询,请尝试添加索引:

CREATE INDEX _index 
ON table2 
USING btree (id) 
WHERE is_active = TRUE;

3

2

Erwin Brandstetter 7 年前

你猜对了 Upgrading to the latest point-release is always the right thing to do.

Quoting the release notes for Postgres 9.6.2:

反连接以及继承案例(Tom Lane)

判断外键关系存在的新代码考虑到在这些情况下做了错误的事情,做出了估计不如9.6之前的代码。

Dima advised .但请保持简单:

is_active = TRUE 和 is_active IS TRUE subtly differ 第二个返回 FALSE 而不是 NULL 输入但在一个 WHERE 条款,其中仅 TRUE 合格。这两个表达式都是噪声。在Postgres中,您可以使用 boolean

CREATE INDEX t2_id_idx ON table2 (id) WHERE is_active;  -- that's all

然后做不用一个 LEFT JOIN table2 没有任何兄弟姐妹 table1 .为了符合您当前的逻辑,它必须是一个 [INNER] JOIN

SELECT t1.*
FROM   table2 t2
JOIN   table1 t1 ON t1.table2_id = t2.id  -- and no parentheses needed
WHERE  t2.is_active  -- that's all
ORDER  BY t1.table2_id;

但根本不需要以这种方式重写查询。这个 EXISTS 你的半连接也一样好。获得部分索引后,将生成相同的查询计划。

SELECT *
FROM   table1 t1
WHERE  EXISTS (
   SELECT 1 FROM table2
   WHERE  is_active  -- that's all
   WHERE  id = t1.table2_id
   )
ORDER  BY table2_id;

顺便说一句,因为你通过升级修复了这个bug,一旦你创建了部分索引(并运行 ANALYZE VACUUM ANALYZE 至少在桌子上一次,或者自动吸尘器为你做了一次),你会从不