explain.depesz.com

PostgreSQL's explain analyze made readable

Result: j8tv

Settings
# exclusive inclusive rows x rows loops node
1. 0.028 5,708.639 ↑ 1.0 1 1

Aggregate (cost=227,086.40..227,086.41 rows=1 width=8) (actual time=5,708.639..5,708.639 rows=1 loops=1)

2.          

CTE pr

3. 24.640 5,474.234 ↑ 48.6 42,961 1

Merge Left Join (cost=130,892.99..164,447.56 rows=2,086,851 width=179) (actual time=5,351.010..5,474.234 rows=42,961 loops=1)

  • Merge Cond: (p_1.person_uuid = pr.person_uuid)
4.          

Initplan (for Merge Left Join)

5. 0.000 0.026 ↓ 0.0 0 1

Append (cost=0.56..3.58 rows=1 width=0) (actual time=0.026..0.026 rows=0 loops=1)

6. 0.026 0.026 ↓ 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.56..3.58 rows=1 width=0) (actual time=0.025..0.026 rows=0 loops=1)

  • Index Cond: ((organization_uuid = '08847962-07df-494c-8725-2b6ca10d0f42'::uuid) AND (person_uuid = '9d193fbd-3f85-49c8-aa25-147e8bf97f18'::uuid))
  • Filter: (is_company_admin AND upper_inf(valid_range))
  • Rows Removed by Filter: 1
7. 268.286 1,613.732 ↓ 1.8 33,900 1

Gather Merge (cost=52,979.90..55,186.50 rows=18,424 width=179) (actual time=1,521.776..1,613.732 rows=33,900 loops=1)

  • Workers Planned: 3
  • Params Evaluated: $0
  • Workers Launched: 3
8. 13.329 1,345.446 ↓ 1.4 8,475 4 / 4

Merge Left Join (cost=51,979.86..52,021.64 rows=5,943 width=179) (actual time=1,324.523..1,345.446 rows=8,475 loops=4)

  • Merge Cond: (p_1.person_uuid = tpr.person_uuid)
  • Filter: ((p_1.person_uuid = '9d193fbd-3f85-49c8-aa25-147e8bf97f18'::uuid) OR $0 OR (tpr.person_uuid IS NOT NULL))
  • Rows Removed by Filter: 158
9. 14.456 319.153 ↓ 1.3 7,651 4 / 4

Sort (cost=47,886.63..47,901.52 rows=5,958 width=179) (actual time=316.651..319.153 rows=7,651 loops=4)

  • Sort Key: p_1.person_uuid
  • Sort Method: quicksort Memory: 3,025kB
  • Worker 0: Sort Method: quicksort Memory: 2,076kB
  • Worker 1: Sort Method: quicksort Memory: 1,724kB
  • Worker 2: Sort Method: quicksort Memory: 2,269kB
10. 10.423 304.697 ↓ 1.3 7,651 4 / 4

Nested Loop Anti Join (cost=851.69..47,513.04 rows=5,958 width=179) (actual time=45.157..304.697 rows=7,651 loops=4)

11. 1.195 148.896 ↑ 1.6 7,652 4 / 4

Parallel Append (cost=851.13..14,774.75 rows=11,916 width=179) (actual time=45.056..148.896 rows=7,652 loops=4)

12. 133.756 147.701 ↑ 1.6 7,652 4 / 4

Parallel Bitmap Heap Scan on persons_org_part_31 p_1 (cost=851.13..14,715.17 rows=11,916 width=179) (actual time=45.054..147.701 rows=7,652 loops=4)

  • Recheck Cond: (valid_range @> '2019-10-01 16:01:17+00'::timestamp with time zone)
  • Filter: ((is_visible IS TRUE) AND (organization_uuid = '08847962-07df-494c-8725-2b6ca10d0f42'::uuid))
  • Heap Blocks: exact=3,897
13. 13.945 13.945 ↓ 1.4 50,662 1 / 4

Bitmap Index Scan on persons_org_part_31_valid_range_idx (cost=0.00..841.90 rows=37,415 width=0) (actual time=55.780..55.780 rows=50,662 loops=1)

  • Index Cond: (valid_range @> '2019-10-01 16:01:17+00'::timestamp with time zone)
