explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aw3s

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 143.550 ↓ 0.0 0 1

Limit (cost=199,244.15..199,244.40 rows=100 width=350) (actual time=143.550..143.550 rows=0 loops=1)

2. 0.053 143.546 ↓ 0.0 0 1

Sort (cost=199,244.15..199,369.16 rows=50,005 width=350) (actual time=143.546..143.546 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. 0.002 143.493 ↓ 0.0 0 1

Subquery Scan on p (cost=189,034.99..197,332.99 rows=50,005 width=350) (actual time=143.493..143.493 rows=0 loops=1)

  • Filter: (((p.first_name)::text ~~* '%ada%'::text) OR ((p.last_name)::text ~~* '%ada%'::text) OR ((p.target_name)::text ~~* '%ada%'::text) OR ((p.web_login)::text ~~ '%ada%'::text))
4. 0.000 143.491 ↓ 0.0 0 1

Unique (cost=189,034.99..190,694.59 rows=331,920 width=372) (actual time=143.491..143.491 rows=0 loops=1)

5.          

Initplan (for Unique)

6. 0.002 0.006 ↓ 0.0 0 1

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

7. 0.004 0.004 ↓ 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.004..0.004 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))
8. 0.008 143.490 ↓ 0.0 0 1

Sort (cost=189,031.55..189,861.35 rows=331,920 width=372) (actual time=143.490..143.490 rows=0 loops=1)

  • Sort Key: p_1.person_uuid
  • Sort Method: quicksort Memory: 25kB
9. 0.002 143.482 ↓ 0.0 0 1

GroupAggregate (cost=96,401.90..122,125.70 rows=331,920 width=372) (actual time=143.482..143.482 rows=0 loops=1)

  • Group Key: p_1.organization_uuid, p_1.revision_uuid, pr.valid_range, pr.sequence, s.name
10. 0.025 143.480 ↓ 0.0 0 1

Sort (cost=96,401.90..97,231.70 rows=331,920 width=381) (actual time=143.480..143.480 rows=0 loops=1)

  • Sort Key: p_1.revision_uuid, pr.valid_range, pr.sequence, s.name
  • Sort Method: quicksort Memory: 25kB
11. 0.001 143.455 ↓ 0.0 0 1

Nested Loop Left Join (cost=23,091.24..28,767.04 rows=331,920 width=381) (actual time=143.455..143.455 rows=0 loops=1)

12. 0.003 143.454 ↓ 0.0 0 1

Merge Left Join (cost=23,090.96..24,607.13 rows=55,320 width=370) (actual time=143.454..143.454 rows=0 loops=1)

  • Merge Cond: (p_1.person_uuid = pr.person_uuid)
13. 0.002 143.451 ↓ 0.0 0 1

Nested Loop Anti Join (cost=17,014.65..17,696.33 rows=1,874 width=291) (actual time=143.451..143.451 rows=0 loops=1)

  • Join Filter: (adm.person_uuid = p_1.person_uuid)
14. 2.075 143.449 ↓ 0.0 0 1

Merge Left Join (cost=17,014.65..17,035.41 rows=3,748 width=291) (actual time=143.449..143.449 rows=0 loops=1)

  • Merge Cond: (p_1.person_uuid = tpr.person_uuid)
  • Filter: ((p_1.person_uuid = '26b9d4a9-10ff-4389-a60b-6febbfd8ffc8'::uuid) OR $0 OR (tpr.person_uuid IS NOT NULL))
  • Rows Removed by Filter: 17,519
15. 15.520 141.303 ↓ 4.7 17,519 1

Sort (cost=16,332.68..16,342.07 rows=3,757 width=291) (actual time=138.443..141.303 rows=17,519 loops=1)

  • Sort Key: p_1.person_uuid
  • Sort Method: quicksort Memory: 5,318kB
16. 1.385 125.783 ↓ 4.7 17,519 1

Append (cost=10,640.47..16,109.60 rows=3,757 width=291) (actual time=112.566..125.783 rows=17,519 loops=1)

17. 11.974 124.398 ↓ 4.7 17,519 1

Bitmap Heap Scan on persons_org_part_default p_1 (cost=10,640.47..16,090.81 rows=3,757 width=291) (actual time=112.564..124.398 rows=17,519 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)
  • Heap Blocks: exact=652
18. 0.741 112.424 ↓ 0.0 0 1

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

19. 2.570 2.570 ↑ 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.570..2.570 rows=19,775 loops=1)

  • Index Cond: (organization_uuid = '9179fd37-1322-4957-8a99-7e2056388b4f'::uuid)
