Re: Is postgres able to share sorts required by common partition window functions?

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?

select
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;

>

select
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*