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?
Best regards, Behrang Saeedzadeh blog.behrang.org On Tue, 1 Oct 2019 at 23:27, Tom Lane <t...@sss.pgh.pa.us> wrote: > Behrang Saeedzadeh <behran...@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. > > regards, tom lane >
