Query slow again after adding an `OR` operation (was: Slow PostgreSQL 10.6 query)
Behrang Saeedzadeh <[email protected]> Wed, 9 Oct 2019 23:07:02 +1100
| Newsgroups | gmane.comp.db.postgresql.performance |
|---|---|
| Message-ID | <CAERAJ+9CPDY17gE7X3BK7f3WPvT7an8C_1ph=Vb=yN8WZ7AWzQ@mail.gmail.com> |
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_idFROM 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.idWHERE
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 ASCLIMIT 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