#	exclusive	inclusive	rows x	node
1.	0.000	0.000	↓ 0.0	→ Limit (cost=150,901.57..150,901.67 rows=40 width=533) (actual rows= loops=)
2.	0.000	0.000	↓ 0.0	→ Sort (cost=150,901.57..150,989.26 rows=35,075 width=533) (actual rows= loops=) Sort Key: p.uuid, p.first_name, p.last_name
3.	0.000	0.000	↓ 0.0	→ Subquery Scan on p (cost=148,565.24..149,792.86 rows=35,075 width=533) (actual rows= loops=) Filter: (((p.first_name)::text ~~* '%hpomi%'::text) OR ((p.last_name)::text ~~* '%hpomi%'::text) OR ((p.target_name)::text ~~* '%hpomi%'::text) OR ((p.web_login)::text ~~ '%hpomi%'::text) OR ((p.first_name)::text ~~* '%w%'::text) OR ((p.last_name)::text ~~* '%w%'::text) OR ((p.target_name)::text ~~* '%w%'::text) OR ((p.web_login)::text ~~ '%w%'::text))
4.	0.000	0.000	↓ 0.0	→ Unique (cost=148,565.24..148,740.61 rows=35,075 width=561) (actual rows= loops=)
5.	0.000	0.000	↓ 0.0	→ Sort (cost=148,565.24..148,652.93 rows=35,075 width=561) (actual rows= loops=) Sort Key: p_1.person_uuid
6.	0.000	0.000	↓ 0.0	→ GroupAggregate (cost=137,670.71..140,213.65 rows=35,075 width=561) (actual rows= loops=) Group Key: p_1.revision_uuid, pr.valid_range, pr.sequence
7.	0.000	0.000	↓ 0.0	→ Sort (cost=137,670.71..137,758.40 rows=35,075 width=570) (actual rows= loops=) Sort Key: p_1.revision_uuid, pr.valid_range, pr.sequence
8.	0.000	0.000	↓ 0.0	→ Hash Left Join (cost=35.46..129,242.62 rows=35,075 width=570) (actual rows= loops=) Hash Cond: ((((jsonb_array_elements_text(pr.roles)))::uuid) = role.role_uuid)
9.	0.000	0.000	↓ 0.0	→ Nested Loop Left Join (cost=1.67..128,814.15 rows=35,075 width=529) (actual rows= loops=)
10.	0.000	0.000	↓ 0.0	→ Nested Loop Anti Join (cost=1.67..127,316.26 rows=351 width=546) (actual rows= loops=)
11.	0.000	0.000	↓ 0.0	→ Nested Loop Left Join (cost=1.11..126,054.90 rows=351 width=546) (actual rows= loops=)
12.	0.000	0.000	↓ 0.0	→ Index Scan using persons_org_valid_range_gist on persons p_1 (cost=0.55..124,793.06 rows=351 width=485) (actual rows= loops=) Index Cond: ((organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid) AND (valid_range @> '2019-08-08 15:57:26-07'::timestamp with time zone)) Filter: ((is_visible IS TRUE) AND ((person_uuid = '4b6fe547-24da-c5eb-cdea-1fa664f8053e'::uuid) OR (SubPlan 1) OR (SubPlan 2)))
13.				SubPlan (forIndex Scan)
14.	0.000	0.000	↓ 0.0	→ Result (cost=0.56..3.58 rows=1 width=0) (actual rows= loops=) One-Time Filter: upper_inf(p_1.valid_range)
15.	0.000	0.000	↓ 0.0	→ Index Scan using person_roles_org_seq_vrange_uidx on person_roles person_roles_1 (cost=0.56..3.58 rows=1 width=0) (actual rows= loops=) Index Cond: ((organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid) AND (person_uuid = '4b6fe547-24da-c5eb-cdea-1fa664f8053e'::uuid)) Filter: (is_company_admin AND upper_inf(valid_range))
16.	0.000	0.000	↓ 0.0	→ Nested Loop (cost=6.07..197.77 rows=1 width=0) (actual rows= loops=) Join Filter: (rr.target_role_uuid = (((jsonb_array_elements_text(tpr.roles)))::uuid))
17.	0.000	0.000	↓ 0.0	→ Nested Loop (cost=6.07..193.25 rows=1 width=49) (actual rows= loops=)
18.	0.000	0.000	↓ 0.0	→ Nested Loop (cost=5.50..189.65 rows=1 width=16) (actual rows= loops=)
19.	0.000	0.000	↓ 0.0	→ Index Scan using person_roles_org_seq_vrange_uidx on person_roles spr (cost=0.56..3.58 rows=1 width=33) (actual rows= loops=) Index Cond: ((organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid) AND (person_uuid = '4b6fe547-24da-c5eb-cdea-1fa664f8053e'::uuid)) Filter: upper_inf(valid_range)
20.	0.000	0.000	↓ 0.0	→ Hash Join (cost=4.94..186.06 rows=1 width=16) (actual rows= loops=) Hash Cond: (rr.source_role_uuid = (((jsonb_array_elements_text(spr.roles)))::uuid))
21.	0.000	0.000	↓ 0.0	→ Index Scan using role_on_role_organization_uuid_idx on role_on_role rr (cost=0.42..181.35 rows=47 width=32) (actual rows= loops=) Index Cond: (organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid) Filter: ((can_edit_person OR can_view_person OR can_use_person) AND upper_inf(valid_range))
22.	0.000	0.000	↓ 0.0	→ Hash (cost=3.27..3.27 rows=100 width=16) (actual rows= loops=)
23.	0.000	0.000	↓ 0.0	→ Result (cost=0.00..2.27 rows=100 width=16) (actual rows= loops=)
24.	0.000	0.000	↓ 0.0	→ ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual rows= loops=)
25.	0.000	0.000	↓ 0.0	→ Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)
26.	0.000	0.000	↓ 0.0	→ Index Scan using person_roles_org_seq_vrange_uidx on person_roles tpr (cost=0.56..3.58 rows=1 width=33) (actual rows= loops=) Index Cond: ((organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid) AND (person_uuid = p_1.person_uuid)) Filter: upper_inf(valid_range)
27.	0.000	0.000	↓ 0.0	→ Result (cost=0.00..2.27 rows=100 width=16) (actual rows= loops=)
28.	0.000	0.000	↓ 0.0	→ ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual rows= loops=)
29.	0.000	0.000	↓ 0.0	→ Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)
30.	0.000	0.000	↓ 0.0	→ Index Scan using person_roles_org_seq_vrange_uidx on person_roles pr (cost=0.56..3.58 rows=1 width=77) (actual rows= loops=) Index Cond: ((organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid) AND (person_uuid = p_1.person_uuid)) Filter: upper_inf(valid_range)
31.	0.000	0.000	↓ 0.0	→ Index Scan using person_roles_org_seq_vrange_uidx on person_roles (cost=0.56..3.58 rows=1 width=16) (actual rows= loops=) Index Cond: ((organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid) AND (person_uuid = p_1.person_uuid)) Filter: (is_super_admin AND upper_inf(valid_range))
32.	0.000	0.000	↓ 0.0	→ Result (cost=0.00..2.27 rows=100 width=16) (actual rows= loops=)
33.	0.000	0.000	↓ 0.0	→ ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual rows= loops=)
34.	0.000	0.000	↓ 0.0	→ Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)
35.	0.000	0.000	↓ 0.0	→ Hash (cost=33.70..33.70 rows=7 width=57) (actual rows= loops=)
36.	0.000	0.000	↓ 0.0	→ Bitmap Heap Scan on roles role (cost=1.96..33.70 rows=7 width=57) (actual rows= loops=) Recheck Cond: (organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid) Filter: upper_inf(valid_range)
37.	0.000	0.000	↓ 0.0	→ Bitmap Index Scan on roles_organization_uuid_idx (cost=0.00..1.96 rows=22 width=0) (actual rows= loops=) Index Cond: (organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid)

