explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7riu

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 534,177.248 ↓ 0.0 0 1

Limit (cost=1,446,969,536.06..1,446,969,536.16 rows=40 width=221) (actual time=534,177.247..534,177.248 rows=0 loops=1)

2. 0.071 534,177.244 ↓ 0.0 0 1

Sort (cost=1,446,969,536.06..1,446,992,053.32 rows=9,006,907 width=221) (actual time=534,177.244..534,177.244 rows=0 loops=1)

  • Sort Key: p.uuid, p.first_name, p.last_name, p.valid_from DESC, p.sequence DESC
  • Sort Method: quicksort Memory: 25kB
3. 214.331 534,177.173 ↓ 0.0 0 1

Subquery Scan on p (cost=1,411,417,049.06..1,446,684,830.96 rows=9,006,907 width=221) (actual time=534,177.173..534,177.173 rows=0 loops=1)

  • Filter: (((p.first_name)::text ~~* '%19080%'::text) OR ((p.last_name)::text ~~* '%19080%'::text) OR ((p.target_name)::text ~~* '%19080%'::text) OR ((p.web_login)::text ~~ '%19080%'::text))
  • Rows Removed by Filter: 29,973
4. 2,331.787 533,962.842 ↑ 47,066.1 29,973 1

Unique (cost=1,411,417,049.06..1,418,470,605.44 rows=1,410,711,276 width=246) (actual time=522,548.032..533,962.842 rows=29,973 loops=1)

5.          

Initplan (for Unique)

6. 0.002 22.616 ↓ 0.0 0 1

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

7. 22.614 22.614 ↓ 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=22.614..22.614 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
8. 90,896.805 531,608.439 ↑ 95.5 14,776,689 1

Sort (cost=1,411,417,045.48..1,414,943,823.67 rows=1,410,711,276 width=246) (actual time=522,548.029..531,608.439 rows=14,776,689 loops=1)

  • Sort Key: p_1.person_uuid
  • Sort Method: external merge Disk: 6,057,768kB
9. 113,024.552 440,711.634 ↑ 95.5 14,776,689 1

GroupAggregate (cost=666,142,504.78..775,472,628.67 rows=1,410,711,276 width=246) (actual time=310,529.244..440,711.634 rows=14,776,689 loops=1)

  • Group Key: p_1.organization_uuid, p_1.revision_uuid, pr.valid_range, pr.sequence, s.name
10. 301,284.435 327,687.082 ↑ 66.6 21,179,773 1

Sort (cost=666,142,504.78..669,669,282.97 rows=1,410,711,276 width=255) (actual time=310,529.063..327,687.082 rows=21,179,773 loops=1)

  • Sort Key: p_1.revision_uuid, pr.valid_range, pr.sequence, s.name
  • Sort Method: external merge Disk: 5,820,048kB
11. 7,280.634 26,402.647 ↑ 66.6 21,179,773 1

Nested Loop Left Join (cost=130,917.76..17,799,255.97 rows=1,410,711,276 width=255) (actual time=16,220.830..26,402.647 rows=21,179,773 loops=1)

12. 130.615 16,157.704 ↑ 48.6 42,961 1

Merge Left Join (cost=130,889.41..164,443.97 rows=2,086,851 width=244) (actual time=15,783.528..16,157.704 rows=42,961 loops=1)

  • Merge Cond: (p_1.person_uuid = pr.person_uuid)
13. 231.937 11,877.884 ↓ 1.8 33,900 1

Gather Merge (cost=52,979.90..55,186.50 rows=18,424 width=162) (actual time=11,656.991..11,877.884 rows=33,900 loops=1)

  • Workers Planned: 3
  • Params Evaluated: $0
  • Workers Launched: 3
14. 21.796 11,645.947 ↓ 1.4 8,475 4 / 4

Merge Left Join (cost=51,979.86..52,021.64 rows=5,943 width=162) (actual time=11,612.599..11,645.947 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
15. 22.919 8,214.943 ↓ 1.3 7,651 4 / 4

Sort (cost=47,886.63..47,901.52 rows=5,958 width=162) (actual time=8,210.225..8,214.943 rows=7,651 loops=4)

  • Sort Key: p_1.person_uuid
  • Sort Method: quicksort Memory: 1,924kB
  • Worker 0: Sort Method: quicksort Memory: 2,247kB
  • Worker 1: Sort Method: quicksort Memory: 2,609kB
  • Worker 2: Sort Method: quicksort Memory: 2,311kB
16. 12.473 8,192.024 ↓ 1.3 7,651 4 / 4

Nested Loop Anti Join (cost=851.69..47,513.04 rows=5,958 width=162) (actual time=4,609.098..8,192.024 rows=7,651 loops=4)

17. 1.535 4,958.269 ↑ 1.6 7,652 4 / 4

Parallel Append (cost=851.13..14,774.75 rows=11,916 width=162) (actual time=4,569.342..4,958.269 rows=7,652 loops=4)

18. 3,821.308 4,956.734 ↑ 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=162) (actual time=4,569.339..4,956.734 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=2,665
19. 1,135.427 1,135.427 ↓ 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=4,541.706..4,541.706 rows=50,662 loops=1)

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

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

