explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xrrk

Settings
# exclusive inclusive rows x rows loops node
1. 12.722 18,927.830 ↑ 1.0 1 1

Aggregate (cost=15,865.64..15,865.65 rows=1 width=8) (actual time=18,927.830..18,927.830 rows=1 loops=1)

2.          

Initplan (for Aggregate)

3. 0.002 0.023 ↓ 0.0 0 1

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

4. 0.021 0.021 ↓ 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.021..0.021 rows=0 loops=1)

  • Index Cond: ((organization_uuid = '9179fd37-1322-4957-8a99-7e2056388b4f'::uuid) AND (person_uuid = '981387e3-9d1a-4d83-bd44-8866e911710b'::uuid))
  • Filter: (is_company_admin AND upper_inf(valid_range))
  • Rows Removed by Filter: 2
5. 644.730 18,915.085 ↓ 1.7 8,655 1

Nested Loop Left Join (cost=481.97..15,849.61 rows=4,978 width=16) (actual time=145.441..18,915.085 rows=8,655 loops=1)

  • Join Filter: (pr.roles @> ((('[""'::text || (role.role_uuid)::text) || '""]'::text))::jsonb)
  • Rows Removed by Join Filter: 490,258
6. 12.567 18,228.278 ↓ 1.2 6,011 1

Nested Loop Left Join (cost=479.46..10,942.03 rows=4,978 width=63) (actual time=145.193..18,228.278 rows=6,011 loops=1)

7. 10,634.144 18,149.590 ↓ 39.3 6,011 1

Nested Loop Left Join (cost=478.90..10,392.38 rows=153 width=16) (actual time=145.177..18,149.590 rows=6,011 loops=1)

  • Join Filter: (tpr.person_uuid = p.person_uuid)
  • Rows Removed by Join Filter: 103,684,214
  • Filter: ((p.person_uuid = '981387e3-9d1a-4d83-bd44-8866e911710b'::uuid) OR $0 OR (tpr.person_uuid IS NOT NULL))
  • Rows Removed by Filter: 9,491
8. 18.223 379.271 ↓ 80.6 12,325 1

Nested Loop Anti Join (cost=102.28..9,232.05 rows=153 width=16) (actual time=5.910..379.271 rows=12,325 loops=1)

9. 2.964 151.523 ↓ 80.0 12,325 1

Append (cost=101.72..8,678.81 rows=154 width=16) (actual time=5.794..151.523 rows=12,325 loops=1)

10. 143.526 148.559 ↓ 80.0 12,325 1

