Behrang Saeedzadeh <behrangsa@gmail.com> 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.
