Query slow again after adding an `OR` operation (was: Slow PostgreSQL 10.6 query)

[Behrang Saeedzadeh <behrangsa@xxxxxxxxx>]

This is a follow up to https://www.postgresql.org/message-id/flat/CAERAJ%2B-1buiJ%2B_JWEo0a9Ao-CVMWpgp%3DEnFx1dJtnB3WmMi2zQ%40mail.gmail.com

The query (generated by Hibernate) got a bit more complex and performance degraded again. I have uploaded all the details here (with changed table names, etc.): https://github.com/behrangsa/slow-query

In short, the new query is:

```

SELECT inv.id           AS i_id,
       inv.invoice_date AS inv_d,
       inv.invoice_xid  AS inv_xid,
       inv.invoice_type AS inv_type,
       brs.branch_id    AS br_id,
       cinvs.company_id AS c_id
FROM invoices inv
         LEFT OUTER JOIN branch_invoices brs ON inv.id = brs.invoice_id
         LEFT OUTER JOIN company_invoices cinvs ON inv.id = cinvs.invoice_id
         INNER JOIN branches br ON brs.branch_id = br.id
WHERE brs.branch_id IN (SELECT br1.id
                        FROM branches br1
                                 INNER JOIN access_rights ar1 ON br1.id = ar1.branch_id
                                 INNER JOIN users usr1 ON ar1.user_id = usr1.id
                                 INNER JOIN groups grp1 ON ar1.group_id = grp1.id
                                 INNER JOIN group_permissions gpr1 ON grp1.id = gpr1.group_id
                                 INNER JOIN permissions prm1 ON gpr1.permission_id = prm1.id
                        WHERE usr1.id = 1636
                          AND prm1.code = 'C2'
                          AND ar1.access_type = 'T1')
   OR brs.branch_id IN (SELECT br3.id
                        FROM companies cmp
                                 INNER JOIN branches br3 ON cmp.id = br3.company_id
                                 INNER JOIN access_rights ar2 ON cmp.id = ar2.company_id
                                 INNER JOIN users usr2 ON ar2.user_id = usr2.id
                                 INNER JOIN groups g2 ON ar2.group_id = g2.id
                                 INNER JOIN group_permissions gpr2 ON g2.id = gpr2.group_id
                                 INNER JOIN permissions prm2 ON gpr2.permission_id = prm2.id
                        WHERE usr2.id = 1636
                          AND prm2.code = 'C2'
                          AND ar2.access_type = 'T1'
                        ORDER BY br3.id)
ORDER BY inv.invoice_date DESC, br.name ASC
LIMIT 12;

```

I tried tweaking join_collapse_limit and from_collapse_limit (I tried up to 30) but couldn't improve the performance (I also increased geqo_threshold to join_collapse_limit + 2).

Any chance of making PostgreSQL 10.6 choose a better plan without rewriting the Hibernate generated query?

Best regards,

Behrang Saeedzadeh

blog.behrang.org