explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jOGw

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 3,064.891 ↑ 1.0 1 1

Aggregate (cost=414,296.85..414,296.86 rows=1 width=8) (actual time=3,064.890..3,064.891 rows=1 loops=1)

2.          

Initplan (for Aggregate)

3. 0.000 0.035 ↓ 0.0 0 1

Append (cost=0.55..3.58 rows=1 width=0) (actual time=0.035..0.035 rows=0 loops=1)

4. 0.035 0.035 ↓ 0.0 0 1

Index Scan using person_roles_org_part_default_organization_uuid_person_uuid_idx on person_roles_org_part_default (cost=0.55..3.58 rows=1 width=0) (actual time=0.034..0.035 rows=0 loops=1)

  • Index Cond: ((organization_uuid = '1501d5f9-0e2a-4435-8ea2-7b79849db7ce'::uuid) AND (person_uuid = '75ead78d-a964-473f-8950-08cd51b135be'::uuid))
  • Filter: (is_company_admin AND upper_inf(valid_range))
  • Rows Removed by Filter: 2
5. 5.534 3,064.848 ↑ 5,740,096.5 2 1

Merge Left Join (cost=208,950.36..385,592.79 rows=11,480,193 width=16) (actual time=3,064.842..3,064.848 rows=2 loops=1)

  • Merge Cond: (p.person_uuid = pr.person_uuid)
6. 22.615 245.621 ↑ 35,726.0 1 1

Gather Merge (cost=132,911.26..137,261.48 rows=35,726 width=16) (actual time=244.839..245.621 rows=1 loops=1)

  • Workers Planned: 4
  • Params Evaluated: $0
  • Workers Launched: 4
7. 0.301 223.006 ↓ 0.0 0 5 / 5

Merge Left Join (cost=131,911.21..132,006.11 rows=8,932 width=16) (actual time=222.934..223.006 rows=0 loops=5)

  • Merge Cond: (p.person_uuid = tpr.person_uuid)
  • Filter: ((p.person_uuid = '75ead78d-a964-473f-8950-08cd51b135be'::uuid) OR $0 OR (tpr.person_uuid IS NOT NULL))
  • Rows Removed by Filter: 1,817
8. 2.367 222.639 ↑ 4.9 1,817 5 / 5

Sort (cost=87,788.61..87,810.99 rows=8,954 width=16) (actual time=222.309..222.639 rows=1,817 loops=5)

  • Sort Key: p.person_uuid
  • Sort Method: quicksort Memory: 141kB
  • Worker 0: Sort Method: quicksort Memory: 129kB
  • Worker 1: Sort Method: quicksort Memory: 195kB
  • Worker 2: Sort Method: quicksort Memory: 110kB
  • Worker 3: Sort Method: quicksort Memory: 142kB
9. 1.919 220.272 ↑ 4.9 1,817 5 / 5

Nested Loop Anti Join (cost=3,708.37..87,200.85 rows=8,954 width=16) (actual time=16.445..220.272 rows=1,817 loops=5)

10. 0.220 160.203 ↑ 9.9 1,817 5 / 5

Parallel Append (cost=3,707.82..55,829.93 rows=17,908 width=16) (actual time=16.325..160.203 rows=1,817 loops=5)

11. 155.001 159.983 ↑ 9.9 1,817 5 / 5

Parallel Bitmap Heap Scan on persons_org_part_default p (cost=3,707.82..55,740.39 rows=17,908 width=16) (actual time=16.322..159.983 rows=1,817 loops=5)

  • Recheck Cond: (organization_uuid = '1501d5f9-0e2a-4435-8ea2-7b79849db7ce'::uuid)
  • Filter: ((is_visible IS TRUE) AND (valid_range @> '2019-08-22 12:01:14+00'::timestamp with time zone) AND (((first_name)::text ~~* '%st%'::text) OR ((last_name)::text ~~* '%st%'::text) OR ((target_name)::text ~~* '%st%'::text) OR ((web_login)::text ~~ '%st%'::text)))
  • Rows Removed by Filter: 37,818
  • Heap Blocks: exact=2,434
12. 4.982 4.982 ↓ 1.0 198,176 1 / 5

Bitmap Index Scan on persons_org_part_default_pkey (cost=0.00..3,689.91 rows=190,331 width=0) (actual time=24.909..24.910 rows=198,176 loops=1)

  • Index Cond: (organization_uuid = '1501d5f9-0e2a-4435-8ea2-7b79849db7ce'::uuid)
13. 0.000 58.150 ↓ 0.0 0 9,086 / 5

Append (cost=0.55..1.74 rows=1 width=16) (actual time=0.032..0.032 rows=0 loops=9,086)

14. 58.150 58.150 ↓ 0.0 0 9,086 / 5

Index Scan using person_roles_org_part_default_organization_uuid_person_uuid_idx on person_roles_org_part_default adm (cost=0.55..1.74 rows=1 width=16) (actual time=0.032..0.032 rows=0 loops=9,086)

  • Index Cond: ((organization_uuid = '1501d5f9-0e2a-4435-8ea2-7b79849db7ce'::uuid) AND (person_uuid = p.person_uuid))
  • Filter: (is_super_admin AND upper_inf(valid_range))
  • Rows Removed by Filter: 2
15. 0.026 0.066 ↓ 0.0 0 5 / 5

Sort (cost=44,122.60..44,122.76 rows=64 width=16) (actual time=0.066..0.066 rows=0 loops=5)

  • Sort Key: tpr.person_uuid
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 25kB
  • Worker 2: Sort Method: quicksort Memory: 25kB
  • Worker 3: Sort Method: quicksort Memory: 25kB
16. 0.002 0.040 ↓ 0.0 0 5 / 5

