explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GEATv

Settings
# exclusive inclusive rows x rows loops node
1. 0.026 13,641.509 ↑ 1.0 100 1

Limit (cost=69,115.05..69,115.30 rows=100 width=350) (actual time=13,641.479..13,641.509 rows=100 loops=1)

2. 0.424 13,641.483 ↑ 2.0 100 1

Sort (cost=69,115.05..69,115.55 rows=200 width=350) (actual time=13,641.478..13,641.483 rows=100 loops=1)

  • Sort Key: p.uuid, p.first_name, p.last_name, p.valid_from DESC, p.sequence DESC
  • Sort Method: top-N heapsort Memory: 91kB
3. 0.966 13,641.059 ↓ 14.2 2,834 1

Unique (cost=68,639.15..69,105.41 rows=200 width=350) (actual time=13,639.673..13,641.059 rows=2,834 loops=1)

4. 3.482 13,640.093 ↑ 32.9 2,834 1

Sort (cost=68,639.15..68,872.28 rows=93,252 width=350) (actual time=13,639.672..13,640.093 rows=2,834 loops=1)

  • Sort Key: p.uuid
  • Sort Method: quicksort Memory: 2,018kB
5. 0.531 13,636.611 ↑ 32.9 2,834 1

Subquery Scan on p (cost=43,149.93..51,309.48 rows=93,252 width=350) (actual time=13,616.739..13,636.611 rows=2,834 loops=1)

6. 18.126 13,636.080 ↑ 32.9 2,834 1

GroupAggregate (cost=43,149.93..50,376.96 rows=93,252 width=372) (actual time=13,616.737..13,636.080 rows=2,834 loops=1)

  • Group Key: p_1.organization_uuid, p_1.revision_uuid, pr.valid_range, pr.sequence, s.name
7.          

Initplan (for GroupAggregate)

8. 0.001 0.026 ↓ 0.0 0 1

Append (cost=0.41..3.44 rows=1 width=0) (actual time=0.026..0.026 rows=0 loops=1)

9. 0.025 0.025 ↓ 0.0 0 1

Index Scan using person_roles_org_part_72_organization_uuid_person_uuid_sequ_idx on person_roles_org_part_72 (cost=0.41..3.44 rows=1 width=0) (actual time=0.025..0.025 rows=0 loops=1)

  • Index Cond: ((organization_uuid = '9179fd37-1322-4957-8a99-7e2056388b4f'::uuid) AND (person_uuid = '26b9d4a9-10ff-4389-a60b-6febbfd8ffc8'::uuid))
  • Filter: (is_company_admin AND upper_inf(valid_range))
10. 17.137 13,617.928 ↑ 10.8 8,655 1

Sort (cost=43,146.49..43,379.62 rows=93,252 width=381) (actual time=13,616.668..13,617.928 rows=8,655 loops=1)

  • Sort Key: p_1.revision_uuid, pr.valid_range, pr.sequence, s.name
  • Sort Method: quicksort Memory: 4,813kB
11. 6.124 13,600.791 ↑ 10.8 8,655 1

Nested Loop Left Join (cost=16,423.76..24,997.83 rows=93,252 width=381) (actual time=1,816.793..13,600.791 rows=8,655 loops=1)

12. 7.655 13,594.667 ↑ 1.8 8,655 1

Hash Left Join (cost=16,423.47..23,821.26 rows=15,542 width=370) (actual time=1,815.652..13,594.667 rows=8,655 loops=1)

  • Hash Cond: (p_1.person_uuid = pr.person_uuid)
13. 6,964.078 11,910.095 ↓ 11.4 6,011 1

Nested Loop Left Join (cost=10,643.18..17,496.44 rows=526 width=291) (actual time=138.688..11,910.095 rows=6,011 loops=1)

  • Join Filter: (tpr.person_uuid = p_1.person_uuid)
  • Rows Removed by Join Filter: 103,684,214
  • Filter: ((p_1.person_uuid = '26b9d4a9-10ff-4389-a60b-6febbfd8ffc8'::uuid) OR $0 OR (tpr.person_uuid IS NOT NULL))
  • Rows Removed by Filter: 9,491
14. 10.906 163.917 ↓ 23.3 12,325 1

Nested Loop Anti Join (cost=10,642.12..16,758.42 rows=528 width=291) (actual time=101.922..163.917 rows=12,325 loops=1)

  • Join Filter: (adm.person_uuid = p_1.person_uuid)
