select distinct tbl1."BDN_EmployeeTerritories_ID", tbl2."BDN_Terretories_ID", tbl1."Reference_Date" from
(
select "BDN_EmployeeTerritories_ID", "Reference_Date", token
from public."BDN_EmployeeTerritories",
unnest(string_to_array("EMP_TerretoryID", ';')) s(token)
--order by token
) tbl1
join (
select "BDN_Terretories_ID", token from public."BDN_Terretories", unnest(string_to_array("EMP_TerretoryID", ';')) s(token)
) tbl2 on tbl1.token = tbl2.token Observations:
1. query runs for 4-5 seconds on v16 and less than a second on v15 2. in v16 it also goes downs to less than a second if 2.1 distinct is removed
2.2 unnest is removed. it is not really needed for this particular data but this query is autogenerated and unnest makes sense for other data
2.3 "order by token" is uncommented, this is my current way of fixing the problem I would really appreciate some feedback if that is expected behaviour and if there are better solutions