Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

select v.user_id, array_agg(distinct names)
from (select v.id,
             max(el) filter (where n = un) as user_id,
             array_agg(el order by el) filter (where n in (un - 2, un - 1)) as names
      from (select v.id, u.*,
                   max(u.n) filter (where el like 'uuid=%') over (partition by v.id) as un
            from (values (1 , 'junkgoeshere;John;Smith;uuid=7c32e9e1-e29e-4211-b11e-e20b2cb78da9; ..'),
                         (2 , 'junkgoeshere;John;Smith;uuid=7c32e9e1-e29e-4211-b11e-e20b2cb78da9; ..'),
                         (3 , 'junkgoeshere;John;Smith;uuid=new_7c32e9e1-e29e-4211-b11e-e20b2cb78da9; ..'),
                         (4 , 'junkgoeshere;John;Jay;uuid=new_7c32e9e1-e29e-4211-b11e-e20b2cb78da9; ..')
                 ) v(id, str) cross join lateral
                 unnest(regexp_split_to_array(v.str, ';')) with ordinality u(el, n)
           ) v
      where n between un - 2 and un
      group by v.id
     ) v
group by user_id
having min(names) <> max(names)

user_id	array_agg
uuid=new_7c32e9e1-e29e-4211-b11e-e20b2cb78da9	{{Jay,John},{John,Smith}}