[bts89]

That’s almost always my experience too.

Though fairly recently I learned that even with all the correct joins in place, sometimes adding a DISTINCT within a CTE can dramatically increase performance. I assume there’s some optimizations the query planner can make when it’s been guaranteed record uniqueness.

[Breza]

I agree with you. I also find that adding DISTINCT can sometimes make it easier for my colleagues to understand code, especially when I'm using multiple CTEs and it might be easy to miss a one-to-many join.

[yxhuvud]

I've seen similar effects when changing a bunch of left outer joins to lateral joins with a limit 1 tacked on. The limit do nothing to the end result, but speed up the query by a factor of 1000..