filter on first join as much as possible due to following left joins

This is just a comment, and can be read different ways, but it seems to suggest that postgres will perform the joins in the order that they are list in the query, which is not necessarily true.
As I understand it, there is no guarantee of this, though outer joins can constrain the order of joining.

Later you have some lines like:

        joins.insert(0, 'buildroot_archives ON buildroot.id = buildroot_archives.buildroot_id AND buildroot_archives.archive_id = %(archiveID)i')

Since postgres chooses join order itself, this is confusing to the reader. It implies the order of the join list is the actual order of the joins.

OTOH, It looks like we could set join_collapse_limit in postgres if we wanted force a particular join order.

https://www.postgresql.org/docs/current/explicit-joins.html

I don't think join_collapse_limit is needed here (but I can test it anyway). The main gain is culling rows during the joins, not creating supertable on which are WHERE clauses run afterwards.

https://pagure.io/koji/issue/1562

It is broken for now.

Änyway, putting inner joins before outer joins is still making a difference. Table will be truncated by join condition and outer joins will be applied only on this result. In current situation, all outer joins are applied first and result is culled by inner joins.

I was not able to make it better this way, so different approach taken in #2074

Can we close this now that #2074 is merged?

Yes