Distinct is a great way to get quick results when writing quick & dirty queries, but I rarely have them perform better than a re-write that avoids the need. It collects a ton of results, orders them, and throws away duplicates in the process. I don't love the idea of that extra work. Did you say you have an index on c1?
c1,
sub1.c2,
sub2.c3
from
t
join lateral (select c2 from t1 where t1.c1 = t.c1 order by c2, c4 limit 1 ) as sub1 on true
join lateral (select c3 from t1 where t1.c1 = t.c1 order by coalesce(c4, '000'), c3 limit 1 ) as sub2 on true;
c1,
(select c2 from t1 where t1.c1 = t.c1 order by c2, c4 limit 1 ) AS c2,
(select c3 from t1 where t1.c1 = t.c1 order by coalesce(c4, '000'), c3 limit 1 ) AS c3
from
t;
I don't know the data, but I assume there may be many rows with the same c1 value, so then you would likely benefit from getting that distinct set first like below as your FROM table.
(select c1 from t group by c1 ) AS t
