#	exclusive	inclusive	rows x	node
1.	0.000	0.000	↓ 0.0	→ <pre> Limit (cost=17,588,317,650.28..17,588,317,650.48 rows=40 width=569) (actual rows= loops=)
2.	0.000	0.000	↓ 0.0	→ -> Unique (cost=17,588,317,650.28..17,591,199,504.78 rows=576,370,900 width=569) (actual rows= loops=)
3.	0.000	0.000	↓ 0.0	→ -> Sort (cost=17,588,317,650.28..17,589,758,577.53 rows=576,370,900 width=569) (actual rows= loops=) Sort Key: p.person_uuid, p.first_name, p.last_name, (lower(pr.valid_range)) DESC, pr.sequence DESC
4.	0.000	0.000	↓ 0.0	→ -> GroupAggregate (cost=17,081,288,892.24..17,124,516,709.74 rows=576,370,900 width=569) (actual rows= loops=) Group Key: p.revision_uuid, pr.valid_range, pr.sequence
5.	0.000	0.000	↓ 0.0	→ -> Sort (cost=17,081,288,892.24..17,082,729,819.49 rows=576,370,900 width=570) (actual rows= loops=) Sort Key: p.revision_uuid, pr.valid_range, pr.sequence DESC
6.	0.000	0.000	↓ 0.0	→ -> Nested Loop Left Join (cost=2.52..16,617,487,951.70 rows=576,370,900 width=570) (actual rows= loops=) Join Filter: (role.role_uuid = (((jsonb_array_elements_text(pr.roles)))::uuid))
7.	0.000	0.000	↓ 0.0	→ -> Nested Loop Left Join (cost=0.56..16,556,968,973.48 rows=576,370,900 width=529) (actual rows= loops=)
8.	0.000	0.000	↓ 0.0	→ -> Nested Loop Left Join (cost=0.56..16,532,372,345.33 rows=5,763,709 width=546) (actual rows= loops=)
9.	0.000	0.000	↓ 0.0	→ -> Seq Scan on persons p (cost=0.00..16,528,104,120.47 rows=5,763,709 width=485) (actual rows= loops=) Filter: (((organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid) AND (valid_range @> '2019-08-08 22:57:26+00'::timestamp with time zone) AND (is_visible IS TRUE) AND (((first_name)::text ~~* '%hpomi%'::text) OR (
10.				SubPlan (for-> Seq Scan on persons p)
11.	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.valid_range)
12.	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=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))
13.	0.000	0.000	↓ 0.0	→ -> Nested Loop (cost=6.07..198.00 rows=1 width=0) (actual rows= loops=) Join Filter: (rr.target_role_uuid = (((jsonb_array_elements_text(tpr.roles)))::uuid))
14.	0.000	0.000	↓ 0.0	→ -> Nested Loop (cost=6.07..193.48 rows=1 width=49) (actual rows= loops=)
15.	0.000	0.000	↓ 0.0	→ -> Nested Loop (cost=5.50..189.88 rows=1 width=16) (actual rows= loops=)
16.	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)
17.	0.000	0.000	↓ 0.0	→ -> Hash Join (cost=4.94..186.29 rows=1 width=16) (actual rows= loops=) Hash Cond: (rr.source_role_uuid = (((jsonb_array_elements_text(spr.roles)))::uuid))
18.	0.000	0.000	↓ 0.0	→ -> Index Scan using role_on_role_organization_uuid_idx on role_on_role rr (cost=0.42..181.59 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))
19.	0.000	0.000	↓ 0.0	→ -> Hash (cost=3.27..3.27 rows=100 width=16) (actual rows= loops=)
20.	0.000	0.000	↓ 0.0	→ -> Result (cost=0.00..2.27 rows=100 width=16) (actual rows= loops=)
21.	0.000	0.000	↓ 0.0	→ -> ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual rows= loops=)
22.	0.000	0.000	↓ 0.0	→ -> Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)
23.	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.person_uuid)) Filter: upper_inf(valid_range)
24.	0.000	0.000	↓ 0.0	→ -> Result (cost=0.00..2.27 rows=100 width=16) (actual rows= loops=)
25.	0.000	0.000	↓ 0.0	→ -> ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual rows= loops=)
26.	0.000	0.000	↓ 0.0	→ -> Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)
27.	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 = p.person_uuid)) Filter: (is_super_admin AND upper_inf(valid_range))
28.	0.000	0.000	↓ 0.0	→ -> Index Scan using person_roles_organization_uuid_idx on person_roles person_roles_2 (cost=0.56..2,525.45 rows=1 width=16) (actual rows= loops=) Index Cond: (organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid) Filter: (is_super_admin AND upper_inf(valid_range))
29.	0.000	0.000	↓ 0.0	→ -> Index Scan using person_roles_org_seq_vrange_uidx on person_roles pr (cost=0.56..0.73 rows=1 width=77) (actual rows= loops=) Index Cond: ((organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid) AND (person_uuid = p.person_uuid)) Filter: upper_inf(valid_range)
30.	0.000	0.000	↓ 0.0	→ -> Result (cost=0.00..2.27 rows=100 width=16) (actual rows= loops=)
31.	0.000	0.000	↓ 0.0	→ -> ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual rows= loops=)
32.	0.000	0.000	↓ 0.0	→ -> Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)
33.	0.000	0.000	↓ 0.0	→ -> Materialize (cost=1.96..33.73 rows=7 width=57) (actual rows= loops=)
34.	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)
35.	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)

