Query slow again after adding an `OR` operation (was: Slow PostgreSQL10.6 query) - Mailing list pgsql-performance

From Behrang Saeedzadeh
Subject Query slow again after adding an `OR` operation (was: Slow PostgreSQL10.6 query)
Date
Msg-id CAERAJ+9CPDY17gE7X3BK7f3WPvT7an8C_1ph=Vb=yN8WZ7AWzQ@mail.gmail.com
Whole thread Raw
Responses Re: Query slow again after adding an `OR` operation (was: SlowPostgreSQL 10.6 query)
Re: Query slow again after adding an `OR` operation (was: SlowPostgreSQL 10.6 query)
List pgsql-performance

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

pgsql-performance by date:

Previous
From: Andrew Gierth
Date:
Subject: Re: Modification of data in base folder and very large tables
Next
From: Yavuz Selim Sertoğlu (ETIYA)
Date:
Subject: Query slows when used with view