在 postgres 中调整子查询

Question

我在数据库中发现了一些可疑数据。我试图确定某个字段（姓氏）是否正确。我在 postgres 中提出了以下查询：

SELECT members."memberID", 
       members.lastname 
  FROM members 
 WHERE members."memberID" NOT IN (SELECT members."memberID" 
                                    FROM members 
                                   WHERE members.lastname ~* '[a-zA-z]+([-][a-zA-Z]+)*');

子查询当前与普通名称和带有连字符的名称匹配。父查询应显示与该模式不匹配的成员。目前，该查询需要花费大量时间来运行（我从未见过它完成）。我不确定为什么需要这么长时间或如何改进它。

Answer 1

不存在

SELECT m."memberID", 
       m.lastname 
  FROM MEMBERS m 
 WHERE NOT EXISTS (SELECT NULL
                     FROM MEMBERS b
                    WHERE b.lastname ~* '[a-zA-z]+([-][a-zA-Z]+)*'
                      AND b."memberID" = m."memberID");

左连接/为空

   SELECT m."memberID", 
          m.lastname 
     FROM MEMBERS m 
LEFT JOIN MEMBERS b ON b."memberID" = m."memberID"
                   AND b.lastname ~* '[a-zA-z]+([-][a-zA-Z]+)*'
    WHERE b."memberID" IS NULL

摘要

引用：

PostgreSQL 同等对待 LEFT JOIN 和 NOT EXISTS，对它们使用相同的执行计划（即上面示例中的哈希反连接）。

至于 NOT IN，它在语义上是不同的，因为它的逻辑是三价的并且可以返回 NULL，PostgreSQL 尝试考虑到这一点，并限制自己使用针对子计划（散列的过滤器）的过滤器。可散列结果集的子计划，如上面的示例）。

由于每个缺失值需要在哈希表中搜索两次（第一次查找该值，第二次查找 NULL），因此这种方法效率稍低。

优化器可以在任何时候确定列表无法放入内存时使用普通子计划，效率非常低，并且应该像避免瘟疫一样避免可能使用它的查询。

这就是为什么在 PostgreSQL 8.4 中应该始终使用 LEFT JOIN / IS NULL 或 NOT EXISTS 而不是 NOT IN 来查找缺失值.

附录

但正如 Andrew Lazarus 指出的那样，如果 MEMBERS 表中没有重复的 memberid，则查询只需：

SELECT m."memberID", 
       m.lastname 
  FROM MEMBERS m 
 WHERE b.lastname ~* '[a-zA-z]+([-][a-zA-Z]+)*'

Answer 2

我喜欢 OMG Ponies 的答案，但是如果 memberID 是唯一的（即 PK），您可以完全删除子查询。

SELECT members."memberID", 
       members.lastname 
  FROM members 
 WHERE members.lastname !~ '[a-zA-Z]+([-][a-zA-Z]+)*';

（我删除了不区分大小写的运算符，因为正则表达式涵盖了这两种情况。）