<pre> Limit  (cost=17588317650.28..17588317650.48 rows=40 width=569)
   -&gt;  Unique  (cost=17588317650.28..17591199504.78 rows=576370900 width=569)
         -&gt;  Sort  (cost=17588317650.28..17589758577.53 rows=576370900 width=569)
               Sort Key: p.person_uuid, p.first_name, p.last_name, (lower(pr.valid_range)) DESC, pr.sequence DESC
               -&gt;  GroupAggregate  (cost=17081288892.24..17124516709.74 rows=576370900 width=569)
                     Group Key: p.revision_uuid, pr.valid_range, pr.sequence
                     -&gt;  Sort  (cost=17081288892.24..17082729819.49 rows=576370900 width=570)
                           Sort Key: p.revision_uuid, pr.valid_range, pr.sequence DESC
                           -&gt;  Nested Loop Left Join  (cost=2.52..16617487951.70 rows=576370900 width=570)
                                 Join Filter: (role.role_uuid = (((jsonb_array_elements_text(pr.roles)))::uuid))
                                 -&gt;  Nested Loop Left Join  (cost=0.56..16556968973.48 rows=576370900 width=529)
                                       -&gt;  Nested Loop Left Join  (cost=0.56..16532372345.33 rows=5763709 width=546)
                                             -&gt;  Seq Scan on persons p  (cost=0.00..16528104120.47 rows=5763709 width=485)
                                                   Filter: (((organization_uuid = &apos;02ea59bf-4c3c-42e1-8f47-1d037f8755b9&apos;::uuid) AND (valid_range @&gt; &apos;2019-08-08 22:57:26+00&apos;::timestamp with time zone) AND (is_visible IS TRUE) AND (((first_name)::text ~~* &apos;%hpomi%&apos;::text) OR (
(last_name)::text ~~* &apos;%hpomi%&apos;::text) OR ((target_name)::text ~~* &apos;%hpomi%&apos;::text) OR ((web_login)::text ~~ &apos;%hpomi%&apos;::text))) OR ((((first_name)::text ~~* &apos;%w%&apos;::text) OR ((last_name)::text ~~* &apos;%w%&apos;::text) OR ((target_name)::text ~~* &apos;%w%&apos;::text) OR ((web_login)::text ~~ 
&apos;%w%&apos;::text)) AND ((person_uuid = &apos;4b6fe547-24da-c5eb-cdea-1fa664f8053e&apos;::uuid) OR (SubPlan 1) OR (SubPlan 2)) AND (NOT (alternatives: SubPlan 3 or hashed SubPlan 4))))
                                                   SubPlan 1
                                                     -&gt;  Result  (cost=0.56..3.58 rows=1 width=0)
                                                           One-Time Filter: upper_inf(p.valid_range)
                                                           -&gt;  Index Scan using person_roles_org_seq_vrange_uidx on person_roles  (cost=0.56..3.58 rows=1 width=0)
                                                                 Index Cond: ((organization_uuid = &apos;02ea59bf-4c3c-42e1-8f47-1d037f8755b9&apos;::uuid) AND (person_uuid = &apos;4b6fe547-24da-c5eb-cdea-1fa664f8053e&apos;::uuid))
                                                                 Filter: (is_company_admin AND upper_inf(valid_range))
                                                   SubPlan 2
                                                     -&gt;  Nested Loop  (cost=6.07..198.00 rows=1 width=0)
                                                           Join Filter: (rr.target_role_uuid = (((jsonb_array_elements_text(tpr.roles)))::uuid))
                                                           -&gt;  Nested Loop  (cost=6.07..193.48 rows=1 width=49)
                                                                 -&gt;  Nested Loop  (cost=5.50..189.88 rows=1 width=16)
                                                                       -&gt;  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 = &apos;02ea59bf-4c3c-42e1-8f47-1d037f8755b9&apos;::uuid) AND (person_uuid = &apos;4b6fe547-24da-c5eb-cdea-1fa664f8053e&apos;::uuid))
                                                                             Filter: upper_inf(valid_range)
                                                                       -&gt;  Hash Join  (cost=4.94..186.29 rows=1 width=16)
                                                                             Hash Cond: (rr.source_role_uuid = (((jsonb_array_elements_text(spr.roles)))::uuid))
                                                                             -&gt;  Index Scan using role_on_role_organization_uuid_idx on role_on_role rr  (cost=0.42..181.59 rows=47 width=32)
                                                                                   Index Cond: (organization_uuid = &apos;02ea59bf-4c3c-42e1-8f47-1d037f8755b9&apos;::uuid)
                                                                                   Filter: ((can_edit_person OR can_view_person OR can_use_person) AND upper_inf(valid_range))
                                                                             -&gt;  Hash  (cost=3.27..3.27 rows=100 width=16)
                                                                                   -&gt;  Result  (cost=0.00..2.27 rows=100 width=16)
                                                                                         -&gt;  ProjectSet  (cost=0.00..0.52 rows=100 width=32)
                                                                                               -&gt;  Result  (cost=0.00..0.01 rows=1 width=0)
                                                                 -&gt;  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 = &apos;02ea59bf-4c3c-42e1-8f47-1d037f8755b9&apos;::uuid) AND (person_uuid = p.person_uuid))
                                                                       Filter: upper_inf(valid_range)
                                                           -&gt;  Result  (cost=0.00..2.27 rows=100 width=16)
                                                                 -&gt;  ProjectSet  (cost=0.00..0.52 rows=100 width=32)
                                                                       -&gt;  Result  (cost=0.00..0.01 rows=1 width=0)
                                                   SubPlan 3
                                                     -&gt;  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 = &apos;02ea59bf-4c3c-42e1-8f47-1d037f8755b9&apos;::uuid) AND (person_uuid = p.person_uuid))
                                                           Filter: (is_super_admin AND upper_inf(valid_range))
                                                   SubPlan 4
                                                     -&gt;  Index Scan using person_roles_organization_uuid_idx on person_roles person_roles_2  (cost=0.56..2525.45 rows=1 width=16)
                                                           Index Cond: (organization_uuid = &apos;02ea59bf-4c3c-42e1-8f47-1d037f8755b9&apos;::uuid)
                                                           Filter: (is_super_admin AND upper_inf(valid_range))
                                             -&gt;  Index Scan using person_roles_org_seq_vrange_uidx on person_roles pr  (cost=0.56..0.73 rows=1 width=77)
                                                   Index Cond: ((organization_uuid = &apos;02ea59bf-4c3c-42e1-8f47-1d037f8755b9&apos;::uuid) AND (person_uuid = p.person_uuid))
                                                   Filter: upper_inf(valid_range)
                                       -&gt;  Result  (cost=0.00..2.27 rows=100 width=16)
                                             -&gt;  ProjectSet  (cost=0.00..0.52 rows=100 width=32)
                                                   -&gt;  Result  (cost=0.00..0.01 rows=1 width=0)
                                 -&gt;  Materialize  (cost=1.96..33.73 rows=7 width=57)
                                       -&gt;  Bitmap Heap Scan on roles role  (cost=1.96..33.70 rows=7 width=57)
                                             Recheck Cond: (organization_uuid = &apos;02ea59bf-4c3c-42e1-8f47-1d037f8755b9&apos;::uuid)
                                             Filter: upper_inf(valid_range)
                                             -&gt;  Bitmap Index Scan on roles_organization_uuid_idx  (cost=0.00..1.96 rows=22 width=0)
                                                   Index Cond: (organization_uuid = &apos;02ea59bf-4c3c-42e1-8f47-1d037f8755b9&apos;::uuid)
