postgresql - 为什么在查询结果中查询会产生重复记录

Question

我无法理解这一点。

SELECT COUNT(*) FROM profiles
WHERE profiles.status IN ('abc', 'man')
  AND profiles.id IN (
                        SELECT artifacts.item_id FROM artifacts
                        WHERE artifacts.deleted_at IS NULL
                          AND artifacts.item_type = 'Profile'
                          AND artifacts.upload_type = 'bill'
                     );
 count 
-------
 12514
(1 row)

以上查询计算配置文件的重复记录(工件有多个记录)。当我使用 distinct 运行上述查询时，我得到了下面的正确计数。

SELECT COUNT(DISTINCT(id)) FROM profiles
WHERE profiles.status IN ('abc', 'man')
  AND profiles.id IN (
                        SELECT artifacts.item_id FROM artifacts
                        WHERE artifacts.deleted_at IS NULL
                          AND artifacts.item_type = 'Profile'
                          AND artifacts.upload_type = 'bill'
                     );
 count 
-------
 12157
(1 row)

对于同一配置文件，工件可以有多个记录。但根据我的理解，IN 查询不会让任何重复的配置文件计算在内。我对吗？或者有什么我想念的吗？

更新:

我试图将查询减少到 2 个不同的过滤条件。两种情况都可以正常工作。请在下面找到。

=> SELECT COUNT(*) FROM profiles WHERE profiles.id IN (
            SELECT artifacts.item_id FROM artifacts 
            WHERE artifacts.deleted_at IS NULL 
            AND artifacts.item_type = 'Profile' 
            AND artifacts.upload_type = 'bill');
 count 
-------
 22664
(1 row)

=> SELECT COUNT(DISTINCT(id)) FROM profiles WHERE profiles.id IN (
            SELECT artifacts.item_id FROM artifacts 
            WHERE artifacts.deleted_at IS NULL 
            AND artifacts.item_type = 'Profile' 
            AND artifacts.upload_type = 'bill');
 count 
-------
 22664
(1 row)


=> SELECT COUNT(DISTINCT(id)) FROM profiles 
        WHERE profiles.status IN ('abc', 'man');
 count 
-------
 20109
(1 row)

=> SELECT COUNT(*) FROM profiles 
        WHERE profiles.status IN ('abc', 'man');
 count 
-------
 20109

因此，当两个 IN 查询结合使用时，就会出现重复。有没有人熟悉这种用例。

Answer 1

有两种可能:

1. id 在profiles 中不是唯一的。

   您可以运行以下查询来调查此问题:

   SELECT profiles.id, count(*) FROM profiles
   WHERE profiles.status IN ('abc', 'man')
     AND profiles.id IN (
                           SELECT artifacts.item_id FROM artifacts
                           WHERE artifacts.deleted_at IS NULL
                             AND artifacts.item_type = 'Profile'
                             AND artifacts.upload_type = 'bill'
                        )
   GROUP BY profiles.id
   HAVING count(*) > 1;
   

   这将返回重复的 id。

   您是否缺少对该列的 UNIQUE 或 PRIMARY KEY 约束？

2. 如果id 上有UNIQUE 或PRIMARY KEY 约束，您将面临数据损坏。查看查询计划 - 它使用索引扫描还是顺序扫描？

   如果将 enable_indexscan、enable_bitmapscan 和 enable_indexonlyscan 设置为 off 可以解决问题，则索引已损坏。 REINDEX TABLE 配置文件 可能会解决这个问题。

   如果仅使用顺序扫描，查询也返回错误的结果，那么您将面临表损坏。从上次良好的备份恢复。

   无论如何，如果是数据损坏，找到它的原因并修复它。它可能是有缺陷的 RAM 或存储，或者存储上的服务器崩溃，无法正确响应同步请求。阅读数据库日志!