21. 3,213.630 3,213.630 ↓ 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.420..0.420 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
22. 51.059 3,409.208 ↓ 1,579.2 36,322 4 / 4

Sort (cost=4,093.24..4,093.30 rows=23 width=16) (actual time=3,402.355..3,409.208 rows=36,322 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
23. 11.686 3,358.149 ↓ 1,579.3 36,325 4 / 4

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

24. 3.681 835.869 ↓ 41.0 41 4 / 4

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

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

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

26. 0.038 0.038 ↑ 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.035..0.038 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)
27. 779.267 832.148 ↓ 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=74.406..832.148 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
28. 52.881 52.881 ↑ 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=52.881..52.881 rows=3,232 loops=4)

  • Index Cond: (organization_uuid = '08847962-07df-494c-8725-2b6ca10d0f42'::uuid)
29. 8.651 2,510.594 ↓ 38.5 886 164 / 4

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

30. 1,136.192 2,501.943 ↓ 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=33.468..61.023 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
31. 1,365.751 1,365.751 ↑ 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=33.311..33.311 rows=1,704 loops=164)

  • Index Cond: (roles @> ((('["'::text || (rr.target_role_uuid)::text) || '"]'::text))::jsonb)
32. 82.612 4,149.205 ↓ 2.0 46,172 1

Sort (cost=77,909.50..77,966.13 rows=22,653 width=98) (actual time=4,126.447..4,149.205 rows=46,172 loops=1)

  • Sort Key: pr.person_uuid
  • Sort Method: quicksort Memory: 6,343kB
33. 3,201.103 4,066.593 ↓ 1.6 37,113 1

Nested Loop Left Join (cost=1,330.01..76,270.85 rows=22,653 width=98) (actual time=464.983..4,066.593 rows=37,113 loops=1)

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

Append (cost=1,327.40..51,660.78 rows=22,653 width=87) (actual time=198.411..403.630 rows=32,990 loops=1)

35. 190.994 387.921 ↓ 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=87) (actual time=198.409..387.921 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
36. 196.927 196.927 ↑ 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=196.927..196.927 rows=65,962 loops=1)

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

Materialize (cost=2.61..144.92 rows=36 width=57) (actual time=0.003..0.014 rows=52 loops=32,990)

38. 212.436 266.222 ↓ 1.4 52 1

Bitmap Heap Scan on roles role (cost=2.61..144.74 rows=36 width=57) (actual time=102.147..266.222 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
39. 53.786 53.786 ↑ 1.0 104 1

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

  • Index Cond: (organization_uuid = '08847962-07df-494c-8725-2b6ca10d0f42'::uuid)
40. 2,274.623 2,964.309 ↑ 1.4 493 42,961

Materialize (cost=28.35..922.74 rows=676 width=11) (actual time=0.010..0.069 rows=493 loops=42,961)

41. 252.433 689.686 ↑ 1.4 493 1

Bitmap Heap Scan on sites s (cost=28.35..919.36 rows=676 width=11) (actual time=437.292..689.686 rows=493 loops=1)

  • Recheck Cond: ((organization_uuid = '08847962-07df-494c-8725-2b6ca10d0f42'::uuid) AND (valid_range @> '2019-10-01 16:01:17+00'::timestamp with time zone))
  • Filter: (is_visible IS TRUE)
  • Heap Blocks: exact=90
42. 437.253 437.253 ↑ 1.2 562 1

Bitmap Index Scan on sites_org_valid_range_gist (cost=0.00..28.18 rows=690 width=0) (actual time=437.253..437.253 rows=562 loops=1)

  • Index Cond: ((organization_uuid = '08847962-07df-494c-8725-2b6ca10d0f42'::uuid) AND (valid_range @> '2019-10-01 16:01:17+00'::timestamp with time zone))
Planning time : 386.219 ms
Execution time : 539,509.675 ms