</pre>

Per node type stats

node type	count	sum of times	% of query
-> Bitmap Heap Scan on roles role	1	0.000 ms	0.0 %
-> Bitmap Index Scan on roles_organization_uuid_idx	1	0.000 ms	0.0 %
-> GroupAggregate	1	0.000 ms	0.0 %
-> Hash	1	0.000 ms	0.0 %
-> Hash Join	1	0.000 ms	0.0 %
-> Index Scan using person_roles_org_seq_vrange_uidx on person_roles	1	0.000 ms	0.0 %
-> Index Scan using person_roles_org_seq_vrange_uidx on person_roles person_roles_1	1	0.000 ms	0.0 %
-> Index Scan using person_roles_org_seq_vrange_uidx on person_roles pr	1	0.000 ms	0.0 %
-> Index Scan using person_roles_org_seq_vrange_uidx on person_roles spr	1	0.000 ms	0.0 %
-> Index Scan using person_roles_org_seq_vrange_uidx on person_roles tpr	1	0.000 ms	0.0 %
-> Index Scan using person_roles_organization_uuid_idx on person_roles person_roles_2	1	0.000 ms	0.0 %
-> Index Scan using role_on_role_organization_uuid_idx on role_on_role rr	1	0.000 ms	0.0 %
-> Materialize	1	0.000 ms	0.0 %
-> Nested Loop	3	0.000 ms	0.0 %
-> Nested Loop Left Join	3	0.000 ms	0.0 %
-> ProjectSet	3	0.000 ms	0.0 %
-> Result	7	0.000 ms	0.0 %
-> Seq Scan on persons p	1	0.000 ms	0.0 %
-> Sort	2	0.000 ms	0.0 %
-> Unique	1	0.000 ms	0.0 %
<pre> Limit	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

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: P3EC

Color mode:

Visible columns:

Per node type stats

Per table stats