Nested Loop (cost=3,754.87..44,120.68 rows=64 width=16) (actual time=0.040..0.040 rows=0 loops=5)

  • Join Filter: (tpr.roles @> ((('["'::text || (rr.target_role_uuid)::text) || '"]'::text))::jsonb)
17. 0.001 0.038 ↓ 0.0 0 5 / 5

Nested Loop (cost=7.84..432.86 rows=1 width=16) (actual time=0.038..0.038 rows=0 loops=5)

  • Join Filter: (spr.roles @> ((('["'::text || (rr.source_role_uuid)::text) || '"]'::text))::jsonb)
18. 0.002 0.037 ↓ 0.0 0 5 / 5

Append (cost=0.55..3.58 rows=1 width=70) (actual time=0.037..0.037 rows=0 loops=5)

19. 0.035 0.035 ↓ 0.0 0 5 / 5

Index Scan using person_roles_org_part_default_organization_uuid_person_uuid_idx on person_roles_org_part_default spr (cost=0.55..3.58 rows=1 width=70) (actual time=0.035..0.035 rows=0 loops=5)

  • Index Cond: ((organization_uuid = '1501d5f9-0e2a-4435-8ea2-7b79849db7ce'::uuid) AND (person_uuid = '981387e3-9d1a-4d83-bd44-8866e911710b'::uuid))
  • Filter: upper_inf(valid_range)
20. 0.000 0.000 ↓ 0.0 0 / 5

Bitmap Heap Scan on role_on_role rr (cost=7.29..426.48 rows=102 width=32) (never executed)

  • Recheck Cond: (organization_uuid = '1501d5f9-0e2a-4435-8ea2-7b79849db7ce'::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 0 / 5

Bitmap Index Scan on role_on_role_organization_uuid_idx (cost=0.00..7.26 rows=312 width=0) (never executed)

  • Index Cond: (organization_uuid = '1501d5f9-0e2a-4435-8ea2-7b79849db7ce'::uuid)
22. 0.000 0.000 ↓ 0.0 0 / 5

Append (cost=3,747.03..41,920.45 rows=64,268 width=86) (never executed)

23. 0.000 0.000 ↓ 0.0 0 / 5

Bitmap Heap Scan on person_roles_org_part_default tpr (cost=3,747.03..41,599.11 rows=64,268 width=86) (never executed)

  • Recheck Cond: (organization_uuid = '1501d5f9-0e2a-4435-8ea2-7b79849db7ce'::uuid)
  • Filter: upper_inf(valid_range)
24. 0.000 0.000 ↓ 0.0 0 / 5

Bitmap Index Scan on person_roles_org_part_default_pkey (cost=0.00..3,730.96 rows=192,805 width=0) (never executed)

  • Index Cond: (organization_uuid = '1501d5f9-0e2a-4435-8ea2-7b79849db7ce'::uuid)
25. 124.550 2,813.693 ↓ 1.4 91,424 1

Sort (cost=76,039.09..76,199.76 rows=64,268 width=16) (actual time=2,803.993..2,813.693 rows=91,424 loops=1)

  • Sort Key: pr.person_uuid
  • Sort Method: external sort Disk: 5,824kB
26. 2,474.799 2,689.143 ↓ 3.1 198,174 1

Nested Loop Left Join (cost=3,749.16..70,906.71 rows=64,268 width=16) (actual time=20.099..2,689.143 rows=198,174 loops=1)

  • Join Filter: (pr.roles @> ((('["'::text || (role.role_uuid)::text) || '"]'::text))::jsonb)
  • Rows Removed by Join Filter: 2,477,202
27. 8.069 115.256 ↓ 1.5 99,088 1

Append (cost=3,747.03..41,920.45 rows=64,268 width=86) (actual time=20.006..115.256 rows=99,088 loops=1)

28. 88.361 107.187 ↓ 1.5 99,088 1

Bitmap Heap Scan on person_roles_org_part_default pr (cost=3,747.03..41,599.11 rows=64,268 width=86) (actual time=20.003..107.187 rows=99,088 loops=1)

  • Recheck Cond: (organization_uuid = '1501d5f9-0e2a-4435-8ea2-7b79849db7ce'::uuid)
  • Filter: upper_inf(valid_range)
  • Rows Removed by Filter: 99,088
  • Heap Blocks: exact=7,891
29. 18.826 18.826 ↓ 1.0 198,176 1

Bitmap Index Scan on person_roles_org_part_default_pkey (cost=0.00..3,730.96 rows=192,805 width=0) (actual time=18.825..18.826 rows=198,176 loops=1)

  • Index Cond: (organization_uuid = '1501d5f9-0e2a-4435-8ea2-7b79849db7ce'::uuid)
30. 99.034 99.088 ↓ 1.8 27 99,088

Materialize (cost=2.13..65.70 rows=15 width=16) (actual time=0.000..0.001 rows=27 loops=99,088)

31. 0.034 0.054 ↓ 1.8 27 1

Bitmap Heap Scan on roles role (cost=2.13..65.63 rows=15 width=16) (actual time=0.038..0.054 rows=27 loops=1)

  • Recheck Cond: (organization_uuid = '1501d5f9-0e2a-4435-8ea2-7b79849db7ce'::uuid)
  • Filter: upper_inf(valid_range)
  • Rows Removed by Filter: 27
  • Heap Blocks: exact=5
32. 0.020 0.020 ↓ 1.2 54 1

Bitmap Index Scan on roles_organization_uuid_idx (cost=0.00..2.13 rows=45 width=0) (actual time=0.020..0.020 rows=54 loops=1)

  • Index Cond: (organization_uuid = '1501d5f9-0e2a-4435-8ea2-7b79849db7ce'::uuid)
Planning time : 2.807 ms
Execution time : 3,067.793 ms