I have three tables where I store actual person data (person), teams (team) and entries (athlete). The schema of the three tables is:

In each team there might be two or more athletes.

I'm trying to create a query to produce the most frequent pairs, meaning people who play in teams of two. I came up with the following query:

SELECT p1.surname, p1.name, p2.surname, p2.name, COUNT(*) AS freq
FROM person p1, athlete a1, person p2, athlete a2
WHERE
    p1.id = a1.person_id AND
    p2.id = a2.person_id AND
    a1.team_id = a2.team_id AND
    a1.team_id IN
          ( SELECT id
            FROM team, athlete
            WHERE team.id = athlete.team_id
            GROUP BY team.id
            HAVING COUNT(*) = 2 )
GROUP BY p1.id
ORDER BY freq DESC

Obviously this is a resource consuming query. Is there a way to improve it?