explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GX2b

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Limit (cost=17,496,505,725.34..17,496,505,725.44 rows=40 width=532) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Sort (cost=17,496,505,725.34..17,498,017,252.09 rows=604,610,700 width=532) (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=17,456,232,823.99..17,477,394,198.49 rows=604,610,700 width=532) (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) O
4. 0.000 0.000 ↓ 0.0

Unique (cost=17,456,232,823.99..17,459,255,877.49 rows=604,610,700 width=560) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Sort (cost=17,456,232,823.99..17,457,744,350.74 rows=604,610,700 width=560) (actual rows= loops=)

  • Sort Key: p_1.person_uuid
6. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=16,936,292,580.84..16,980,126,856.59 rows=604,610,700 width=560) (actual rows= loops=)

  • Group Key: p_1.revision_uuid, pr.valid_range, pr.sequence
7. 0.000 0.000 ↓ 0.0

Sort (cost=16,936,292,580.84..16,937,804,107.59 rows=604,610,700 width=569) (actual rows= loops=)

  • Sort Key: p_1.revision_uuid, pr.valid_range, pr.sequence
8. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=3.09..16,449,558,684.43 rows=604,610,700 width=569) (actual rows= loops=)

  • Join Filter: (role.role_uuid = (((jsonb_array_elements_text(pr.roles)))::uuid))
9. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.13..16,386,074,527.22 rows=604,610,700 width=528) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.13..16,360,272,765.60 rows=6,046,107 width=545) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Index Scan using persons_person_uuid_idx on persons p_1 (cost=0.57..16,355,830,867.93 rows=6,046,107 width=484) (actual rows= loops=)

  • Filter: (((organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid) AND (valid_range @> '2019-08-08 22:57:26+00'::timestamp with tim
12.          

SubPlan (forIndex Scan)

13. 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)
14. 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-1fa
  • Filter: (is_company_admin AND upper_inf(valid_range))
15. 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))
16. 0.000 0.000 ↓ 0.0

Nested Loop (cost=6.07..193.25 rows=1 width=49) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Nested Loop (cost=5.50..189.65 rows=1 width=16) (actual rows= loops=)

18. 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-c
  • Filter: upper_inf(valid_range)
19. 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))
20. 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))
21. 0.000 0.000 ↓ 0.0

Hash (cost=3.27..3.27 rows=100 width=16) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Result (cost=0.00..2.27 rows=100 width=16) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

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

25. 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)
26. 0.000 0.000 ↓ 0.0

Result (cost=0.00..2.27 rows=100 width=16) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

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

29. 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_1.person_uuid))
  • Filter: (is_super_admin AND upper_inf(valid_range))
30. 0.000 0.000 ↓ 0.0

Index Scan using person_roles_organization_uuid_idx on person_roles person_roles_2 (cost=0.56..2,543.46 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))
31. 0.000 0.000 ↓ 0.0

Index Scan using person_roles_org_seq_vrange_uidx on person_roles pr (cost=0.56..0.72 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)
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

Materialize (cost=1.96..33.73 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)