简单的存在的地方..订购方式..在PostrgeSQL中查询非常慢

Question

我有一个非常简单的查询，由我的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"

1. 有 2 条"is_active"记录。其他涉及的列("id"(是主键。查询正好返回 4 行。
2. 表 1 是 9600 万条记录。
3. 表 2 是 3000 万条记录。
4. 此查询中涉及的 3 列已编制索引(is_active、id、table2_id(。
5. 生成此简单查询的 C#/LINQ 代码是：Table2.Where(t => t.IsActive(。包括(t => t.表1(。ToList((;'
6. SET STATISTICS 10000设置为所有 3 列。
7. 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)

使用ORDER BY子句，查询需要 5 分钟才能完成！解释输出：

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与 4 行匹配table1！

这是一个非常简单的查询，要返回的记录很少。为什么Postgres无法正确执行它？

Answer 1

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

Answer 2

添加索引：

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_active是真的"和"is_active=真"。因此，索引谓词和查询中的表达式必须匹配。

如果无法重写查询，请尝试添加索引：

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

Answer 3

你的猜测是对的，Postgres 9.6.1 中有一个错误完全适合你的用例。升级是正确的做法。升级到最新的单点版本始终是正确的做法。

引用 Postgres 9.6.2 的发行说明：

• 修复半连接和 反合并以及继承案件(汤姆·莱恩(

  用于获取外键关系存在的新代码 考虑到在这些情况下做了错误的事情，做出了估计 比 9.6 之前的代码更糟糕。

您仍应按照 Dima 的建议创建该部分索引。但请保持简单：

is_active = TRUE和is_active IS TRUE的细微区别在于，第二个返回FALSE而不是NULLNULL输入。但在只有TRUE符合条件的WHERE条款中，这些都不重要。这两种表达都只是噪音。在 Postgres 中，您可以直接使用boolean值：

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

并且不要用LEFT JOIN重写您的查询。这会将包含 NULL 值的行添加到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;

顺便说一句，由于您通过升级修复了错误，并且一旦创建了该部分索引(并在表上运行ANALYZE或VACUUM ANALYZE至少一次 - 或者自动真空为您做了一次(，您将再也不会为此获得糟糕的查询计划，因为 Postgres 维护部分索引的单独估计，这对您的数字是明确的。详：

从

• 给定条件下从 pg_class.reltuples 获取计数估计值
• 未使用但影响查询的索引