简单WHERE EXISTS ... ORDER BY ...查询在PostrgeSQL

问题描述：

很慢我有这个非常简单的查询，通过我的ORM（实体框架核心）产生：简单WHERE EXISTS ... ORDER BY ...查询在PostrgeSQL

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_ACTIVE “ 记录。其他涉及的列（“id”）是主键。查询返回正好4行。
表1是9600万条记录。
表2是3000万条记录。
索引涉及此查询中的3列（is_active，id，table2_id）。生成此简单的查询
的C＃/ LINQ代码是：Table2.Where（T => t.IsActive）.INCLUDE（T => t.Table1）.ToList（）;`
SET STATISTICS 10000设定为所有3列。
VACUUM FULL ANALYZE在两个表上运行。

没有ORDER BY子句，查询在几毫秒内返回，我期望没有其他4个记录返回。 EXPLAIN输出：

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)

WITH的 ORDER BY条款

，查询需要5分钟就可以完成！ EXPLAIN输出：

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行的成本没有任何意义。它只需要在table2中匹配2行，在table1中匹配4行！

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

您需要包括的解释计划。 –

答

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

最可能没有错误。查询计划员非常复杂，并不总是做出正确的决定。无法保证您的查询在几周内或在另一次升级后仍能继续正常运行。 –

@JakubKania：一般都是如此。但碰巧9.6.1中有一个bug符合情况。而对于清晰的价值频率，Postgres将在错误修复和部分索引就位之后再也不会做出这样错误的决定。 –

答

添加一个索引：

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

既要考虑到 “IS_ACTIVE是真实的” 和 “IS_ACTIVE = TRUE” 在PostgreSQL过程不同的方式。因此索引谓词和查询中的表达式必须匹配。

如果你无法改写查询尝试添加索引：

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

谢谢你的回答，这是一个好主意，我不知道我们可以做这样的部分索引。我的问题是通过将Postgres升级到最新的小版本解决的，这是我认为可以修复它的最后一件事。 – sixtstorm1

有趣的是，你可以更新新的解释计划吗？ –

答

你的猜测是正确的，有在Postgres的9.6.1恰好适合您的使用情况下的错误。升级是正确的。Upgrading to the latest point-release is always the right thing to do.

Quoting the release notes for Postgres 9.6.2:外键为主

修复加盟的半连接和抗加入，以及继承案件（汤姆巷）选择性估计

新的代码考虑到外部关键关系的存在在这些情况下做了错误的事情，使得估计比9.6之前的代码差。

您仍然应该创建一个像Dima advised那部分索引。但保持它的简单：

is_active = TRUE和is_active IS TRUEsubtly differ在第二个返回FALSE而不是NULL为NULL输入。但是，在WHERE子句中，其中只有TRUE才合格。而这两种表情都只是噪音。在Postgres里，你可以直接使用boolean值：

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

，做不与LEFT JOIN重写查询。这将在table2中为“活动”行的结果添加由NULL值组成的行，在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至少一次 - 或自动清理做到了你），你会永远再次得到一个糟糕的查询计划，因为Postgres对部分索引维护了单独的估计值，这些估计值对于您的数字是毫不含糊的。详细信息：

简单WHERE EXISTS ... ORDER BY ...查询在PostrgeSQL

相关推荐