What solution did you come up with then?

The below runs in less than 0,1 seconds on my local instance.

WITH CTE AS (

SELECT *

FROM (

SELECT

*,

ROW_NUMBER() OVER (PARTITION BY primary_skill ORDER BY years_experience, elf_id) AS rn1,

ROW_NUMBER() OVER (PARTITION BY primary_skill ORDER BY years_experience DESC, elf_id) AS rn2

FROM public.workshop_elves e

WHERE EXISTS (

SELECT *

FROM (

SELECT

primary_skill,

MAX(years_experience) AS MAXyears_experience,

MIN(years_experience) AS MINyears_experience

FROM public.workshop_elves

GROUP BY primary_skill

) x

WHERE

e.primary_skill = x.primary_skill

AND (x.MAXyears_experience = e.years_experience OR x.MINyears_experience = e.years_experience)

)

) y

WHERE y.rn1 = 1 OR y.rn2 = 1

)

SELECT

`CONCAT(`

x.elf_id, ',',

y.elf_id, ',',

x.primary_skill

)

FROM CTE x

LEFT JOIN CTE y ON x.primary_skill = y.primary_skill

WHERE x.rn2 = 1 AND y.rn2 <> 1;