Thank you for your response. I have tested out the patch in the linked
thread and it works very well on a bunch of complex queries that I
have tested, improving both the planning time significantly and the
execution time drastically.
I have also read through the entire linked discussion thread as well
as a few other large threads linked from it, and found the discussion
very interesting.
I don't believe that all such queries are "poorly-written". As was
discussed in the other threads, the reason these types of self-joins
can occur is when you use SQL views. You can create a library of
reusable views that are small, easy-to-understand and readable. Then
you build them up into bigger views, and finally query from them. But
then you end up with lots of (hidden) self-joins. The alternative is
to copy&paste the shared logic from the views into all of the queries.
I understand the need to be conservative about which optimizations to
apply in order to not waste time looking for opportunities that don't
exist. One idea I had that I didn't see mentioned is the following
heuristic: Only if a query references an SQL view (or multiple views),
then try to apply the self_join_removal optimization. This should be
enough, because as you say, no human would intentionally write such a
query. Queries generated by ORMs were also discussed, so I believe it
might also be beneficial to consider queries that contain inner
SELECTs.
