explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VxLc : person count - 3rd run

Settings
# exclusive inclusive rows x rows loops node
1. 9.375 14,227.434 ↑ 1.0 1 1

Aggregate (cost=23,861.23..23,861.24 rows=1 width=8) (actual time=14,227.433..14,227.434 rows=1 loops=1)

2.          

Initplan (for Aggregate)

3. 0.001 0.022 ↓ 0.0 0 1

Append (cost=0.41..4.12 rows=1 width=0) (actual time=0.022..0.022 rows=0 loops=1)

4. 0.021 0.021 ↓ 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..4.11 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. 8.973 14,218.037 ↑ 1.8 8,655 1

Hash Left Join (cost=16,420.47..23,818.26 rows=15,542 width=16) (actual time=1,488.382..14,218.037 rows=8,655 loops=1)

  • Hash Cond: (p.person_uuid = pr.person_uuid)
6. 7,395.255 12,862.504 ↓ 11.4 6,011 1

Nested Loop Left Join (cost=10,640.18..17,493.44 rows=526 width=16) (actual time=141.776..12,862.504 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
7. 11.843 192.149 ↓ 23.3 12,325 1

Nested Loop Anti Join (cost=10,639.12..16,755.42 rows=528 width=16) (actual time=106.462..192.149 rows=12,325 loops=1)

  • Join Filter: (adm.person_uuid = p.person_uuid)
8. 2.507 180.306 ↓ 11.7 12,325 1

Append (cost=10,639.12..16,134.87 rows=1,056 width=16) (actual time=101.745..180.306 rows=12,325 loops=1)

9. 76.184 177.799 ↓ 11.7 12,325 1

Bitmap Heap Scan on persons_org_part_default p (cost=10,639.12..16,129.59 rows=1,056 width=16) (actual time=101.743..177.799 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=652
10. 0.739 101.615 ↓ 0.0 0 1

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

11. 2.632 2.632 ↑ 1.1 19,775 1

Bitmap Index Scan on persons_org_part_default_pkey (cost=0.00..359.83 rows=21,120 width=0) (actual time=2.632..2.632 rows=19,775 loops=1)

  • Index Cond: (organization_uuid = '9179fd37-1322-4957-8a99-7e2056388b4f'::uuid)
12. 98.244 98.244 ↑ 1.0 433,828 1

Bitmap Index Scan on persons_org_part_default_valid_range_idx (cost=0.00..10,278.51 rows=444,213 width=0) (actual time=98.244..98.244 rows=433,828 loops=1)

  • Index Cond: (valid_range @> '2019-09-29 01:19:31+00'::timestamp with time zone)
13. 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)

14. 0.002 4.704 ↓ 0.0 0 1

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

15. 4.702 4.702 ↓ 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.702..4.702 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
16. 5,257.729 5,275.100 ↓ 1,402.2 8,413 12,325

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

17. 0.767 17.371 ↓ 1,402.2 8,413 1

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

18. 0.716 1.259 ↓ 31.0 31 1

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

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

Append (cost=0.41..4.12 rows=1 width=43) (actual time=0.052..0.054 rows=1 loops=1)

20. 0.053 0.053 ↑ 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.051..0.053 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
21. 0.489 0.489 ↓ 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.056..0.489 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
22. 0.775 15.345 ↓ 45.2 271 31

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

23. 13.392 14.570 ↓ 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.053..0.470 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
24. 1.178 1.178 ↓ 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.038..0.038 rows=273 loops=31)

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

Hash (cost=5,706.50..5,706.50 rows=5,904 width=16) (actual time=1,346.560..1,346.560 rows=19,319 loops=1)

  • Buckets: 32,768 (originally 8192) Batches: 1 (originally 1) Memory Usage: 1,162kB
26. 1,256.091 1,336.787 ↓ 3.3 19,319 1

Nested Loop Left Join (cost=2.42..5,706.50 rows=5,904 width=16) (actual time=0.265..1,336.787 rows=19,319 loops=1)

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

Append (cost=0.00..634.21 rows=5,904 width=59) (actual time=0.023..10.280 rows=17,604 loops=1)

28. 8.750 8.750 ↓ 3.0 17,604 1

Seq Scan on person_roles_org_part_72 pr (cost=0.00..604.69 rows=5,904 width=59) (actual time=0.022..8.750 rows=17,604 loops=1)

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

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

30. 0.077 0.135 ↓ 3.0 83 1

Bitmap Heap Scan on roles role (cost=2.42..112.85 rows=28 width=16) (actual time=0.073..0.135 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
31. 0.058 0.058 ↓ 1.0 86 1

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

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