The flavour in question is PostgreSQL, but it is a generic enough query pattern that your techniques for optimising against other RDBMs should help as well.
The purpose of the query is to show all records from a table that are duplicated along several columns (user selectable).
SELECT usr_id,
team_name, team_streetno, team_city,
usr_firstname, usr_lastname, usr_jobtitle, usr_department, usr_email
FROM team c
JOIN usr p on p.team_id=c.team_id
JOIN
(
select match_firstname, match_lastname, match_jobtitle, match_department, match_email ,
match_team_name, match_team_streetno, match_team_city
FROM usr
JOIN team on usr.team_id=team.team_id
GROUP BY match_firstname, match_lastname, match_jobtitle, match_department, match_email,
match_team_name, match_team_streetno, match_team_city
HAVING count(usr_id)>1
) AS sub
ON sub.match_team_name = c.match_team_name
AND sub.match_team_streetno = c.match_team_streetno
AND sub.match_team_city = c.match_team_city
AND sub.match_firstname = p.match_firstname
AND sub.match_lastname = p.match_lastname
AND sub.match_jobtitle = p.match_jobtitle
AND sub.match_department = p.match_department
AND sub.match_email = p.match_email
ORDER BY usr_firstname, usr_lastname, usr_jobtitle, usr_department, usr_email,
team_name, team_streetno, team_city
The EXPLAIN output looks like this:
I tried this variation to make arrays out of the duplicated usr_id's and JOIN back to the usr table using = ANY
; that increased the duration by about 15%.
SELECT u.usr_id
, t.team_name, t.team_streetno, t.team_city
, u.usr_firstname, u.usr_lastname, u.usr_jobtitle, u.usr_department, u.usr_email
FROM (
SELECT array_agg(u1.usr_id) dups
FROM usr u1
JOIN team t1 ON u1.team_id = t1.team_id
GROUP BY match_firstname, match_lastname, match_jobtitle, match_department, match_email
, match_team_name, match_team_streetno, match_team_city
HAVING count(usr_id)>1
) AS dup
JOIN usr u on u.usr_id = ANY (dup.dups)
JOIN team t on t.team_id = u.team_id
ORDER BY u.usr_firstname, u.usr_lastname, u.usr_jobtitle, u.usr_department, u.usr_email
, t.team_name, t.team_streetno, t.team_city
Look forward to your suggestions.
Consolidating comments
Tried WITH TMP AS (... ,count(*) over (partition by...) c FROM ..) WHERE c>1
, which fared worse than the arrag_agg/ANY approach. Seems like windowing functions in PostgreSQL is not that great, or it needs to be rewritten in another form.
For context, consider census data. If someone else filled it for you, he may fill in Tom one year, Thomas or Tomas the next. The "match" columns are derived from the base columns.
EXISTS
has been tried, but didn't perform as well as the code shown above.
COUNT(*) OVER (PARTITION BY ...)
would be a test candidate.EXISTS
way?HAVING COUNT(*)>1
condition).GROUP BY
, depending on your version of Postgres ...