15. 1.943 153.011 ↓ 11.7 12,325 1

Append (cost=10,642.12..16,137.87 rows=1,056 width=291) (actual time=97.293..153.011 rows=12,325 loops=1)

16. 53.887 151.068 ↓ 11.7 12,325 1

Bitmap Heap Scan on persons_org_part_default p_1 (cost=10,642.12..16,132.59 rows=1,056 width=291) (actual time=97.292..151.068 rows=12,325 loops=1)

  • Recheck Cond: ((organization_uuid = '9179fd37-1322-4957-8a99-7e2056388b4f'::uuid) AND (valid_range @> '2019-09-29 01:19:31+00'::timestamp with time zone))
  • Filter: ((is_visible IS TRUE) 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: 5,194
  • Heap Blocks: exact=641
17. 0.619 97.181 ↓ 0.0 0 1

BitmapAnd (cost=10,642.12..10,642.12 rows=4,013 width=0) (actual time=97.181..97.181 rows=0 loops=1)

18. 2.182 2.182 ↑ 1.1 19,762 1

Bitmap Index Scan on persons_org_part_default_pkey (cost=0.00..361.33 rows=21,120 width=0) (actual time=2.182..2.182 rows=19,762 loops=1)

  • Index Cond: (organization_uuid = '9179fd37-1322-4957-8a99-7e2056388b4f'::uuid)
19. 94.380 94.380 ↑ 1.0 434,288 1

Bitmap Index Scan on persons_org_part_default_valid_range_idx (cost=0.00..10,280.01 rows=444,213 width=0) (actual time=94.380..94.380 rows=434,288 loops=1)

  • Index Cond: (valid_range @> '2019-09-29 01:19:31+00'::timestamp with time zone)
20. 0.000 0.000 ↓ 0.0 0 12,325

Materialize (cost=0.00..604.70 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=12,325)

21. 0.001 4.622 ↓ 0.0 0 1

Append (cost=0.00..604.70 rows=1 width=16) (actual time=4.622..4.622 rows=0 loops=1)

22. 4.621 4.621 ↓ 0.0 0 1

Seq Scan on person_roles_org_part_72 adm (cost=0.00..604.69 rows=1 width=16) (actual time=4.621..4.621 rows=0 loops=1)

  • Filter: (is_super_admin AND upper_inf(valid_range) AND (organization_uuid = '9179fd37-1322-4957-8a99-7e2056388b4f'::uuid))
  • Rows Removed by Filter: 17,726
23. 4,761.097 4,782.100 ↓ 1,402.2 8,413 12,325

Materialize (cost=1.06..682.60 rows=6 width=16) (actual time=0.000..0.388 rows=8,413 loops=12,325)

24. 0.876 21.003 ↓ 1,402.2 8,413 1

Nested Loop (cost=1.06..682.57 rows=6 width=16) (actual time=0.220..21.003 rows=8,413 loops=1)

25. 0.787 1.403 ↓ 31.0 31 1

Nested Loop (cost=0.84..678.75 rows=1 width=16) (actual time=0.141..1.403 rows=31 loops=1)

  • Join Filter: (spr.roles @> ((('[""'::text || (rr.source_role_uuid)::text) || '""]'::text))::jsonb)
  • Rows Removed by Join Filter: 619
26. 0.001 0.041 ↑ 1.0 1 1

Append (cost=0.41..4.12 rows=1 width=43) (actual time=0.038..0.041 rows=1 loops=1)

27. 0.040 0.040 ↑ 1.0 1 1

Index Scan using person_roles_org_part_72_organization_uuid_person_uuid_sequ_idx on person_roles_org_part_72 spr (cost=0.41..4.11 rows=1 width=43) (actual time=0.038..0.040 rows=1 loops=1)

  • Index Cond: ((organization_uuid = '9179fd37-1322-4957-8a99-7e2056388b4f'::uuid) AND (person_uuid = '981387e3-9d1a-4d83-bd44-8866e911710b'::uuid))
  • Filter: upper_inf(valid_range)
  • Rows Removed by Filter: 1
28. 0.575 0.575 ↓ 3.8 650 1

Index Scan using role_on_role_organization_uuid_idx on role_on_role rr (cost=0.42..669.93 rows=171 width=32) (actual time=0.047..0.575 rows=650 loops=1)

  • Index Cond: (organization_uuid = '9179fd37-1322-4957-8a99-7e2056388b4f'::uuid)
  • Filter: ((can_edit_person OR can_view_person OR can_use_person) AND upper_inf(valid_range))
  • Rows Removed by Filter: 20
29. 0.744 18.724 ↓ 45.2 271 31

Append (cost=0.22..3.76 rows=6 width=59) (actual time=0.052..0.604 rows=271 loops=31)

30. 16.833 17.980 ↓ 45.2 271 31

Bitmap Heap Scan on person_roles_org_part_72 tpr (cost=0.22..3.73 rows=6 width=59) (actual time=0.050..0.580 rows=271 loops=31)

  • Recheck Cond: (roles @> ((('[""'::text || (rr.target_role_uuid)::text) || '""]'::text))::jsonb)
  • Filter: (upper_inf(valid_range) AND (organization_uuid = '9179fd37-1322-4957-8a99-7e2056388b4f'::uuid))
  • Rows Removed by Filter: 2
  • Heap Blocks: exact=2,676
31. 1.147 1.147 ↓ 15.2 273 31

Bitmap Index Scan on person_roles_org_part_72_roles_idx (cost=0.00..0.22 rows=18 width=0) (actual time=0.037..0.037 rows=273 loops=31)

  • Index Cond: (roles @> ((('[""'::text || (rr.target_role_uuid)::text) || '""]'::text))::jsonb)
32. 15.808 1,676.917 ↓ 3.3 19,319 1

Hash (cost=5,706.50..5,706.50 rows=5,904 width=95) (actual time=1,676.916..1,676.917 rows=19,319 loops=1)

  • Buckets: 32,768 (originally 8192) Batches: 1 (originally 1) Memory Usage: 2,551kB
33. 1,558.537 1,661.109 ↓ 3.3 19,319 1

Nested Loop Left Join (cost=2.42..5,706.50 rows=5,904 width=95) (actual time=0.218..1,661.109 rows=19,319 loops=1)

  • Join Filter: (pr.roles @> ((('[""'::text || (role.role_uuid)::text) || '""]'::text))::jsonb)
  • Rows Removed by Join Filter: 1,444,112
34. 1.944 14.552 ↓ 3.0 17,604 1

Append (cost=0.00..634.21 rows=5,904 width=81) (actual time=0.013..14.552 rows=17,604 loops=1)

35. 12.608 12.608 ↓ 3.0 17,604 1

Seq Scan on person_roles_org_part_72 pr (cost=0.00..604.69 rows=5,904 width=81) (actual time=0.012..12.608 rows=17,604 loops=1)

  • Filter: (upper_inf(valid_range) AND (organization_uuid = '9179fd37-1322-4957-8a99-7e2056388b4f'::uuid))
  • Rows Removed by Filter: 122
36. 87.909 88.020 ↓ 3.0 83 17,604

Materialize (cost=2.42..112.99 rows=28 width=57) (actual time=0.000..0.005 rows=83 loops=17,604)

37. 0.066 0.111 ↓ 3.0 83 1

Bitmap Heap Scan on roles role (cost=2.42..112.85 rows=28 width=57) (actual time=0.059..0.111 rows=83 loops=1)

  • Recheck Cond: (organization_uuid = '9179fd37-1322-4957-8a99-7e2056388b4f'::uuid)
  • Filter: upper_inf(valid_range)
  • Rows Removed by Filter: 3
  • Heap Blocks: exact=4
38. 0.045 0.045 ↓ 1.0 86 1

Bitmap Index Scan on roles_organization_uuid_idx (cost=0.00..2.41 rows=83 width=0) (actual time=0.045..0.045 rows=86 loops=1)

  • Index Cond: (organization_uuid = '9179fd37-1322-4957-8a99-7e2056388b4f'::uuid)
39. 0.000 0.000 ↑ 6.0 1 8,655

Materialize (cost=0.28..10.93 rows=6 width=11) (actual time=0.000..0.000 rows=1 loops=8,655)

40. 1.133 1.133 ↑ 6.0 1 1

Index Scan using sites_org_valid_range_gist on sites s (cost=0.28..10.90 rows=6 width=11) (actual time=1.133..1.133 rows=1 loops=1)

  • Index Cond: ((organization_uuid = '9179fd37-1322-4957-8a99-7e2056388b4f'::uuid) AND (valid_range @> '2019-09-29 01:19:31+00'::timestamp with time zone))
  • Filter: (is_visible IS TRUE)
Planning time : 138.567 ms
Execution time : 13,642.556 ms