Thread: Slow PostgreSQL 10.6 query

Slow PostgreSQL 10.6 query

From
Behrang Saeedzadeh
Date:

Hardware

  • CPU: Core i7 6700
  • OS: Ubuntu 19.04
  • RAM: 32GB (limited to 2GB for this test)

Also reproducible on a 2018 MacBook Pro.

Details

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'
);

PostgreSQL does not seem to be choosing the best plan to execute this query due to the IN( <subquery> ) expression. Adding indexes does not seem to eliminate this particular bottleneck.

As the query is generated bt Hibernate, it is not possible to tweak it easily (there's a way to parse the generated SQL and modify it before it is executed, but ideally I would like to avoid that). Otherwise it was possible to rewrite the query without the subquery. Another tweak that seems to work (but again not supported by JPA/Hibernate) is adding a dummy order by clause to the sub query:

```
EXPLAIN ( ANALYZE , COSTS , VERBOSE , BUFFERS )
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'
    ORDER BY b.id
);

Hash Right Join  (cost=69.70..105.15 rows=108 width=48) (actual time=1.814..1.893 rows=324 loops=1)
"  Output: bills.id, bills.bill_date, bills.bill_number, branch_bills.branch_id, company_bills.company_id"
  Hash Cond: (company_bills.bill_id = bills.id)
  Buffers: shared hit=1320 read=6
  ->  Seq Scan on public.tbl_company_bills company_bills  (cost=0.00..28.50 rows=1850 width=16) (actual time=0.003..0.003 rows=0 loops=1)
"        Output: company_bills.company_id, company_bills.bill_id"
  ->  Hash  (cost=68.35..68.35 rows=108 width=40) (actual time=1.805..1.806 rows=324 loops=1)
"        Output: bills.id, bills.bill_date, bills.bill_number, branch_bills.branch_id"
        Buckets: 1024  Batches: 1  Memory Usage: 31kB
        Buffers: shared hit=1320 read=6
        ->  Nested Loop  (cost=6.87..68.35 rows=108 width=40) (actual time=0.141..1.692 rows=324 loops=1)
"              Output: bills.id, bills.bill_date, bills.bill_number, branch_bills.branch_id"
              Inner Unique: true
              Buffers: shared hit=1320 read=6
              ->  Nested Loop  (cost=6.44..15.55 rows=108 width=16) (actual time=0.135..0.299 rows=324 loops=1)
"                    Output: branch_bills.branch_id, branch_bills.bill_id"
                    Buffers: shared hit=25 read=3
                    ->  Nested Loop  (cost=6.01..10.04 rows=1 width=16) (actual time=0.086..0.094 rows=3 loops=1)
"                          Output: tbl_branches.id, b.id"
                          Inner Unique: true
                          Buffers: shared hit=17
                          ->  HashAggregate  (cost=5.73..5.74 rows=1 width=8) (actual time=0.081..0.083 rows=3 loops=1)
                                Output: b.id
                                Group Key: b.id
                                Buffers: shared hit=10
                                ->  Nested Loop  (cost=1.40..5.72 rows=1 width=8) (actual time=0.064..0.077 rows=3 loops=1)
                                      Output: b.id
                                      Buffers: shared hit=10
                                      ->  Nested Loop  (cost=1.40..4.69 rows=1 width=16) (actual time=0.062..0.070 rows=3 loops=1)
"                                            Output: b.id, r.user_id"
                                            Join Filter: (r.group_id = g.id)
                                            Buffers: shared hit=7
                                            ->  Merge Join  (cost=1.40..1.55 rows=3 width=24) (actual time=0.050..0.054 rows=3 loops=1)
"                                                  Output: b.id, r.group_id, r.user_id"
                                                  Merge Cond: (b.id = r.branch_id)
                                                  Buffers: shared hit=4
                                                  ->  Index Only Scan using tbl_branches_pkey on public.tbl_branches b  (cost=0.29..270.29 rows=10000 width=8) (actual time=0.021..0.022 rows=6 loops=1)
                                                        Output: b.id
                                                        Heap Fetches: 0
                                                        Buffers: shared hit=3
                                                  ->  Sort  (cost=1.11..1.12 rows=3 width=24) (actual time=0.023..0.024 rows=3 loops=1)
"                                                        Output: r.branch_id, r.group_id, r.user_id"
                                                        Sort Key: r.branch_id
                                                        Sort Method: quicksort  Memory: 25kB
                                                        Buffers: shared hit=1
                                                        ->  Seq Scan on public.tbl_rules r  (cost=0.00..1.09 rows=3 width=24) (actual time=0.010..0.013 rows=3 loops=1)
"                                                              Output: r.branch_id, r.group_id, r.user_id"
                                                              Filter: ((r.user_id = 1) AND ((r.rule_type)::text = 'BRANCH'::text))
                                                              Rows Removed by Filter: 3
                                                              Buffers: shared hit=1
                                            ->  Materialize  (cost=0.00..3.10 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=3)
"                                                  Output: g.id, gp.group_id"
                                                  Buffers: shared hit=3
                                                  ->  Nested Loop  (cost=0.00..3.10 rows=1 width=16) (actual time=0.010..0.011 rows=1 loops=1)
"                                                        Output: g.id, gp.group_id"
                                                        Inner Unique: true
                                                        Join Filter: (gp.permission_id = p.id)
                                                        Buffers: shared hit=3
                                                        ->  Nested Loop  (cost=0.00..2.03 rows=1 width=24) (actual time=0.006..0.007 rows=1 loops=1)
"                                                              Output: g.id, gp.permission_id, gp.group_id"
                                                              Join Filter: (g.id = gp.group_id)
                                                              Buffers: shared hit=2
                                                              ->  Seq Scan on public.tbl_groups g  (cost=0.00..1.01 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1)
"                                                                    Output: g.id, g.name"
                                                                    Buffers: shared hit=1
                                                              ->  Seq Scan on public.tbl_group_permissions gp  (cost=0.00..1.01 rows=1 width=16) (actual time=0.002..0.003 rows=1 loops=1)
"                                                                    Output: gp.group_id, gp.permission_id"
                                                                    Buffers: shared hit=1
                                                        ->  Seq Scan on public.tbl_permissions p  (cost=0.00..1.05 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=1)
"                                                              Output: p.id, p.name"
                                                              Filter: ((p.name)::text = 'Permission W'::text)
                                                              Buffers: shared hit=1
                                      ->  Seq Scan on public.tbl_users u  (cost=0.00..1.01 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=3)
"                                            Output: u.id, u.user_email"
                                            Filter: (u.id = 1)
                                            Buffers: shared hit=3
                          ->  Index Only Scan using tbl_branches_pkey on public.tbl_branches  (cost=0.29..4.30 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=3)
                                Output: tbl_branches.id
                                Index Cond: (tbl_branches.id = b.id)
                                Heap Fetches: 0
                                Buffers: shared hit=7
                    ->  Index Only Scan using tbl_branch_bills_pkey on public.tbl_branch_bills branch_bills  (cost=0.43..4.43 rows=108 width=16) (actual time=0.020..0.047 rows=108 loops=3)
"                          Output: branch_bills.branch_id, branch_bills.bill_id"
                          Index Cond: (branch_bills.branch_id = tbl_branches.id)
                          Heap Fetches: 0
                          Buffers: shared hit=8 read=3
              ->  Index Scan using tbl_bills_pkey on public.tbl_bills bills  (cost=0.43..0.49 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=324)
"                    Output: bills.id, bills.bill_date, bills.bill_number"
                    Index Cond: (bills.id = branch_bills.bill_id)
                    Buffers: shared hit=1295 read=3
Planning time: 1.999 ms
Execution time: 2.005 ms

```

This will reduce execution time from more than 1s to under 3ms.

Is there a way to make PostgreSQL to choose the same plan as when the order by clause is present without changing it?

Here are the necessary steps to reproduce this issue.

1.1 Run MySQL 8 and PostgreSQL 10.6 locally

$ docker run --name mysql8 \            -e MYSQL_ROOT_PASSWORD=password -p 13306:3306 \            -d mysql:8

$ docker update --cpus 2 --memory 2GB mysql8

1.2. Create the MySQL database

CREATE TABLE `tbl_bills`
(   `id`          bigint(20)   NOT NULL AUTO_INCREMENT,   `bill_date`   date         NOT NULL,   `bill_number` varchar(255) NOT NULL,   PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

CREATE TABLE `tbl_branch_bills`
(   `branch_id` bigint(20) DEFAULT NULL,   `bill_id`   bigint(20) NOT NULL,   PRIMARY KEY (`bill_id`),   KEY `FKjr0egr9t34sxr1pv2ld1ux174` (`branch_id`),   CONSTRAINT `FK7ekkvq33j12dw8a8bwx90a0gb` FOREIGN KEY (`bill_id`) REFERENCES `tbl_bills` (`id`),   CONSTRAINT `FKjr0egr9t34sxr1pv2ld1ux174` FOREIGN KEY (`branch_id`) REFERENCES `tbl_branches` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

CREATE TABLE `tbl_branches`
(   `id`         bigint(20)   NOT NULL AUTO_INCREMENT,   `name`       varchar(255) NOT NULL,   `company_id` bigint(20) DEFAULT NULL,   PRIMARY KEY (`id`),   KEY `FK1fde50hcsaf4os3fq6isshf23` (`company_id`),   CONSTRAINT `FK1fde50hcsaf4os3fq6isshf23` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

CREATE TABLE `tbl_companies`
(   `id`   bigint(20)   NOT NULL AUTO_INCREMENT,   `name` varchar(255) NOT NULL,   PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

CREATE TABLE `tbl_company_bills`
(   `company_id` bigint(20) DEFAULT NULL,   `bill_id`    bigint(20) NOT NULL,   PRIMARY KEY (`bill_id`),   KEY `FKet3kkl9d16jeb5v8ic5pvq89` (`company_id`),   CONSTRAINT `FK6d3r6to4orsc0mgflgt7aefsh` FOREIGN KEY (`bill_id`) REFERENCES `tbl_bills` (`id`),   CONSTRAINT `FKet3kkl9d16jeb5v8ic5pvq89` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

CREATE TABLE `tbl_group_permissions`
(   `group_id`      bigint(20) NOT NULL,   `permission_id` bigint(20) NOT NULL,   PRIMARY KEY (`group_id`, `permission_id`),   KEY `FKocxt78iv4ufox094sdr1pudf7` (`permission_id`),   CONSTRAINT `FKe4adr2lkq2s61ju3pnbiq5m14` FOREIGN KEY (`group_id`) REFERENCES `tbl_groups` (`id`),   CONSTRAINT `FKocxt78iv4ufox094sdr1pudf7` FOREIGN KEY (`permission_id`) REFERENCES `tbl_permissions` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

CREATE TABLE `tbl_groups`
(   `id`   bigint(20) NOT NULL AUTO_INCREMENT,   `name` varchar(255) DEFAULT NULL,   PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

CREATE TABLE `tbl_permissions`
(   `id`   bigint(20)   NOT NULL AUTO_INCREMENT,   `name` varchar(256) NOT NULL,   PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

CREATE TABLE `tbl_rules`
(   `id`         bigint(20)   NOT NULL AUTO_INCREMENT,   `rule_type`  varchar(255) NOT NULL,   `branch_id`  bigint(20) DEFAULT NULL,   `company_id` bigint(20) DEFAULT NULL,   `group_id`   bigint(20) DEFAULT NULL,   `user_id`    bigint(20) DEFAULT NULL,   PRIMARY KEY (`id`),   KEY `FK18sr791qaonsmvodm1v7g8vyr` (`branch_id`),   KEY `FKtjjtlnfuxmbj4xij3j9t0m99m` (`company_id`),   KEY `FKldsvxs2qijr9quon4srw627ky` (`group_id`),   KEY `FKp28tcx68kdbb8flhl1xdtl0hp` (`user_id`),   CONSTRAINT `FK18sr791qaonsmvodm1v7g8vyr` FOREIGN KEY (`branch_id`) REFERENCES `tbl_branches` (`id`),   CONSTRAINT `FKldsvxs2qijr9quon4srw627ky` FOREIGN KEY (`group_id`) REFERENCES `tbl_groups` (`id`),   CONSTRAINT `FKp28tcx68kdbb8flhl1xdtl0hp` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`),   CONSTRAINT `FKtjjtlnfuxmbj4xij3j9t0m99m` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

CREATE TABLE `tbl_users`
(   `id`         bigint(20)   NOT NULL AUTO_INCREMENT,   `user_email` varchar(255) NOT NULL,   PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

CREATE OR REPLACE VIEW generator_16
AS SELECT 0 n  UNION ALL SELECT 1  UNION ALL SELECT 2  UNION ALL SELECT 3  UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL SELECT 6  UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL SELECT 9  UNION ALL SELECT 10  UNION ALL SELECT 11  UNION ALL SELECT 12  UNION ALL SELECT 13  UNION ALL SELECT 14  UNION ALL SELECT 15;

CREATE OR REPLACE VIEW generator_256
AS
SELECT ((hi.n << 4) | lo.n) AS n
FROM generator_16 lo,    generator_16 hi;

CREATE OR REPLACE VIEW generator_4k
AS
SELECT ((hi.n << 8) | lo.n) AS n
FROM generator_256 lo,    generator_16 hi;

CREATE OR REPLACE VIEW generator_64k
AS
SELECT ((hi.n << 8) | lo.n) AS n
FROM generator_256 lo,    generator_256 hi;

CREATE OR REPLACE VIEW generator_1m
AS
SELECT ((hi.n << 16) | lo.n) AS n
FROM generator_64k lo,    generator_16 hi;

CREATE OR replace view dates_10y AS   SELECT date('2010-01-01') d   UNION ALL SELECT date('2010-02-01')   UNION ALL SELECT date('2010-03-01')   UNION ALL SELECT date('2010-04-01')   UNION ALL SELECT date('2010-05-01')   UNION ALL SELECT date('2010-06-01')   UNION ALL SELECT date('2010-07-01')   UNION ALL SELECT date('2010-08-01')   UNION ALL SELECT date('2010-09-01')   UNION ALL SELECT date('2010-10-01')   UNION ALL SELECT date('2010-12-01')   UNION ALL SELECT date('2010-12-01')   UNION ALL SELECT date('2011-01-01')   UNION ALL SELECT date('2011-02-01')   UNION ALL SELECT date('2011-03-01')   UNION ALL SELECT date('2011-04-01')   UNION ALL SELECT date('2011-05-01')   UNION ALL SELECT date('2011-06-01')   UNION ALL SELECT date('2011-07-01')   UNION ALL SELECT date('2011-08-01')   UNION ALL SELECT date('2011-09-01')   UNION ALL SELECT date('2011-10-01')   UNION ALL SELECT date('2011-12-01')   UNION ALL SELECT date('2011-12-01')   UNION ALL SELECT date('2012-01-01')   UNION ALL SELECT date('2012-02-01')   UNION ALL SELECT date('2012-03-01')   UNION ALL SELECT date('2012-04-01')   UNION ALL SELECT date('2012-05-01')   UNION ALL SELECT date('2012-06-01')   UNION ALL SELECT date('2012-07-01')   UNION ALL SELECT date('2012-08-01')   UNION ALL SELECT date('2012-09-01')   UNION ALL SELECT date('2012-10-01')   UNION ALL SELECT date('2012-12-01')   UNION ALL SELECT date('2012-12-01')   UNION ALL SELECT date('2013-01-01')   UNION ALL SELECT date('2013-02-01')   UNION ALL SELECT date('2013-03-01')   UNION ALL SELECT date('2013-04-01')   UNION ALL SELECT date('2013-05-01')   UNION ALL SELECT date('2013-06-01')   UNION ALL SELECT date('2013-07-01')   UNION ALL SELECT date('2013-08-01')   UNION ALL SELECT date('2013-09-01')   UNION ALL SELECT date('2013-10-01')   UNION ALL SELECT date('2013-12-01')   UNION ALL SELECT date('2013-12-01')   UNION ALL SELECT date('2014-01-01')   UNION ALL SELECT date('2014-02-01')   UNION ALL SELECT date('2014-03-01')   UNION ALL SELECT date('2014-04-01')   UNION ALL SELECT date('2014-05-01')   UNION ALL SELECT date('2014-06-01')   UNION ALL SELECT date('2014-07-01')   UNION ALL SELECT date('2014-08-01')   UNION ALL SELECT date('2014-09-01')   UNION ALL SELECT date('2014-10-01')   UNION ALL SELECT date('2014-12-01')   UNION ALL SELECT date('2014-12-01')   UNION ALL SELECT date('2015-01-01')   UNION ALL SELECT date('2015-02-01')   UNION ALL SELECT date('2015-03-01')   UNION ALL SELECT date('2015-04-01')   UNION ALL SELECT date('2015-05-01')   UNION ALL SELECT date('2015-06-01')   UNION ALL SELECT date('2015-07-01')   UNION ALL SELECT date('2015-08-01')   UNION ALL SELECT date('2015-09-01')   UNION ALL SELECT date('2015-10-01')   UNION ALL SELECT date('2015-12-01')   UNION ALL SELECT date('2015-12-01')   UNION ALL SELECT date('2016-01-01')   UNION ALL SELECT date('2016-02-01')   UNION ALL SELECT date('2016-03-01')   UNION ALL SELECT date('2016-04-01')   UNION ALL SELECT date('2016-05-01')   UNION ALL SELECT date('2016-06-01')   UNION ALL SELECT date('2016-07-01')   UNION ALL SELECT date('2016-08-01')   UNION ALL SELECT date('2016-09-01')   UNION ALL SELECT date('2016-10-01')   UNION ALL SELECT date('2016-12-01')   UNION ALL SELECT date('2016-12-01')   UNION ALL SELECT date('2017-01-01')   UNION ALL SELECT date('2017-02-01')   UNION ALL SELECT date('2017-03-01')   UNION ALL SELECT date('2017-04-01')   UNION ALL SELECT date('2017-05-01')   UNION ALL SELECT date('2017-06-01')   UNION ALL SELECT date('2017-07-01')   UNION ALL SELECT date('2017-08-01')   UNION ALL SELECT date('2017-09-01')   UNION ALL SELECT date('2017-10-01')   UNION ALL SELECT date('2017-12-01')   UNION ALL SELECT date('2017-12-01')   UNION ALL SELECT date('2018-01-01')   UNION ALL SELECT date('2018-02-01')   UNION ALL SELECT date('2018-03-01')   UNION ALL SELECT date('2018-04-01')   UNION ALL SELECT date('2018-05-01')   UNION ALL SELECT date('2018-06-01')   UNION ALL SELECT date('2018-07-01')   UNION ALL SELECT date('2018-08-01')   UNION ALL SELECT date('2018-09-01')   UNION ALL SELECT date('2018-10-01')   UNION ALL SELECT date('2018-12-01')   UNION ALL SELECT date('2018-12-01')   UNION ALL SELECT date('2019-01-01')   UNION ALL SELECT date('2019-02-01')   UNION ALL SELECT date('2019-03-01')   UNION ALL SELECT date('2019-04-01')   UNION ALL SELECT date('2019-05-01')   UNION ALL SELECT date('2019-06-01')   UNION ALL SELECT date('2019-07-01')   UNION ALL SELECT date('2019-08-01')   UNION ALL SELECT date('2019-09-01')   UNION ALL SELECT date('2019-10-01')   UNION ALL SELECT date('2019-12-01')   UNION ALL SELECT date('2019-12-01')   UNION ALL SELECT date('2020-01-01')   UNION ALL SELECT date('2020-02-01')   UNION ALL SELECT date('2020-03-01')   UNION ALL SELECT date('2020-04-01')   UNION ALL SELECT date('2020-05-01')   UNION ALL SELECT date('2020-06-01')   UNION ALL SELECT date('2020-07-01')   UNION ALL SELECT date('2020-08-01')   UNION ALL SELECT date('2020-09-01')   UNION ALL SELECT date('2020-10-01')   UNION ALL SELECT date('2020-12-01')   UNION ALL SELECT date('2020-12-01');

1.3. Populate the MySQL database

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE tbl_users;
TRUNCATE tbl_groups;
TRUNCATE tbl_permissions;
TRUNCATE tbl_group_permissions;
TRUNCATE tbl_rules;
TRUNCATE tbl_companies;
TRUNCATE tbl_branches;
TRUNCATE tbl_bills;
TRUNCATE tbl_company_bills;
TRUNCATE tbl_branch_bills;

SET FOREIGN_KEY_CHECKS = 1;

INSERT INTO tbl_companies(name)
SELECT CONCAT('Company ', g.n)
from generator_4k as g
LIMIT 100;

INSERT INTO tbl_branches(name, company_id)
SELECT CONCAT('Branch ', b.n, ' (Company', c.id, ')'), c.id
from generator_4k as b,    tbl_companies c
WHERE b.n < 100;

INSERT INTO tbl_users(user_email)
VALUES ('email@example.com');

INSERT INTO tbl_groups(name)
VALUES ('Group X');

INSERT INTO tbl_permissions(name)
VALUES ('Permission W'),      ('Permission X'),      ('Permission Y'),      ('Permission Z');

INSERT INTO tbl_group_permissions(group_id, permission_id)
SELECT g.id, p.id
FROM tbl_groups      g,    tbl_permissions p
WHERE g.name = 'Group X' AND p.name = 'Permission W';

INSERT INTO tbl_rules(rule_type, user_id, group_id, company_id, branch_id)
SELECT 'BRANCH', u.id, g.id, b.company_id, b.id
FROM tbl_branches b,    tbl_groups   g,    tbl_users    u
WHERE (g.name = 'Group X' AND b.id IN (1, 3, 5));

INSERT INTO tbl_rules(rule_type, user_id, group_id, company_id, branch_id)
SELECT 'COMPANY', u.id, g.id, c.id, NULL
FROM tbl_companies c,    tbl_groups    g,    tbl_users     u
WHERE (g.name = 'Group X' AND c.id IN (2, 4, 6));

SET FOREIGN_KEY_CHECKS = 0;

INSERT INTO tbl_branch_bills(branch_id, bill_id)
SELECT b.id, ROW_NUMBER() OVER ()
from tbl_branches b,    dates_10y d;

INSERT INTO tbl_bills(id, bill_date, bill_number)
SELECT ROW_NUMBER() OVER (), d.d, CONCAT('#NUM-', d.d, '-', b.id) from tbl_branches b,dates_10y d;

SET FOREIGN_KEY_CHECKS = 1;

1.4. Run the query

EXPLAIN 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'
);

1,SIMPLE,u,,const,PRIMARY,PRIMARY,8,const,1,100,Using index
1,SIMPLE,g,,index,PRIMARY,PRIMARY,8,,1,100,Using index; Start temporary
1,SIMPLE,gp,,ref,"PRIMARY,FKocxt78iv4ufox094sdr1pudf7",PRIMARY,8,companies_and_branches.g.id,1,100,Using index
1,SIMPLE,p,,eq_ref,PRIMARY,PRIMARY,8,companies_and_branches.gp.permission_id,1,25,Using where
1,SIMPLE,r,,ref,"FK18sr791qaonsmvodm1v7g8vyr,FKldsvxs2qijr9quon4srw627ky,FKp28tcx68kdbb8flhl1xdtl0hp",FKldsvxs2qijr9quon4srw627ky,9,companies_and_branches.g.id,1,16.67,Using where
1,SIMPLE,b,,eq_ref,PRIMARY,PRIMARY,8,companies_and_branches.r.branch_id,1,100,Using index
1,SIMPLE,tbl_branches,,eq_ref,PRIMARY,PRIMARY,8,companies_and_branches.r.branch_id,1,100,Using index
1,SIMPLE,branch_bills,,ref,"PRIMARY,FKjr0egr9t34sxr1pv2ld1ux174",FKjr0egr9t34sxr1pv2ld1ux174,9,companies_and_branches.r.branch_id,1,100,Using where; Using index
1,SIMPLE,bills,,eq_ref,PRIMARY,PRIMARY,8,companies_and_branches.branch_bills.bill_id,1,100,
1,SIMPLE,company_bills,,eq_ref,PRIMARY,PRIMARY,8,companies_and_branches.branch_bills.bill_id,1,100,End temporary

10 rows retrieved starting from 1 in 50 ms (execution: 6 ms, fetching: 44 ms)

2.1 Run PostgreSQL 10.6 locally

$ docker run --name postgres106 \            -e POSTGRES_PASSWORD=password \            -p 15432:5432 \            -d postgres:10.6

$ docker update --cpus 2 --memory 2GB postgres106

2.2. Create the PostgreSQL database

DROP TABLE IF EXISTS tbl_rules,   tbl_permissions,   tbl_groups,   tbl_group_permissions,   tbl_companies,   tbl_branches,   tbl_departments,   tbl_users,   tbl_company_bills,   tbl_branch_bills,   tbl_bills CASCADE;

CREATE TABLE tbl_permissions
(   id   bigserial    NOT NULL PRIMARY KEY,   name varchar(255) NOT NULL UNIQUE
);

CREATE TABLE tbl_groups
(   id   bigserial NOT NULL PRIMARY KEY,   name varchar(255) UNIQUE
);

CREATE TABLE tbl_group_permissions
(   group_id      bigint NOT NULL REFERENCES tbl_groups (id),   permission_id bigint NOT NULL REFERENCES tbl_permissions (id),   PRIMARY KEY (group_id, permission_id)
);

CREATE TABLE tbl_companies
(   id   bigserial NOT NULL PRIMARY KEY,   name text      NOT NULL
);

CREATE TABLE tbl_branches
(   id         bigserial NOT NULL PRIMARY KEY,   company_id bigint    NOT NULL REFERENCES tbl_companies (id),   name       text      NOT NULL
);

CREATE TABLE tbl_users
(   id         bigserial    NOT NULL PRIMARY KEY,   user_email varchar(255) NOT NULL
);

CREATE TABLE tbl_rules
(   id         bigserial NOT NULL PRIMARY KEY,   rule_type  varchar(255),   user_id    bigint REFERENCES tbl_users (id),   group_id   bigint REFERENCES tbl_groups (id),   company_id bigint REFERENCES tbl_companies (id),   branch_id  bigint REFERENCES tbl_branches (id)
);

CREATE TABLE tbl_bills
(   id          bigserial    NOT NULL PRIMARY KEY,   bill_date   date         NOT NULL,   bill_number varchar(255) NOT NULL UNIQUE,   CONSTRAINT bill_const1 UNIQUE (bill_date, bill_number)
);

CREATE TABLE tbl_company_bills
(   company_id bigint REFERENCES tbl_companies (id),   bill_id    bigint NOT NULL REFERENCES tbl_bills (id),   PRIMARY KEY (company_id, bill_id)
);

CREATE TABLE tbl_branch_bills
(   branch_id bigint REFERENCES tbl_branches (id),   bill_id   bigint NOT NULL REFERENCES tbl_bills (id),   PRIMARY KEY (branch_id, bill_id)
);

2.3. Populate the PostgreSQL database

TRUNCATE tbl_users, tbl_companies, tbl_branches, tbl_groups, tbl_permissions, tbl_group_permissions, tbl_rules, tbl_bills, tbl_branch_bills, tbl_company_bills RESTART IDENTITY CASCADE;

INSERT INTO tbl_users(user_email)
VALUES ('email@example.com');

WITH new_comps AS (INSERT INTO tbl_companies (id, name)   SELECT nextval('tbl_companies_id_seq'),          'Company ' || currval('tbl_companies_id_seq')   FROM generate_series(1, 100) num RETURNING id)
INSERT
INTO tbl_branches(id, company_id, name)
SELECT nextval('tbl_branches_id_seq'),      c.id,      'Branch ' || currval('tbl_branches_id_seq') || ' ( Company ' || c.id || ')'
FROM new_comps               c,    generate_series(1, 100) num;

INSERT INTO tbl_groups(name)
VALUES ('Group X');

INSERT INTO tbl_permissions(name)
VALUES ('Permission W'),      ('Permission X'),      ('Permission Y'),      ('Permission Z');

INSERT INTO tbl_group_permissions(group_id, permission_id)
SELECT g.id, p.id
FROM tbl_groups      g,    tbl_permissions p
WHERE g.name = 'Group X' AND p.name = 'Permission W';

INSERT INTO tbl_rules(rule_type, user_id, group_id, company_id, branch_id)
SELECT 'BRANCH', u.id, g.id, b.company_id, b.id
FROM tbl_branches b,    tbl_groups   g,    tbl_users    u
WHERE (g.name = 'Group X' AND b.id IN (1, 3, 5));

INSERT INTO tbl_rules(rule_type, user_id, group_id, company_id, branch_id)
SELECT 'COMPANY', u.id, g.id, c.id, NULL
FROM tbl_companies c,    tbl_groups    g,    tbl_users     u
WHERE (g.name = 'Group X' AND c.id IN (2, 4, 6));

WITH ids AS (SELECT nextval('tbl_bills_id_seq') AS bill_id,                   make_date(year, month, 1)   AS bill_date,                   br.id                       AS branch_id            FROM tbl_branches                AS br,                 generate_series(2010, 2018) AS year,                 generate_series(1, 12)      AS month
),    bills AS (INSERT INTO tbl_bills (id, bill_date, bill_number)        SELECT ids.bill_id                                      AS billl_id,               ids.bill_date                                    AS bill_date,               '#NUM-' || ids.bill_date || '-' || ids.branch_id AS bill_num        FROM ids RETURNING *)
INSERT
INTO tbl_branch_bills(branch_id, bill_id)
SELECT branch_id, bill_id
FROM ids;

EXPLAIN ( ANALYZE , COSTS , VERBOSE , BUFFERS , FORMAT JSON )
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'
);

2.4. Run the query

VACUUM ANALYZE ;

EXPLAIN ( ANALYZE , COSTS , VERBOSE , BUFFERS )
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'
);

Gather  (cost=36865.05..89524.81 rows=108 width=48) (actual time=667.105..1976.054 rows=324 loops=1)
"  Output: bills.id, bills.bill_date, bills.bill_number, branch_bills.branch_id, company_bills.company_id" Workers Planned: 2 Workers Launched: 2
"  Buffers: shared hit=28392 read=4240 written=336, temp read=20821 written=20635" ->  Hash Semi Join  (cost=35865.05..88514.01 rows=45 width=48) (actual time=636.256..1948.638 rows=108 loops=3)
"        Output: bills.id, bills.bill_date, bills.bill_number, branch_bills.branch_id, company_bills.company_id"       Hash Cond: (branch_bills.branch_id = b.id)
"        Buffers: shared hit=28392 read=4240 written=336, temp read=20821 written=20635"       Worker 0: actual time=563.702..1964.847 rows=105 loops=1
"          Buffers: shared hit=10027 read=953 written=109, temp read=6971 written=6909"       Worker 1: actual time=679.468..1965.037 rows=122 loops=1
"          Buffers: shared hit=9292 read=1628 written=114, temp read=6960 written=6898"       ->  Hash Join  (cost=35859.32..87326.53 rows=450000 width=56) (actual time=491.279..1875.725 rows=360000 loops=3)
"              Output: bills.id, bills.bill_date, bills.bill_number, branch_bills.branch_id, company_bills.company_id, tbl_branches.id"             Inner Unique: true             Hash Cond: (branch_bills.branch_id = tbl_branches.id)
"              Buffers: shared hit=28269 read=4239 written=336, temp read=20821 written=20635"             Worker 0: actual time=497.021..1870.969 rows=364536 loops=1
"                Buffers: shared hit=9971 read=952 written=109, temp read=6971 written=6909"             Worker 1: actual time=479.286..1900.802 rows=363072 loops=1
"                Buffers: shared hit=9235 read=1628 written=114, temp read=6960 written=6898"             ->  Hash Join  (cost=35541.32..85826.78 rows=450000 width=48) (actual time=487.460..1545.962 rows=360000 loops=3)
"                    Output: bills.id, bills.bill_date, bills.bill_number, branch_bills.branch_id, company_bills.company_id"                   Hash Cond: (bills.id = branch_bills.bill_id)
"                    Buffers: shared hit=27990 read=4239 written=336, temp read=20821 written=20635"                   Worker 0: actual time=493.881..1583.609 rows=364536 loops=1
"                      Buffers: shared hit=9878 read=952 written=109, temp read=6971 written=6909"                   Worker 1: actual time=474.878..1542.282 rows=363072 loops=1
"                      Buffers: shared hit=9142 read=1628 written=114, temp read=6960 written=6898"                   ->  Merge Left Join  (cost=129.32..31921.28 rows=450000 width=40) (actual time=0.047..239.155 rows=360000 loops=3)
"                          Output: bills.id, bills.bill_date, bills.bill_number, company_bills.company_id"                         Merge Cond: (bills.id = company_bills.bill_id)                         Buffers: shared hit=12327 read=2345 written=336                         Worker 0: actual time=0.058..248.250 rows=364536 loops=1                           Buffers: shared hit=4336 read=637 written=109                         Worker 1: actual time=0.065..222.495 rows=363072 loops=1                           Buffers: shared hit=3979 read=929 written=114                         ->  Parallel Index Scan using tbl_bills_pkey on public.tbl_bills bills  (cost=0.43..30650.43 rows=450000 width=32) (actual time=0.030..127.785 rows=360000 loops=3)
"                                Output: bills.id, bills.bill_date, bills.bill_number"                               Buffers: shared hit=12327 read=2345 written=336                               Worker 0: actual time=0.037..105.247 rows=364536 loops=1                                 Buffers: shared hit=4336 read=637 written=109                               Worker 1: actual time=0.044..108.513 rows=363072 loops=1                                 Buffers: shared hit=3979 read=929 written=114                         ->  Sort  (cost=128.89..133.52 rows=1850 width=16) (actual time=0.015..0.015 rows=0 loops=3)
"                                Output: company_bills.company_id, company_bills.bill_id"                               Sort Key: company_bills.bill_id                               Sort Method: quicksort  Memory: 25kB                               Worker 0: actual time=0.019..0.019 rows=0 loops=1                               Worker 1: actual time=0.018..0.018 rows=0 loops=1                               ->  Seq Scan on public.tbl_company_bills company_bills  (cost=0.00..28.50 rows=1850 width=16) (actual time=0.006..0.006 rows=0 loops=3)
"                                      Output: company_bills.company_id, company_bills.bill_id"                                     Worker 0: actual time=0.007..0.007 rows=0 loops=1                                     Worker 1: actual time=0.008..0.008 rows=0 loops=1                   ->  Hash  (cost=16638.00..16638.00 rows=1080000 width=16) (actual time=486.822..486.822 rows=1080000 loops=3)
"                          Output: branch_bills.branch_id, branch_bills.bill_id"                         Buckets: 131072  Batches: 32  Memory Usage: 2614kB
"                          Buffers: shared hit=15620 read=1894, temp written=13740"                         Worker 0: actual time=493.045..493.045 rows=1080000 loops=1
"                            Buffers: shared hit=5523 read=315, temp written=4580"                         Worker 1: actual time=474.144..474.144 rows=1080000 loops=1
"                            Buffers: shared hit=5139 read=699, temp written=4580"                         ->  Seq Scan on public.tbl_branch_bills branch_bills  (cost=0.00..16638.00 rows=1080000 width=16) (actual time=0.025..158.450 rows=1080000 loops=3)
"                                Output: branch_bills.branch_id, branch_bills.bill_id"                               Buffers: shared hit=15620 read=1894                               Worker 0: actual time=0.032..182.305 rows=1080000 loops=1                                 Buffers: shared hit=5523 read=315                               Worker 1: actual time=0.022..144.461 rows=1080000 loops=1                                 Buffers: shared hit=5139 read=699             ->  Hash  (cost=193.00..193.00 rows=10000 width=8) (actual time=3.769..3.769 rows=10000 loops=3)                   Output: tbl_branches.id                   Buckets: 16384  Batches: 1  Memory Usage: 519kB                   Buffers: shared hit=279                   Worker 0: actual time=3.077..3.077 rows=10000 loops=1                     Buffers: shared hit=93                   Worker 1: actual time=4.331..4.331 rows=10000 loops=1                     Buffers: shared hit=93                   ->  Seq Scan on public.tbl_branches  (cost=0.00..193.00 rows=10000 width=8) (actual time=0.006..1.755 rows=10000 loops=3)                         Output: tbl_branches.id                         Buffers: shared hit=279                         Worker 0: actual time=0.007..1.485 rows=10000 loops=1                           Buffers: shared hit=93                         Worker 1: actual time=0.008..1.980 rows=10000 loops=1                           Buffers: shared hit=93       ->  Hash  (cost=5.72..5.72 rows=1 width=16) (actual time=0.117..0.117 rows=3 loops=3)
"              Output: b.id, r.branch_id"             Buckets: 1024  Batches: 1  Memory Usage: 9kB             Buffers: shared hit=40             Worker 0: actual time=0.125..0.125 rows=3 loops=1               Buffers: shared hit=15             Worker 1: actual time=0.156..0.156 rows=3 loops=1               Buffers: shared hit=15             ->  Nested Loop  (cost=1.40..5.72 rows=1 width=16) (actual time=0.102..0.113 rows=3 loops=3)
"                    Output: b.id, r.branch_id"                   Buffers: shared hit=40                   Worker 0: actual time=0.111..0.120 rows=3 loops=1                     Buffers: shared hit=15                   Worker 1: actual time=0.140..0.153 rows=3 loops=1                     Buffers: shared hit=15                   ->  Nested Loop  (cost=1.40..4.69 rows=1 width=24) (actual time=0.096..0.103 rows=3 loops=3)
"                          Output: b.id, r.branch_id, r.user_id"                         Join Filter: (r.group_id = g.id)                         Buffers: shared hit=31                         Worker 0: actual time=0.107..0.112 rows=3 loops=1                           Buffers: shared hit=12                         Worker 1: actual time=0.131..0.139 rows=3 loops=1                           Buffers: shared hit=12                         ->  Merge Join  (cost=1.40..1.55 rows=3 width=32) (actual time=0.073..0.077 rows=3 loops=3)
"                                Output: b.id, r.branch_id, r.group_id, r.user_id"                               Merge Cond: (b.id = r.branch_id)                               Buffers: shared hit=22                               Worker 0: actual time=0.079..0.082 rows=3 loops=1                                 Buffers: shared hit=9                               Worker 1: actual time=0.102..0.107 rows=3 loops=1                                 Buffers: shared hit=9                               ->  Index Only Scan using tbl_branches_pkey on public.tbl_branches b  (cost=0.29..270.29 rows=10000 width=8) (actual time=0.035..0.036 rows=6 loops=3)                                     Output: b.id                                     Heap Fetches: 0                                     Buffers: shared hit=11                                     Worker 0: actual time=0.038..0.039 rows=6 loops=1                                       Buffers: shared hit=4                                     Worker 1: actual time=0.049..0.051 rows=6 loops=1                                       Buffers: shared hit=4                               ->  Sort  (cost=1.11..1.12 rows=3 width=24) (actual time=0.035..0.036 rows=3 loops=3)
"                                      Output: r.branch_id, r.group_id, r.user_id"                                     Sort Key: r.branch_id                                     Sort Method: quicksort  Memory: 25kB                                     Buffers: shared hit=11                                     Worker 0: actual time=0.039..0.039 rows=3 loops=1                                       Buffers: shared hit=5                                     Worker 1: actual time=0.050..0.051 rows=3 loops=1                                       Buffers: shared hit=5                                     ->  Seq Scan on public.tbl_rules r  (cost=0.00..1.09 rows=3 width=24) (actual time=0.017..0.019 rows=3 loops=3)
"                                            Output: r.branch_id, r.group_id, r.user_id"                                           Filter: ((r.user_id = 1) AND ((r.rule_type)::text = 'BRANCH'::text))                                           Rows Removed by Filter: 3                                           Buffers: shared hit=3                                           Worker 0: actual time=0.015..0.016 rows=3 loops=1                                             Buffers: shared hit=1                                           Worker 1: actual time=0.028..0.030 rows=3 loops=1                                             Buffers: shared hit=1                         ->  Materialize  (cost=0.00..3.10 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=9)
"                                Output: g.id, gp.group_id"                               Buffers: shared hit=9                               Worker 0: actual time=0.009..0.010 rows=1 loops=3                                 Buffers: shared hit=3                               Worker 1: actual time=0.009..0.010 rows=1 loops=3                                 Buffers: shared hit=3                               ->  Nested Loop  (cost=0.00..3.10 rows=1 width=16) (actual time=0.019..0.020 rows=1 loops=3)
"                                      Output: g.id, gp.group_id"                                     Inner Unique: true                                     Join Filter: (gp.permission_id = p.id)                                     Buffers: shared hit=9                                     Worker 0: actual time=0.024..0.025 rows=1 loops=1                                       Buffers: shared hit=3                                     Worker 1: actual time=0.024..0.025 rows=1 loops=1                                       Buffers: shared hit=3                                     ->  Nested Loop  (cost=0.00..2.03 rows=1 width=24) (actual time=0.012..0.012 rows=1 loops=3)
"                                            Output: g.id, gp.permission_id, gp.group_id"                                           Join Filter: (g.id = gp.group_id)                                           Buffers: shared hit=6                                           Worker 0: actual time=0.013..0.014 rows=1 loops=1                                             Buffers: shared hit=2                                           Worker 1: actual time=0.015..0.016 rows=1 loops=1                                             Buffers: shared hit=2                                           ->  Seq Scan on public.tbl_groups g  (cost=0.00..1.01 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=3)
"                                                  Output: g.id, g.name"                                                 Buffers: shared hit=3                                                 Worker 0: actual time=0.006..0.006 rows=1 loops=1                                                   Buffers: shared hit=1                                                 Worker 1: actual time=0.006..0.006 rows=1 loops=1                                                   Buffers: shared hit=1                                           ->  Seq Scan on public.tbl_group_permissions gp  (cost=0.00..1.01 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=3)
"                                                  Output: gp.group_id, gp.permission_id"                                                 Buffers: shared hit=3                                                 Worker 0: actual time=0.006..0.007 rows=1 loops=1                                                   Buffers: shared hit=1                                                 Worker 1: actual time=0.008..0.008 rows=1 loops=1                                                   Buffers: shared hit=1                                     ->  Seq Scan on public.tbl_permissions p  (cost=0.00..1.05 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=3)
"                                            Output: p.id, p.name"                                           Filter: ((p.name)::text = 'Permission W'::text)                                           Buffers: shared hit=3                                           Worker 0: actual time=0.010..0.010 rows=1 loops=1                                             Buffers: shared hit=1                                           Worker 1: actual time=0.008..0.008 rows=1 loops=1                                             Buffers: shared hit=1                   ->  Seq Scan on public.tbl_users u  (cost=0.00..1.01 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=9)
"                          Output: u.id, u.user_email"                         Filter: (u.id = 1)                         Buffers: shared hit=9                         Worker 0: actual time=0.001..0.002 rows=1 loops=3                           Buffers: shared hit=3                         Worker 1: actual time=0.003..0.004 rows=1 loops=3                           Buffers: shared hit=3
Planning time: 2.680 ms
Execution time: 1976.277 ms

Best regards,
Behrang Saeedzadeh

Re: Slow PostgreSQL 10.6 query

From
Tom Lane
Date:
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



Re: Slow PostgreSQL 10.6 query

From
Behrang Saeedzadeh
Date:
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


On Tue, 1 Oct 2019 at 23:27, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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

Re: Slow PostgreSQL 10.6 query

From
Tomas Vondra
Date:
On Tue, Oct 01, 2019 at 11:42:33PM +1000, Behrang Saeedzadeh wrote:
>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?
>

IIRC the ORDER BY clause makes it impossible to "collapse" the subquery
into the main (upper) one, and it probably happens to constrict the
choices so that the planner ends up picking a good plan. I guess adding
"OFFSET 0" to the subquery would have the same effect.


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services 



Re: Slow PostgreSQL 10.6 query

From
Behrang Saeedzadeh
Date:
Thanks for the tip!

Regards,
Behrang (sent from my mobile)

On Mon, Oct 7, 2019, 07:37 Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote:
On Tue, Oct 01, 2019 at 11:42:33PM +1000, Behrang Saeedzadeh wrote:
>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?
>

IIRC the ORDER BY clause makes it impossible to "collapse" the subquery
into the main (upper) one, and it probably happens to constrict the
choices so that the planner ends up picking a good plan. I guess adding
"OFFSET 0" to the subquery would have the same effect.


regards

--
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services