postgresql - 消除从 postgresql 上的 JOIN SQL 查询返回的重复记录

Question

我有一个查询，它跨 4 个表连接数据以提供基于测试结果的数据。查询工作正常，除了它为每行唯一数据返回两个相同的记录。如果我在连接中的一个表的主键列 (a.id) 前面抛出一个 DISTINCT，这将消除所有重复项。但是，我已经阅读(并发现)DISTINCT 往往会影响性能，因此我希望尽可能找到性能更好的解决方案。希望我只是用我的 JOINS 做了一些很容易修复的愚蠢的事情。这是在 postgresql-9.0.x 上，是的，我知道如果我升级到 9.1.x，那么我可能会做一个“group by a.id”，但现在我被困在 9.0.x 上。

这是查询:

SELECT a.id,a.suiteid,a.testname
     ,date_trunc('second',a.last_update) AS last_update
     ,regexp_replace(p.relname,E'tests','','g')
     ,o.osname 
FROM smoketests AS a
   , pg_class AS p
   , smoke AS t
   , osversmap AS o 
WHERE a.osversion=o.osversion
  AND a.suiteid=t.id
  AND a.tableoid=p.oid
  AND ( a.current_status='FAILED' )
  AND ( a.arch='i386' )
  AND ( a.os='Darwin' )
  AND a.last_update>'2012-05-01 04:00:00'
  AND a.last_update<'2012-05-02 14:20:45' 
ORDER BY a.id ;

产生这个输出:

    id    | suiteid |     testname     |     last_update     | regexp_replace |   osname   
----------+---------+------------------+---------------------+----------------+------------
 32549818 |  668232 | bug377064        | 2012-05-01 08:38:07 | smoke          | OSX-10.7.x
 32549818 |  668232 | bug377064        | 2012-05-01 08:38:07 | smoke          | OSX-10.7.x
 32549819 |  668232 | funcmem_resize   | 2012-05-01 08:38:07 | smoke          | OSX-10.7.x
 32549819 |  668232 | funcmem_resize   | 2012-05-01 08:38:07 | smoke          | OSX-10.7.x
 32549820 |  668232 | leitest          | 2012-05-01 08:38:07 | smoke          | OSX-10.7.x
 32549820 |  668232 | leitest          | 2012-05-01 08:38:07 | smoke          | OSX-10.7.x

问题在 id 列中可见，即使 a.id 是烟熏表的唯一主键并且实际上没有重复值，但每个值都有两个返回值。 'smoke' 表与 smoketests 表有一对多的关系，但我仍然很困惑为什么我得到所有的副本。

Answer 1

您应该做的第一件事就是停止进行隐式交叉连接。这些使得追踪此类问题变得更加困难。将您的查询重写为:

SELECT a.id,a.suiteid,a.testname
     ,date_trunc('second',a.last_update) AS last_update
     ,regexp_replace(p.relname,E'tests','','g')
     ,o.osname 
FROM smoketests AS a
JOIN pg_class AS p ON a.tableoid=p.oid
JOIN smoke AS t ON a.suiteid=t.id
JOIN osversmap AS o ON a.osversion=o.osversion
WHERE
  AND ( a.current_status='FAILED' )
  AND ( a.arch='i386' )
  AND ( a.os='Darwin' )
  AND a.last_update>'2012-05-01 04:00:00'
  AND a.last_update<'2012-05-02 14:20:45' 
ORDER BY a.id ;

从那里您需要找出导致重复的原因。烟雾会导致重复吗？尝试返回更多记录。如果是这样，请尝试删除连接并将其替换为 IN 子查询。