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
Date: Tue, 01 Oct 2019 09:27:06 -0400
Message-ID: <9624.1569936426@sss.pgh.pa.us> (raw)
In-Reply-To: <CAERAJ+-1buiJ+_JWEo0a9Ao-CVMWpgp=EnFx1dJtnB3WmMi2zQ@mail.gmail.com>
References: <CAERAJ+-1buiJ+_JWEo0a9Ao-CVMWpgp=EnFx1dJtnB3WmMi2zQ@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, behrangsa@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Slow PostgreSQL 10.6 query
In-Reply-To: <9624.1569936426@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox