explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8Ig

Settings
# exclusive inclusive rows x rows loops node
1. 0.031 1,907,895.946 ↑ 1.0 40 1

Limit (cost=17,587,109,016.11..17,587,109,016.31 rows=40 width=569) (actual time=1,907,895.896..1,907,895.946 rows=40 loops=1)

2. 0.020 1,907,895.915 ↑ 14,408,282.5 40 1

Unique (cost=17,587,109,016.11..17,589,990,672.61 rows=576,331,300 width=569) (actual time=1,907,895.893..1,907,895.915 rows=40 loops=1)

3. 1.220 1,907,895.895 ↑ 6,943,750.6 83 1

Sort (cost=17,587,109,016.11..17,588,549,844.36 rows=576,331,300 width=569) (actual time=1,907,895.889..1,907,895.895 rows=83 loops=1)

  • Sort Key: p.person_uuid, p.first_name, p.last_name, (lower(pr.valid_range)) DESC, pr.sequence DESC
  • Sort Method: quicksort Memory: 483kB
4. 3.255 1,907,894.675 ↑ 932,574.9 618 1

GroupAggregate (cost=17,080,115,375.99..17,123,340,223.49 rows=576,331,300 width=569) (actual time=1,907,891.462..1,907,894.675 rows=618 loops=1)

  • Group Key: p.revision_uuid, pr.valid_range, pr.sequence
5. 1.752 1,907,891.420 ↑ 470,858.9 1,224 1

Sort (cost=17,080,115,375.99..17,081,556,204.24 rows=576,331,300 width=570) (actual time=1,907,891.280..1,907,891.420 rows=1,224 loops=1)

  • Sort Key: p.revision_uuid, pr.valid_range, pr.sequence DESC
  • Sort Method: quicksort Memory: 486kB
6. 2.727 1,907,889.668 ↑ 470,858.9 1,224 1

Nested Loop Left Join (cost=2.52..16,616,346,583.37 rows=576,331,300 width=570) (actual time=295,329.740..1,907,889.668 rows=1,224 loops=1)

  • Join Filter: (role.role_uuid = (((jsonb_array_elements_text(pr.roles)))::uuid))
  • Rows Removed by Join Filter: 35496
7. 0.277 1,907,856.341 ↑ 470,858.9 1,224 1

Nested Loop Left Join (cost=0.56..16,555,831,763.16 rows=576,331,300 width=529) (actual time=295,301.765..1,907,856.341 rows=1,224 loops=1)

8. 0.415 1,907,854.210 ↑ 9,325.7 618 1

Nested Loop Left Join (cost=0.56..16,531,236,824.93 rows=5,763,313 width=546) (actual time=295,301.736..1,907,854.210 rows=618 loops=1)

9. 257,579.060 1,907,851.941 ↑ 9,325.7 618 1

Seq Scan on persons p (cost=0.00..16,526,968,847.70 rows=5,763,313 width=485) (actual time=295,301.671..1,907,851.941 rows=618 loops=1)

  • Filter: (((organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid) AND (valid_range @> '2019-08-08 15:57:26-07'::timestamp with time zone) AND (is_visible IS TRUE) AND (((first_name)::text ~~* '%hpomi%'::text) OR ((last_name)::text ~~* '%hpomi%'::text) OR ((target_name)::text ~~* '%hpomi%'::text) OR ((web_login)::text ~~ '%hpomi%'::text))) OR ((((first_name)::text ~~* '%w%'::text) OR ((last_name)::text ~~* '%w%'::text) OR ((target_name)::text ~~* '%w%'::text) OR ((web_login)::text ~~ '%w%'::text)) AND ((person_uuid = '4b6fe547-24da-c5eb-cdea-1fa664f8053e'::uuid) OR (SubPlan 1) OR (SubPlan 2)) AND (NOT (alternatives: SubPlan 3 or hashed SubPlan 4))))
  • Rows Removed by Filter: 79838190
10.          

SubPlan (forSeq Scan)

11. 0.000 0.000 ↓ 0.0 0 12,400,259

Result (cost=0.56..3.58 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=12,400,259)

  • One-Time Filter: upper_inf(p.valid_range)
12. 1,414.560 1,414.560 ↓ 0.0 0 471,520

Index Scan using person_roles_org_seq_vrange_uidx on person_roles (cost=0.56..3.58 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=471,520)

  • 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))
  • Rows Removed by Filter: 1
13. 0.000 1,649,234.447 ↓ 0.0 0 12,400,259

Nested Loop (cost=6.07..198.00 rows=1 width=0) (actual time=0.133..0.133 rows=0 loops=12,400,259)

  • Join Filter: (rr.target_role_uuid = (((jsonb_array_elements_text(tpr.roles)))::uuid))
  • Rows Removed by Join Filter: 0
14. 24,811.988 1,649,234.447 ↓ 0.0 0 12,400,259

Nested Loop (cost=6.07..193.48 rows=1 width=49) (actual time=0.133..0.133 rows=0 loops=12,400,259)

15. 24,800.518 1,302,027.195 ↓ 13.0 13 12,400,259

Nested Loop (cost=5.50..189.88 rows=1 width=16) (actual time=0.011..0.105 rows=13 loops=12,400,259)

16. 37,200.777 37,200.777 ↑ 1.0 1 12,400,259

Index Scan using person_roles_org_seq_vrange_uidx on person_roles spr (cost=0.56..3.58 rows=1 width=33) (actual time=0.003..0.003 rows=1 loops=12,400,259)

  • 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. 272,805.698 1,240,025.900 ↓ 13.0 13 12,400,259

Hash Join (cost=4.94..186.29 rows=1 width=16) (actual time=0.008..0.100 rows=13 loops=12,400,259)

  • Hash Cond: (rr.source_role_uuid = (((jsonb_array_elements_text(spr.roles)))::uuid))
18. 930,019.425 930,019.425 ↓ 4.3 202 12,400,259

Index Scan using role_on_role_organization_uuid_idx on role_on_role rr (cost=0.42..181.59 rows=47 width=32) (actual time=0.003..0.075 rows=202 loops=12,400,259)

  • 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))
  • Rows Removed by Filter: 11
19. 12,400.259 37,200.777 ↑ 100.0 1 12,400,259

Hash (cost=3.27..3.27 rows=100 width=16) (actual time=0.003..0.003 rows=1 loops=12,400,259)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.000 24,800.518 ↑ 100.0 1 12,400,259

Result (cost=0.00..2.27 rows=100 width=16) (actual time=0.002..0.002 rows=1 loops=12,400,259)

21. 24,800.518 24,800.518 ↑ 100.0 1 12,400,259

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.001..0.002 rows=1 loops=12,400,259)

22. 0.000 0.000 ↑ 1.0 1 12,400,259

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=12,400,259)

23. 322,395.264 322,395.264 ↓ 0.0 0 161,197,632

Index Scan using person_roles_org_seq_vrange_uidx on person_roles tpr (cost=0.56..3.58 rows=1 width=33) (actual time=0.002..0.002 rows=0 loops=161,197,632)

  • Index Cond: ((organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid) AND (person_uuid = p.person_uuid))
  • Filter: upper_inf(valid_range)
24. 3.898 7.796 ↑ 100.0 1 3,898

Result (cost=0.00..2.27 rows=100 width=16) (actual time=0.002..0.002 rows=1 loops=3,898)

25. 3.898 3.898 ↑ 100.0 1 3,898

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.001..0.001 rows=1 loops=3,898)

26. 0.000 0.000 ↑ 1.0 1 3,898

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=3,898)

27. 0.000 0.000 ↓ 0.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) (never executed)

  • 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. 1,038.434 1,038.434 ↓ 0.0 0 1

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 time=1,038.434..1,038.434 rows=0 loops=1)

  • Index Cond: (organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid)
  • Filter: (is_super_admin AND upper_inf(valid_range))
  • Rows Removed by Filter: 3081
29. 1.854 1.854 ↑ 1.0 1 618

Index Scan using person_roles_org_seq_vrange_uidx on person_roles pr (cost=0.56..0.73 rows=1 width=77) (actual time=0.002..0.003 rows=1 loops=618)

  • Index Cond: ((organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid) AND (person_uuid = p.person_uuid))
  • Filter: upper_inf(valid_range)
30. 1.236 1.854 ↑ 50.0 2 618

Result (cost=0.00..2.27 rows=100 width=16) (actual time=0.002..0.003 rows=2 loops=618)

31. 0.618 0.618 ↑ 50.0 2 618

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.001..0.001 rows=2 loops=618)

32. 0.000 0.000 ↑ 1.0 1 618

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=618)

33. 2.626 30.600 ↓ 4.3 30 1,224

Materialize (cost=1.96..33.73 rows=7 width=57) (actual time=0.023..0.025 rows=30 loops=1,224)

34. 0.060 27.974 ↓ 4.3 30 1

Bitmap Heap Scan on roles role (cost=1.96..33.70 rows=7 width=57) (actual time=27.948..27.974 rows=30 loops=1)

  • Recheck Cond: (organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid)
  • Filter: upper_inf(valid_range)
  • Heap Blocks: exact=3
35. 27.914 27.914 ↓ 1.4 30 1

Bitmap Index Scan on roles_organization_uuid_idx (cost=0.00..1.96 rows=22 width=0) (actual time=27.914..27.914 rows=30 loops=1)

  • Index Cond: (organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid)
Planning time : 2.531 ms
Execution time : 1,907,896.272 ms