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

From: Behrang Saeedzadeh <behrangsa(at)gmail(dot)com>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: Query slow again after adding an `OR` operation (was: Slow PostgreSQL 10.6 query)
Date: 2019-10-09 12:07:02
Message-ID: CAERAJ+9CPDY17gE7X3BK7f3WPvT7an8C_1ph=Vb=yN8WZ7AWzQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Yavuz Selim Sertoğlu (ETIYA) 2019-10-09 12:31:40 Query slows when used with view
Previous Message Andrew Gierth 2019-10-09 08:42:18 Re: Modification of data in base folder and very large tables