Re: Slow PostgreSQL 10.6 query

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