Bitmap Heap Scan on persons_org_part_default p (cost=101.72..8,678.04 rows=154 width=16) (actual time=5.793..148.559 rows=12,325 loops=1)

  • Recheck Cond: (organization_uuid = '9179fd37-1322-4957-8a99-7e2056388b4f'::uuid)
  • Filter: ((is_visible IS TRUE) AND (valid_range @> '2019-09-29 01:19:31+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: 7,415
  • Heap Blocks: exact=1,799
11. 5.033 5.033 ↓ 3.4 19,795 1

Bitmap Index Scan on persons_org_part_default_pkey (cost=0.00..101.68 rows=5,883 width=0) (actual time=5.033..5.033 rows=19,795 loops=1)

  • Index Cond: (organization_uuid = '9179fd37-1322-4957-8a99-7e2056388b4f'::uuid)
12. 12.325 209.525 ↓ 0.0 0 12,325

Append (cost=0.56..3.58 rows=1 width=16) (actual time=0.017..0.017 rows=0 loops=12,325)

13. 197.200 197.200 ↓ 0.0 0 12,325

Index Scan using person_roles_org_part_default_organization_uuid_person_uuid_idx on person_roles_org_part_default adm (cost=0.56..3.58 rows=1 width=16) (actual time=0.016..0.016 rows=0 loops=12,325)

  • Index Cond: ((organization_uuid = '9179fd37-1322-4957-8a99-7e2056388b4f'::uuid) AND (person_uuid = p.person_uuid))
  • Filter: (is_super_admin AND upper_inf(valid_range))
  • Rows Removed by Filter: 1
14. 6,954.105 7,136.175 ↓ 1,201.9 8,413 12,325

Materialize (cost=376.63..1,141.60 rows=7 width=16) (actual time=0.001..0.579 rows=8,413 loops=12,325)

15. 1.062 182.070 ↓ 1,201.9 8,413 1

Nested Loop (cost=376.63..1,141.56 rows=7 width=16) (actual time=11.001..182.070 rows=8,413 loops=1)

16. 1.139 2.231 ↓ 31.0 31 1

Nested Loop (cost=0.98..735.34 rows=1 width=16) (actual time=0.266..2.231 rows=31 loops=1)

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

Append (cost=0.56..3.58 rows=1 width=47) (actual time=0.081..0.083 rows=1 loops=1)

18. 0.082 0.082 ↑ 1.0 1 1

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=47) (actual time=0.080..0.082 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
19. 1.009 1.009 ↓ 3.5 650 1

Index Scan using role_on_role_organization_uuid_idx on role_on_role rr (cost=0.42..726.62 rows=187 width=32) (actual time=0.088..1.009 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
20. 1.271 178.777 ↓ 38.7 271 31

Append (cost=375.65..406.15 rows=7 width=63) (actual time=4.614..5.767 rows=271 loops=31)

21. 35.836 177.506 ↓ 38.7 271 31

Bitmap Heap Scan on person_roles_org_part_default tpr (cost=375.65..406.11 rows=7 width=63) (actual time=4.602..5.726 rows=271 loops=31)

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

BitmapAnd (cost=375.65..375.65 rows=20 width=0) (actual time=4.570..4.570 rows=0 loops=31)

23. 104.935 104.935 ↑ 11.2 273 31

Bitmap Index Scan on person_roles_org_part_default_roles_idx1 (cost=0.00..28.79 rows=3,050 width=0) (actual time=3.385..3.385 rows=273 loops=31)

  • Index Cond: (roles @> ((('[""'::text || (rr.target_role_uuid)::text) || '""]'::text))::jsonb)
24. 35.928 35.928 ↑ 1.1 17,725 24

Bitmap Index Scan on person_roles_org_part_default_pkey (cost=0.00..344.98 rows=19,523 width=0) (actual time=1.497..1.497 rows=17,725 loops=24)

  • Index Cond: (organization_uuid = '9179fd37-1322-4957-8a99-7e2056388b4f'::uuid)
25. 6.011 66.121 ↑ 1.0 1 6,011

Append (cost=0.56..3.58 rows=1 width=63) (actual time=0.010..0.011 rows=1 loops=6,011)

26. 60.110 60.110 ↑ 1.0 1 6,011

Index Scan using person_roles_org_part_default_organization_uuid_person_uuid_idx on person_roles_org_part_default pr (cost=0.56..3.58 rows=1 width=63) (actual time=0.009..0.010 rows=1 loops=6,011)

  • Index Cond: ((organization_uuid = '9179fd37-1322-4957-8a99-7e2056388b4f'::uuid) AND (person_uuid = p.person_uuid))
  • Filter: upper_inf(valid_range)
  • Rows Removed by Filter: 0
27. 41.874 42.077 ↓ 2.6 83 6,011

Materialize (cost=2.52..128.78 rows=32 width=16) (actual time=0.000..0.007 rows=83 loops=6,011)

28. 0.128 0.203 ↓ 2.6 83 1

Bitmap Heap Scan on roles role (cost=2.52..128.62 rows=32 width=16) (actual time=0.093..0.203 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
29. 0.075 0.075 ↑ 1.1 86 1

Bitmap Index Scan on roles_organization_uuid_idx (cost=0.00..2.51 rows=96 width=0) (actual time=0.075..0.075 rows=86 loops=1)

  • Index Cond: (organization_uuid = '9179fd37-1322-4957-8a99-7e2056388b4f'::uuid)
Planning time : 37.061 ms
Execution time : 18,928.789 ms