From: Tom Lane <tgl@sss.pgh.pa.us>
To: Behrang Saeedzadeh <behrangsa@gmail.com>
cc: pgsql-performance@lists.postgresql.org
Subject: Re: Slow PostgreSQL 10.6 query
In-reply-to: 
 <CAERAJ+-1buiJ+_JWEo0a9Ao-CVMWpgp=EnFx1dJtnB3WmMi2zQ@mail.gmail.com>
References: 
 <CAERAJ+-1buiJ+_JWEo0a9Ao-CVMWpgp=EnFx1dJtnB3WmMi2zQ@mail.gmail.com>
Comments: In-reply-to Behrang Saeedzadeh <behrangsa@gmail.com>
	message dated "Tue, 01 Oct 2019 22:37:03 +1000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <9623.1569936426.1@sss.pgh.pa.us>
Date: Tue, 01 Oct 2019 09:27:06 -0400
Message-ID: <9624.1569936426@sss.pgh.pa.us>
Precedence: bulk

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.

			regards, tom lane