Limit  (cost=150901.57..150901.67 rows=40 width=533)
  ->  Sort  (cost=150901.57..150989.26 rows=35075 width=533)
        Sort Key: p.uuid, p.first_name, p.last_name
        ->  Subquery Scan on p  (cost=148565.24..149792.86 rows=35075 width=533)
              Filter: (((p.first_name)::text ~~* '%hpomi%'::text) OR ((p.last_name)::text ~~* '%hpomi%'::text) OR ((p.target_name)::text ~~* '%hpomi%'::text) OR ((p.web_login)::text ~~ '%hpomi%'::text) OR ((p.first_name)::text ~~* '%w%'::text) OR ((p.last_name)::text ~~* '%w%'::text) OR ((p.target_name)::text ~~* '%w%'::text) OR ((p.web_login)::text ~~ '%w%'::text))
              ->  Unique  (cost=148565.24..148740.61 rows=35075 width=561)
                    ->  Sort  (cost=148565.24..148652.93 rows=35075 width=561)
                          Sort Key: p_1.person_uuid
                          ->  GroupAggregate  (cost=137670.71..140213.65 rows=35075 width=561)
                                Group Key: p_1.revision_uuid, pr.valid_range, pr.sequence
                                ->  Sort  (cost=137670.71..137758.40 rows=35075 width=570)
                                      Sort Key: p_1.revision_uuid, pr.valid_range, pr.sequence
                                      ->  Hash Left Join  (cost=35.46..129242.62 rows=35075 width=570)
                                            Hash Cond: ((((jsonb_array_elements_text(pr.roles)))::uuid) = role.role_uuid)
                                            ->  Nested Loop Left Join  (cost=1.67..128814.15 rows=35075 width=529)
                                                  ->  Nested Loop Anti Join  (cost=1.67..127316.26 rows=351 width=546)
                                                        ->  Nested Loop Left Join  (cost=1.11..126054.90 rows=351 width=546)
                                                              ->  Index Scan using persons_org_valid_range_gist on persons p_1  (cost=0.55..124793.06 rows=351 width=485)
                                                                    Index Cond: ((organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid) AND (valid_range @> '2019-08-08 15:57:26-07'::timestamp with time zone))
                                                                    Filter: ((is_visible IS TRUE) AND ((person_uuid = '4b6fe547-24da-c5eb-cdea-1fa664f8053e'::uuid) OR (SubPlan 1) OR (SubPlan 2)))
                                                                    SubPlan 1
                                                                      ->  Result  (cost=0.56..3.58 rows=1 width=0)
                                                                            One-Time Filter: upper_inf(p_1.valid_range)
                                                                            ->  Index Scan using person_roles_org_seq_vrange_uidx on person_roles person_roles_1  (cost=0.56..3.58 rows=1 width=0)
                                                                                  Index Cond: ((organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid) AND (person_uuid = '4b6fe547-24da-c5eb-cdea-1fa664f8053e'::uuid))
                                                                                  Filter: (is_company_admin AND upper_inf(valid_range))
                                                                    SubPlan 2
                                                                      ->  Nested Loop  (cost=6.07..197.77 rows=1 width=0)
                                                                            Join Filter: (rr.target_role_uuid = (((jsonb_array_elements_text(tpr.roles)))::uuid))
                                                                            ->  Nested Loop  (cost=6.07..193.25 rows=1 width=49)
                                                                                  ->  Nested Loop  (cost=5.50..189.65 rows=1 width=16)
                                                                                        ->  Index Scan using person_roles_org_seq_vrange_uidx on person_roles spr  (cost=0.56..3.58 rows=1 width=33)
                                                                                              Index Cond: ((organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid) AND (person_uuid = '4b6fe547-24da-c5eb-cdea-1fa664f8053e'::uuid))
                                                                                              Filter: upper_inf(valid_range)
                                                                                        ->  Hash Join  (cost=4.94..186.06 rows=1 width=16)
                                                                                              Hash Cond: (rr.source_role_uuid = (((jsonb_array_elements_text(spr.roles)))::uuid))
                                                                                              ->  Index Scan using role_on_role_organization_uuid_idx on role_on_role rr  (cost=0.42..181.35 rows=47 width=32)
                                                                                                    Index Cond: (organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid)
                                                                                                    Filter: ((can_edit_person OR can_view_person OR can_use_person) AND upper_inf(valid_range))
                                                                                              ->  Hash  (cost=3.27..3.27 rows=100 width=16)
                                                                                                    ->  Result  (cost=0.00..2.27 rows=100 width=16)
                                                                                                          ->  ProjectSet  (cost=0.00..0.52 rows=100 width=32)
                                                                                                                ->  Result  (cost=0.00..0.01 rows=1 width=0)
                                                                                  ->  Index Scan using person_roles_org_seq_vrange_uidx on person_roles tpr  (cost=0.56..3.58 rows=1 width=33)
                                                                                        Index Cond: ((organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid) AND (person_uuid = p_1.person_uuid))
                                                                                        Filter: upper_inf(valid_range)
                                                                            ->  Result  (cost=0.00..2.27 rows=100 width=16)
                                                                                  ->  ProjectSet  (cost=0.00..0.52 rows=100 width=32)
                                                                                        ->  Result  (cost=0.00..0.01 rows=1 width=0)
                                                              ->  Index Scan using person_roles_org_seq_vrange_uidx on person_roles pr  (cost=0.56..3.58 rows=1 width=77)
                                                                    Index Cond: ((organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid) AND (person_uuid = p_1.person_uuid))
                                                                    Filter: upper_inf(valid_range)
                                                        ->  Index Scan using person_roles_org_seq_vrange_uidx on person_roles  (cost=0.56..3.58 rows=1 width=16)
                                                              Index Cond: ((organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid) AND (person_uuid = p_1.person_uuid))
                                                              Filter: (is_super_admin AND upper_inf(valid_range))
                                                  ->  Result  (cost=0.00..2.27 rows=100 width=16)
                                                        ->  ProjectSet  (cost=0.00..0.52 rows=100 width=32)
                                                              ->  Result  (cost=0.00..0.01 rows=1 width=0)
                                            ->  Hash  (cost=33.70..33.70 rows=7 width=57)
                                                  ->  Bitmap Heap Scan on roles role  (cost=1.96..33.70 rows=7 width=57)
                                                        Recheck Cond: (organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid)
                                                        Filter: upper_inf(valid_range)
                                                        ->  Bitmap Index Scan on roles_organization_uuid_idx  (cost=0.00..1.96 rows=22 width=0)
                                                              Index Cond: (organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid)

