explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yYU8

Settings
# exclusive inclusive rows x rows loops node
1. 4.764 3,838.149 ↑ 1.0 1 1

Aggregate (cost=240,087.16..240,087.17 rows=1 width=8) (actual time=3,838.148..3,838.149 rows=1 loops=1)

2.          

Initplan (for Aggregate)

3. 0.001 0.028 ↓ 0.0 0 1

Append (cost=0.42..4.13 rows=1 width=0) (actual time=0.028..0.028 rows=0 loops=1)

4. 0.027 0.027 ↓ 0.0 0 1

Index Scan using person_roles_org_part_20_organization_uuid_person_uuid_sequ_idx on person_roles_org_part_20 (cost=0.42..4.12 rows=1 width=0) (actual time=0.027..0.027 rows=0 loops=1)

  • Index Cond: ((organization_uuid = '57746a44-45a2-4b38-aeda-85b411de685b'::uuid) AND (person_uuid = '8ba6c978-5e01-4ca2-8496-bde271d81b72'::uuid))
  • Filter: (is_company_admin AND upper_inf(valid_range))
  • Rows Removed by Filter: 2
5. 16.908 3,833.357 ↑ 276.9 20,055 1

Merge Left Join (cost=141,269.53..226,201.46 rows=5,552,630 width=16) (actual time=3,766.455..3,833.357 rows=20,055 loops=1)

  • Merge Cond: (p.person_uuid = pr.person_uuid)
6. 87.089 740.975 ↓ 1.4 18,237 1

Gather Merge (cost=87,204.57..88,814.12 rows=13,171 width=16) (actual time=709.960..740.975 rows=18,237 loops=1)

  • Workers Planned: 4
  • Params Evaluated: $0
  • Workers Launched: 4
7. 10.860 653.886 ↓ 1.1 3,647 5 / 5

Merge Left Join (cost=86,204.51..86,245.27 rows=3,293 width=16) (actual time=634.691..653.886 rows=3,647 loops=5)

  • Merge Cond: (p.person_uuid = tpr.person_uuid)
  • Filter: ((p.person_uuid = '8ba6c978-5e01-4ca2-8496-bde271d81b72'::uuid) OR $0 OR (tpr.person_uuid IS NOT NULL))
  • Rows Removed by Filter: 199
8. 2.827 258.268 ↓ 1.1 3,787 5 / 5

Sort (cost=85,844.67..85,852.92 rows=3,301 width=16) (actual time=257.569..258.268 rows=3,787 loops=5)

  • Sort Key: p.person_uuid
  • Sort Method: quicksort Memory: 450kB
  • Worker 0: Sort Method: quicksort Memory: 264kB
  • Worker 1: Sort Method: quicksort Memory: 256kB
  • Worker 2: Sort Method: quicksort Memory: 229kB
  • Worker 3: Sort Method: quicksort Memory: 267kB
9. 1.419 255.441 ↓ 1.1 3,787 5 / 5

Nested Loop Anti Join (cost=2,896.13..85,651.75 rows=3,301 width=16) (actual time=28.951..255.441 rows=3,787 loops=5)

10. 0.451 193.436 ↑ 1.7 3,787 5 / 5

Parallel Append (cost=2,895.71..79,585.16 rows=6,602 width=16) (actual time=28.863..193.436 rows=3,787 loops=5)

11. 187.346 192.985 ↑ 1.7 3,787 5 / 5

Parallel Bitmap Heap Scan on persons_org_part_13 p (cost=2,895.71..79,552.15 rows=6,602 width=16) (actual time=28.861..192.985 rows=3,787 loops=5)

  • Recheck Cond: (valid_range @> '2019-09-18 21:46:50+00'::timestamp with time zone)
  • Filter: ((is_visible IS TRUE) AND (organization_uuid = '57746a44-45a2-4b38-aeda-85b411de685b'::uuid) AND (((first_name)::text ~~* '%is%'::text) OR ((last_name)::text ~~* '%is%'::text) OR ((target_name)::text ~~* '%is%'::text) OR ((web_login)::text ~~ '%is%'::text) OR ((first_name)::text ~~* '%10%'::text) OR ((last_name)::text ~~* '%10%'::text) OR ((target_name)::text ~~* '%10%'::text) OR ((web_login)::text ~~ '%10%'::text)))
  • Rows Removed by Filter: 19,802
  • Heap Blocks: exact=14,031