14. 0.000 145.379 ↓ 0.0 0 30,606 / 4

Append (cost=0.56..2.74 rows=1 width=16) (actual time=0.019..0.019 rows=0 loops=30,606)

15. 145.379 145.379 ↓ 0.0 0 30,606 / 4

Index Scan using person_roles_org_part_default_organization_uuid_person_uuid_idx on person_roles_org_part_default adm (cost=0.56..2.73 rows=1 width=16) (actual time=0.019..0.019 rows=0 loops=30,606)

  • Index Cond: ((organization_uuid = '08847962-07df-494c-8725-2b6ca10d0f42'::uuid) AND (person_uuid = p_1.person_uuid))
  • Filter: (is_super_admin AND upper_inf(valid_range))
  • Rows Removed by Filter: 2
16. 35.412 1,012.964 ↓ 1,579.2 36,321 4 / 4

Sort (cost=4,093.24..4,093.30 rows=23 width=16) (actual time=1,007.853..1,012.964 rows=36,321 loops=4)

  • Sort Key: tpr.person_uuid
  • Sort Method: quicksort Memory: 3,239kB
  • Worker 0: Sort Method: quicksort Memory: 3,239kB
  • Worker 1: Sort Method: quicksort Memory: 3,239kB
  • Worker 2: Sort Method: quicksort Memory: 3,239kB
17. 6.002 977.552 ↓ 1,579.3 36,325 4 / 4

Nested Loop (cost=73.18..4,092.72 rows=23 width=16) (actual time=23.202..977.552 rows=36,325 loops=4)

18. 2.755 6.410 ↓ 41.0 41 4 / 4

Nested Loop (cost=47.23..3,870.51 rows=1 width=16) (actual time=0.735..6.410 rows=41 loops=4)

  • Join Filter: (spr.roles @> ((('["'::text || (rr.source_role_uuid)::text) || '"]'::text))::jsonb)
  • Rows Removed by Join Filter: 1,564
19. 0.002 0.041 ↑ 1.0 1 4 / 4

Append (cost=0.56..3.58 rows=1 width=46) (actual time=0.038..0.041 rows=1 loops=4)

20. 0.039 0.039 ↑ 1.0 1 4 / 4

Index Scan using person_roles_org_part_default_organization_uuid_person_uuid_idx on person_roles_org_part_default spr (cost=0.56..3.58 rows=1 width=46) (actual time=0.037..0.039 rows=1 loops=4)

  • Index Cond: ((organization_uuid = '08847962-07df-494c-8725-2b6ca10d0f42'::uuid) AND (person_uuid = '9d193fbd-3f85-49c8-aa25-147e8bf97f18'::uuid))
  • Filter: upper_inf(valid_range)
21. 3.272 3.614 ↓ 1.5 1,605 4 / 4

Bitmap Heap Scan on role_on_role rr (cost=46.68..3,836.52 rows=1,106 width=32) (actual time=0.381..3.614 rows=1,605 loops=4)

  • Recheck Cond: (organization_uuid = '08847962-07df-494c-8725-2b6ca10d0f42'::uuid)
  • Filter: ((can_edit_person OR can_view_person OR can_use_person) AND upper_inf(valid_range))
  • Rows Removed by Filter: 1,627
  • Heap Blocks: exact=162
22. 0.342 0.342 ↑ 1.0 3,232 4 / 4

Bitmap Index Scan on role_on_role_organization_uuid_idx (cost=0.00..46.40 rows=3,330 width=0) (actual time=0.342..0.342 rows=3,232 loops=4)

  • Index Cond: (organization_uuid = '08847962-07df-494c-8725-2b6ca10d0f42'::uuid)
23. 7.667 965.140 ↓ 38.5 886 164 / 4

Append (cost=25.95..221.97 rows=23 width=62) (actual time=16.040..23.540 rows=886 loops=164)

24. 306.270 957.473 ↓ 38.5 886 164 / 4

