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