12. 5.639 5.639 ↑ 1.1 117,941 1 / 5

Bitmap Index Scan on persons_org_part_13_valid_range_idx (cost=0.00..2,889.11 rows=130,360 width=0) (actual time=28.197..28.197 rows=117,941 loops=1)

  • Index Cond: (valid_range @> '2019-09-18 21:46:50+00'::timestamp with time zone)
13. 0.000 60.586 ↓ 0.0 0 18,933 / 5

Append (cost=0.42..0.91 rows=1 width=16) (actual time=0.016..0.016 rows=0 loops=18,933)

14. 60.586 60.586 ↓ 0.0 0 18,933 / 5

Index Scan using person_roles_org_part_20_organization_uuid_person_uuid_sequ_idx on person_roles_org_part_20 adm (cost=0.42..0.90 rows=1 width=16) (actual time=0.016..0.016 rows=0 loops=18,933)

  • Index Cond: ((organization_uuid = '57746a44-45a2-4b38-aeda-85b411de685b'::uuid) AND (person_uuid = p.person_uuid))
  • Filter: (is_super_admin AND upper_inf(valid_range))
  • Rows Removed by Filter: 2
15. 49.199 384.758 ↓ 1,380.4 115,954 5 / 5

Sort (cost=359.84..360.05 rows=84 width=16) (actual time=377.102..384.758 rows=115,954 loops=5)

  • Sort Key: tpr.person_uuid
  • Sort Method: quicksort Memory: 8,167kB
  • Worker 0: Sort Method: quicksort Memory: 8,167kB
  • Worker 1: Sort Method: quicksort Memory: 8,167kB
  • Worker 2: Sort Method: quicksort Memory: 8,167kB
  • Worker 3: Sort Method: quicksort Memory: 8,167kB
16. 10.182 335.559 ↓ 1,380.6 115,974 5 / 5

Nested Loop (cost=4.15..357.16 rows=84 width=16) (actual time=162.485..335.559 rows=115,974 loops=5)

17. 0.229 1.752 ↓ 15.0 15 5 / 5

Nested Loop (cost=0.84..193.13 rows=1 width=16) (actual time=0.268..1.752 rows=15 loops=5)

  • Join Filter: (spr.roles @> ((('["'::text || (rr.source_role_uuid)::text) || '"]'::text))::jsonb)
  • Rows Removed by Join Filter: 179
18. 0.002 0.031 ↑ 1.0 1 5 / 5

Append (cost=0.42..4.13 rows=1 width=42) (actual time=0.028..0.031 rows=1 loops=5)

19. 0.029 0.029 ↑ 1.0 1 5 / 5

Index Scan using person_roles_org_part_20_organization_uuid_person_uuid_sequ_idx on person_roles_org_part_20 spr (cost=0.42..4.12 rows=1 width=42) (actual time=0.028..0.029 rows=1 loops=5)

  • Index Cond: ((organization_uuid = '57746a44-45a2-4b38-aeda-85b411de685b'::uuid) AND (person_uuid = '8ba6c978-5e01-4ca2-8496-bde271d81b72'::uuid))
  • Filter: upper_inf(valid_range)
  • Rows Removed by Filter: 1
20. 1.492 1.492 ↓ 4.1 194 5 / 5

