Thanks. That eliminated the bottleneck!
Any ideas why adding ORDER BY to the subquery also changes the plan in a way that eliminates the bottleneck?
On Tue, 1 Oct 2019 at 23:27, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Behrang Saeedzadeh <behrangsa@xxxxxxxxx> writes:
> On my machine, this query that is generated by Hibernate runs in about 57
> ms on MySQL 8 but it takes more than 1 second to run on PostgreSQL:
> SELECT bills.id AS bill_id,
> bills.bill_date AS bill_date,
> bills.bill_number AS bill_number,
> branch_bills.branch_id AS branch_id,
> company_bills.company_id AS company_id
> FROM tbl_bills bills
> LEFT OUTER JOIN tbl_branch_bills branch_bills ON bills.id =
> branch_bills.bill_id
> LEFT OUTER JOIN tbl_company_bills company_bills ON bills.id =
> company_bills.bill_id
> INNER JOIN tbl_branches ON branch_bills.branch_id =
> tbl_branches.id
> WHERE branch_bills.branch_id IN (
> SELECT b.id
> FROM tbl_branches b
> INNER JOIN tbl_rules r ON b.id = r.branch_id
> INNER JOIN tbl_groups g ON r.group_id = g.id
> INNER JOIN (tbl_group_permissions gp INNER JOIN
> tbl_permissions p ON gp.permission_id = p.id)
> ON g.id = gp.group_id
> INNER JOIN tbl_users u ON r.user_id = u.id
> WHERE u.id = 1
> AND r.rule_type = 'BRANCH'
> AND p.name = 'Permission W'
> );
[ counts the JOINs... ] You might try raising join_collapse_limit and
from_collapse_limit to be 12 or so.
regards, tom lane
