Postgres 13

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?.

create table table1 (value text);
insert into table1
values
  ('65→67→69'),
  ('15→19→17'),
  ('25→23→45'),
  ('19→28→98');
4 rows affected

create table table2 (ID int, value text);
insert into table2
values
  (65, 'J7'),
  (67, 'G2'),
  (69, 'P9'),
  (15, 'A8'),
  (19, 'b9'),
  (17, 'C1'),
  (25, 'b12'),
  (23, 'e12'),
  (45, 'A23'),
  (28, 'C17'),
  (98, 'F18');
11 rows affected

select t1.value,
       (select string_agg(t2.value, ',' order by t.idx)
        from table2 t2
          join lateral unnest(string_to_array(t1.value,'→')) with ordinality as t(val,idx) on t2.id::text = t.val
        ) as new_value
from table1 t1;


value
new_value


65→67→69
J7,G2,P9

15→19→17
A8,b9,C1

25→23→45
b12,e12,A23

19→28→98
b9,C17,F18

value	new_value
65→67→69	J7,G2,P9
15→19→17	A8,b9,C1
25→23→45	b12,e12,A23
19→28→98	b9,C17,F18