Index Scan using role_on_role_organization_uuid_idx on role_on_role rr (cost=0.42..187.71 rows=47 width=32) (actual time=0.046..1.492 rows=194 loops=5)

  • Index Cond: (organization_uuid = '57746a44-45a2-4b38-aeda-85b411de685b'::uuid)
  • Filter: ((can_edit_person OR can_view_person OR can_use_person) AND upper_inf(valid_range))
  • Rows Removed by Filter: 216
21. 8.895 323.625 ↓ 92.0 7,732 75 / 5

Append (cost=3.31..163.19 rows=84 width=58) (actual time=11.207..21.575 rows=7,732 loops=75)

22. 294.210 314.730 ↓ 92.0 7,732 75 / 5

Bitmap Heap Scan on person_roles_org_part_20 tpr (cost=3.31..162.77 rows=84 width=58) (actual time=11.202..20.982 rows=7,732 loops=75)

  • Recheck Cond: (roles @> ((('["'::text || (rr.target_role_uuid)::text) || '"]'::text))::jsonb)
  • Filter: (upper_inf(valid_range) AND (organization_uuid = '57746a44-45a2-4b38-aeda-85b411de685b'::uuid))
  • Rows Removed by Filter: 8,032
  • Heap Blocks: exact=8,985
23. 20.520 20.520 ↓ 62.3 15,764 75 / 5

Bitmap Index Scan on person_roles_org_part_20_roles_idx (cost=0.00..3.28 rows=253 width=0) (actual time=1.368..1.368 rows=15,764 loops=75)

  • Index Cond: (roles @> ((('["'::text || (rr.target_role_uuid)::text) || '"]'::text))::jsonb)
24. 90.444 3,075.474 ↓ 1.5 130,329 1

Sort (cost=54,064.96..54,275.75 rows=84,316 width=16) (actual time=3,056.484..3,075.474 rows=130,329 loops=1)

  • Sort Key: pr.person_uuid
  • Sort Method: external sort Disk: 3,784kB
25. 2,653.920 2,985.030 ↓ 1.5 128,608 1

Nested Loop Left Join (cost=2.12..47,166.43 rows=84,316 width=16) (actual time=0.128..2,985.030 rows=128,608 loops=1)

  • Join Filter: (pr.roles @> ((('["'::text || (role.role_uuid)::text) || '"]'::text))::jsonb)
  • Rows Removed by Join Filter: 3,056,299
26. 9.162 77.296 ↓ 1.5 126,907 1

Append (cost=0.00..9,159.82 rows=84,316 width=58) (actual time=0.024..77.296 rows=126,907 loops=1)

27. 68.134 68.134 ↓ 1.5 126,907 1

Seq Scan on person_roles_org_part_20 pr (cost=0.00..8,738.24 rows=84,316 width=58) (actual time=0.023..68.134 rows=126,907 loops=1)

  • Filter: (upper_inf(valid_range) AND (organization_uuid = '57746a44-45a2-4b38-aeda-85b411de685b'::uuid))
  • Rows Removed by Filter: 126,042
28. 253.750 253.814 ↓ 1.7 25 126,907

Materialize (cost=2.12..64.45 rows=15 width=16) (actual time=0.000..0.002 rows=25 loops=126,907)

29. 0.038 0.064 ↓ 1.7 25 1

Bitmap Heap Scan on roles role (cost=2.12..64.37 rows=15 width=16) (actual time=0.036..0.064 rows=25 loops=1)

  • Recheck Cond: (organization_uuid = '57746a44-45a2-4b38-aeda-85b411de685b'::uuid)
  • Filter: upper_inf(valid_range)
  • Rows Removed by Filter: 25
  • Heap Blocks: exact=7
30. 0.026 0.026 ↓ 1.1 50 1

Bitmap Index Scan on roles_organization_uuid_idx (cost=0.00..2.12 rows=44 width=0) (actual time=0.026..0.026 rows=50 loops=1)

  • Index Cond: (organization_uuid = '57746a44-45a2-4b38-aeda-85b411de685b'::uuid)
Planning time : 95.501 ms
Execution time : 3,841.350 ms