20. 109.113 109.113 ↑ 1.0 433,815 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=109.113..109.113 rows=433,815 loops=1)

  • Index Cond: (valid_range @> '2019-09-29 01:19:31+00'::timestamp with time zone)
21. 0.015 0.071 ↓ 0.0 0 1

Sort (cost=681.97..681.98 rows=6 width=16) (actual time=0.071..0.071 rows=0 loops=1)

  • Sort Key: tpr.person_uuid
  • Sort Method: quicksort Memory: 25kB
22. 0.000 0.056 ↓ 0.0 0 1

Nested Loop (cost=1.06..681.89 rows=6 width=16) (actual time=0.056..0.056 rows=0 loops=1)

23. 0.001 0.056 ↓ 0.0 0 1

Nested Loop (cost=0.84..678.07 rows=1 width=16) (actual time=0.055..0.056 rows=0 loops=1)

  • Join Filter: (spr.roles @> ((('[""'::text || (rr.source_role_uuid)::text) || '""]'::text))::jsonb)
24. 0.002 0.055 ↓ 0.0 0 1

Append (cost=0.41..3.44 rows=1 width=43) (actual time=0.054..0.055 rows=0 loops=1)

25. 0.053 0.053 ↓ 0.0 0 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..3.44 rows=1 width=43) (actual time=0.053..0.053 rows=0 loops=1)

  • Index Cond: ((organization_uuid = '9179fd37-1322-4957-8a99-7e2056388b4f'::uuid) AND (person_uuid = '26b9d4a9-10ff-4389-a60b-6febbfd8ffc8'::uuid))
  • Filter: upper_inf(valid_range)
26. 0.000 0.000 ↓ 0.0 0

Index Scan using role_on_role_organization_uuid_idx on role_on_role rr (cost=0.42..669.93 rows=171 width=32) (never executed)

  • 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))
27. 0.000 0.000 ↓ 0.0 0

Append (cost=0.22..3.76 rows=6 width=59) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on person_roles_org_part_72 tpr (cost=0.22..3.73 rows=6 width=59) (never executed)

  • 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))
29. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on person_roles_org_part_72_roles_idx (cost=0.00..0.22 rows=18 width=0) (never executed)

  • Index Cond: (roles @> ((('[""'::text || (rr.target_role_uuid)::text) || '""]'::text))::jsonb)
30. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..604.70 rows=1 width=16) (never executed)

31. 0.000 0.000 ↓ 0.0 0

Append (cost=0.00..604.70 rows=1 width=16) (never executed)

32. 0.000 0.000 ↓ 0.0 0

Seq Scan on person_roles_org_part_72 adm (cost=0.00..604.69 rows=1 width=16) (never executed)

  • Filter: (is_super_admin AND upper_inf(valid_range) AND (organization_uuid = '9179fd37-1322-4957-8a99-7e2056388b4f'::uuid))
33. 0.000 0.000 ↓ 0.0 0

Sort (cost=6,076.31..6,091.07 rows=5,904 width=95) (never executed)

  • Sort Key: pr.person_uuid
34. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=2.42..5,706.50 rows=5,904 width=95) (never executed)

  • Join Filter: (pr.roles @> ((('[""'::text || (role.role_uuid)::text) || '""]'::text))::jsonb)
35. 0.000 0.000 ↓ 0.0 0

Append (cost=0.00..634.21 rows=5,904 width=81) (never executed)

36. 0.000 0.000 ↓ 0.0 0

Seq Scan on person_roles_org_part_72 pr (cost=0.00..604.69 rows=5,904 width=81) (never executed)

  • Filter: (upper_inf(valid_range) AND (organization_uuid = '9179fd37-1322-4957-8a99-7e2056388b4f'::uuid))
37. 0.000 0.000 ↓ 0.0 0

Materialize (cost=2.42..112.99 rows=28 width=57) (never executed)

38. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on roles role (cost=2.42..112.85 rows=28 width=57) (never executed)

  • Recheck Cond: (organization_uuid = '9179fd37-1322-4957-8a99-7e2056388b4f'::uuid)
  • Filter: upper_inf(valid_range)
39. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on roles_organization_uuid_idx (cost=0.00..2.41 rows=83 width=0) (never executed)

  • Index Cond: (organization_uuid = '9179fd37-1322-4957-8a99-7e2056388b4f'::uuid)
40. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.28..10.93 rows=6 width=11) (never executed)

41. 0.000 0.000 ↓ 0.0 0

Index Scan using sites_org_valid_range_gist on sites s (cost=0.28..10.90 rows=6 width=11) (never executed)

  • 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 : 159.322 ms
Execution time : 144.662 ms