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