Bitmap Heap Scan on person_roles_org_part_default tpr (cost=25.95..221.86 rows=23 width=62) (actual time=15.976..23.353 rows=886 loops=164)

  • Recheck Cond: (roles @> ((('["'::text || (rr.target_role_uuid)::text) || '"]'::text))::jsonb)
  • Filter: (upper_inf(valid_range) AND (organization_uuid = '08847962-07df-494c-8725-2b6ca10d0f42'::uuid))
  • Rows Removed by Filter: 814
  • Heap Blocks: exact=11,939
25. 651.203 651.203 ↑ 1.9 1,704 164 / 4

Bitmap Index Scan on person_roles_org_part_default_roles_idx (cost=0.00..25.94 rows=3,241 width=0) (actual time=15.883..15.883 rows=1,704 loops=164)

  • Index Cond: (roles @> ((('["'::text || (rr.target_role_uuid)::text) || '"]'::text))::jsonb)
26. 79.475 3,835.836 ↓ 2.0 46,172 1

Sort (cost=77,909.50..77,966.13 rows=22,653 width=16) (actual time=3,829.221..3,835.836 rows=46,172 loops=1)

  • Sort Key: pr.person_uuid
  • Sort Method: quicksort Memory: 3,276kB
27. 3,451.042 3,756.361 ↓ 1.6 37,113 1

Nested Loop Left Join (cost=1,330.01..76,270.85 rows=22,653 width=16) (actual time=8.817..3,756.361 rows=37,113 loops=1)

  • Join Filter: (pr.roles @> ((('["'::text || (role.role_uuid)::text) || '"]'::text))::jsonb)
  • Rows Removed by Join Filter: 1,679,149
28. 6.015 140.369 ↓ 1.5 32,990 1

Append (cost=1,327.40..51,660.78 rows=22,653 width=62) (actual time=8.569..140.369 rows=32,990 loops=1)

29. 127.141 134.354 ↓ 1.5 32,990 1

Bitmap Heap Scan on person_roles_org_part_default pr (cost=1,327.40..51,547.52 rows=22,653 width=62) (actual time=8.568..134.354 rows=32,990 loops=1)

  • Recheck Cond: (organization_uuid = '08847962-07df-494c-8725-2b6ca10d0f42'::uuid)
  • Filter: upper_inf(valid_range)
  • Rows Removed by Filter: 32,799
  • Heap Blocks: exact=7,646
30. 7.213 7.213 ↑ 1.0 65,962 1

Bitmap Index Scan on person_roles_org_part_default_pkey (cost=0.00..1,321.74 rows=67,958 width=0) (actual time=7.213..7.213 rows=65,962 loops=1)

  • Index Cond: (organization_uuid = '08847962-07df-494c-8725-2b6ca10d0f42'::uuid)
31. 164.824 164.950 ↓ 1.4 52 32,990

Materialize (cost=2.61..144.92 rows=36 width=16) (actual time=0.000..0.005 rows=52 loops=32,990)

32. 0.101 0.126 ↓ 1.4 52 1

Bitmap Heap Scan on roles role (cost=2.61..144.74 rows=36 width=16) (actual time=0.034..0.126 rows=52 loops=1)

  • Recheck Cond: (organization_uuid = '08847962-07df-494c-8725-2b6ca10d0f42'::uuid)
  • Filter: upper_inf(valid_range)
  • Rows Removed by Filter: 52
  • Heap Blocks: exact=27
33. 0.025 0.025 ↑ 1.0 104 1

Bitmap Index Scan on roles_organization_uuid_idx (cost=0.00..2.60 rows=108 width=0) (actual time=0.025..0.025 rows=104 loops=1)

  • Index Cond: (organization_uuid = '08847962-07df-494c-8725-2b6ca10d0f42'::uuid)
34. 5,708.611 5,708.611 ↓ 0.0 0 1

CTE Scan on pr p (cost=0.00..62,605.53 rows=13,324 width=16) (actual time=5,708.611..5,708.611 rows=0 loops=1)

  • Filter: (((first_name)::text ~~* '%19080%'::text) OR ((last_name)::text ~~* '%19080%'::text) OR ((target_name)::text ~~* '%19080%'::text) OR ((web_login)::text ~~ '%19080%'::text))
  • Rows Removed by Filter: 42,961
Planning time : 14.584 ms
Execution time : 5,720.166 ms