Per node type stats

node type	count	sum of times	% of query
Bitmap Heap Scan	1	0.000 ms	0.0 %
Bitmap Index Scan	1	0.000 ms	0.0 %
GroupAggregate	1	0.000 ms	0.0 %
Hash	2	0.000 ms	0.0 %
Hash Join	1	0.000 ms	0.0 %
Hash Left Join	1	0.000 ms	0.0 %
Index Scan	7	0.000 ms	0.0 %
Limit	1	0.000 ms	0.0 %
Nested Loop	3	0.000 ms	0.0 %
Nested Loop Anti Join	1	0.000 ms	0.0 %
Nested Loop Left Join	2	0.000 ms	0.0 %
ProjectSet	3	0.000 ms	0.0 %
Result	7	0.000 ms	0.0 %
Sort	3	0.000 ms	0.0 %
Subquery Scan	1	0.000 ms	0.0 %
Unique	1	0.000 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
person_roles	5	0.000 ms	0.0 %
Index Scan	5	0.000 ms	0.0 %
persons	1	0.000 ms	0.0 %
Index Scan	1	0.000 ms	0.0 %
role_on_role	1	0.000 ms	0.0 %
Index Scan	1	0.000 ms	0.0 %
roles	1	0.000 ms	0.0 %
Bitmap Heap Scan	1	0.000 ms	0.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MG5

Color mode:

Visible columns:

Per node type stats

Per table stats