Re: Slow PostgreSQL 10.6 query

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 <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Behrang Saeedzadeh <behrangsa(at)gmail(dot)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
>