sql - 在 PostgreSQL 中使用 LEFT JOIN 而不是 NOT IN

Question

我正在调试 django-reversion(一个 django 库)中可能存在的性能错误。我遇到的问题是，每次我运行 django-reversion 的 createinitialrevisions 时，我的数据库都会花费大量时间来处理正在发生的一切。

我在 RDS 中启用了 Performance Insights，我看到正在杀死我的数据库的查询如下所示:

SELECT "table_a"."id"
FROM "table_a"
WHERE NOT (CAST("table_a"."id" as text) IN (
        SELECT U0."object_id"
        FROM "reversion_version" U0
        WHERE (U0."content_type_id" = 49 AND U0."db" = 'default')
))

如果我没看错我在这里读到的内容 https://explainextended.com/2009/09/16/not-in-vs-not-exists-vs-left-join-is-null-postgresql/事实证明，PostgreSQL 无法以 NOT IN 与 LEFT JOIN 相同的方式进行优化。这就是为什么我决定重写此查询并查看它是否需要相同的运行时间。

这是改写后的结果:

SELECT "table_a"."id"
FROM "table_a"
LEFT JOIN 
        "reversion_version" U0
ON U0."object_id" = "table_a"."id"::text
WHERE U0."object_id" IS NULL AND U0."content_type_id" = 49 AND U0."db" = 'default'

我一定做错了什么，因为我得到了不同的结果。我的查询(重写的查询)根本没有返回任何内容。

我错过了什么？

Answer 1

正确重写的查询需要前一个子查询的 WHERE 条件作为 LEFT JOIN 的连接条件，例如:

SELECT table_a.id
FROM   table_a
LEFT   JOIN  reversion_version U0 ON U0.object_id = table_a.id::text
                                 AND U0.content_type_id = 49
                                 AND U0.db = 'default'
WHERE  U0.object_id IS NULL;

您尝试的方式在逻辑上是矛盾的:它会在 table_a 中请求行，而在 reversion_version 中没有匹配的行，然后然后强加额外的不存在的行的条件。这永远不会返回任何行。

必须反过来:在 table_a 中查找行，而在 reversion_version 中没有匹配的行可以满足上述条件。因此，将这些条件从 WHERE 子句移动到 LEFT JOIN 的连接子句。细微但根本的区别。

参见:

• SQL / PostgreSQL left join ignores "on = constant" predicate, on left table
• Explain JOIN vs. LEFT JOIN and WHERE condition performance suggestion in more detail
• Select rows which are not present in other table

关于性能可能还有更多要说的，但并非没有必要的设置细节......