explain.depesz.com

PostgreSQL's explain analyze made readable

Result: U0e

Settings
# exclusive inclusive rows x rows loops node
1. 0.025 788.750 ↑ 1.0 40 1

Limit (cost=150,901.57..150,901.67 rows=40 width=533) (actual time=788.723..788.750 rows=40 loops=1)

2. 0.171 788.725 ↑ 876.9 40 1

Sort (cost=150,901.57..150,989.26 rows=35,075 width=533) (actual time=788.722..788.725 rows=40 loops=1)

  • Sort Key: p.uuid, p.first_name, p.last_name
  • Sort Method: top-N heapsort Memory: 49kB
3. 7.493 788.554 ↑ 119.3 294 1

Subquery Scan on p (cost=148,565.24..149,792.86 rows=35,075 width=533) (actual time=780.290..788.554 rows=294 loops=1)

  • Filter: (((p.first_name)::text ~~* '%hpomi%'::text) OR ((p.last_name)::text ~~* '%hpomi%'::text) OR ((p.target_name)::text ~~* '%hpomi%'::text) OR ((p.web_login)::text ~~ '%hpomi%'::text) OR ((p.first_name)::text ~~* '%w%'::text) OR ((p.last_name)::text ~~* '%
  • Rows Removed by Filter: 1875
4. 0.632 781.061 ↑ 16.2 2,169 1

Unique (cost=148,565.24..148,740.61 rows=35,075 width=561) (actual time=780.209..781.061 rows=2,169 loops=1)

5. 2.476 780.429 ↑ 16.2 2,169 1

Sort (cost=148,565.24..148,652.93 rows=35,075 width=561) (actual time=780.207..780.429 rows=2,169 loops=1)

  • Sort Key: p_1.person_uuid
  • Sort Method: quicksort Memory: 1371kB
6. 10.294 777.953 ↑ 16.2 2,169 1

GroupAggregate (cost=137,670.71..140,213.65 rows=35,075 width=561) (actual time=767.210..777.953 rows=2,169 loops=1)

  • Group Key: p_1.revision_uuid, pr.valid_range, pr.sequence
7. 6.628 767.659 ↑ 8.2 4,259 1

Sort (cost=137,670.71..137,758.40 rows=35,075 width=570) (actual time=767.132..767.659 rows=4,259 loops=1)

  • Sort Key: p_1.revision_uuid, pr.valid_range, pr.sequence
  • Sort Method: quicksort Memory: 1597kB
8. 2.032 761.031 ↑ 8.2 4,259 1

Hash Left Join (cost=35.46..129,242.62 rows=35,075 width=570) (actual time=23.738..761.031 rows=4,259 loops=1)

  • Hash Cond: ((((jsonb_array_elements_text(pr.roles)))::uuid) = role.role_uuid)
9. 2.480 758.854 ↑ 8.2 4,259 1

Nested Loop Left Join (cost=1.67..128,814.15 rows=35,075 width=529) (actual time=23.575..758.854 rows=4,259 loops=1)

10. 1.654 752.036 ↓ 6.2 2,169 1

Nested Loop Anti Join (cost=1.67..127,316.26 rows=351 width=546) (actual time=23.563..752.036 rows=2,169 loops=1)

11. 0.499 746.044 ↓ 6.2 2,169 1

Nested Loop Left Join (cost=1.11..126,054.90 rows=351 width=546) (actual time=23.548..746.044 rows=2,169 loops=1)

12. 521.338 739.038 ↓ 6.2 2,169 1

Index Scan using persons_org_valid_range_gist on persons p_1 (cost=0.55..124,793.06 rows=351 width=485) (actual time=23.532..739.038 rows=2,169 loops=1)

  • Index Cond: ((organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid) AND (valid_range @> '2019-08-08 22:57:26+00'::timestamp with time zone))
  • Filter: ((is_visible IS TRUE) AND ((person_uuid = '4b6fe547-24da-c5eb-cdea-1fa664f8053e'::uuid) OR (SubPlan 1) OR (SubPlan 2)))
  • Rows Removed by Filter: 9
13.          

SubPlan (for Index Scan)

14. 0.000 6.531 ↓ 0.0 0 2,177

Result (cost=0.56..3.58 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=2,177)

  • One-Time Filter: upper_inf(p_1.valid_range)
15. 6.531 6.531 ↓ 0.0 0 2,177

Index Scan using person_roles_org_seq_vrange_uidx on person_roles person_roles_1 (cost=0.56..3.58 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=2,177)

  • Index Cond: ((organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid) AND (person_uuid = '4b6fe547-24da-c5eb-cdea-1fa664f8053e'::uuid))
  • Filter: (is_company_admin AND upper_inf(valid_range))
  • Rows Removed by Filter: 1
16. 8.555 211.169 ↑ 1.0 1 2,177

Nested Loop (cost=6.07..197.77 rows=1 width=0) (actual time=0.097..0.097 rows=1 loops=2,177)

  • Join Filter: (rr.target_role_uuid = (((jsonb_array_elements_text(tpr.roles)))::uuid))
  • Rows Removed by Join Filter: 12
17. 5.213 169.806 ↓ 8.0 8 2,177

Nested Loop (cost=6.07..193.25 rows=1 width=49) (actual time=0.029..0.078 rows=8 loops=2,177)

18. 4.354 115.381 ↓ 8.0 8 2,177

Nested Loop (cost=5.50..189.65 rows=1 width=16) (actual time=0.022..0.053 rows=8 loops=2,177)

19. 4.354 4.354 ↑ 1.0 1 2,177

Index Scan using person_roles_org_seq_vrange_uidx on person_roles spr (cost=0.56..3.58 rows=1 width=33) (actual time=0.002..0.002 rows=1 loops=2,177)

  • Index Cond: ((organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid) AND (person_uuid = '4b6fe547-24da-c5eb-cdea-1fa664f8053e'::uuid))
  • Filter: upper_inf(valid_range)
20. 23.947 106.673 ↓ 8.0 8 2,177

Hash Join (cost=4.94..186.06 rows=1 width=16) (actual time=0.019..0.049 rows=8 loops=2,177)

  • Hash Cond: (rr.source_role_uuid = (((jsonb_array_elements_text(spr.roles)))::uuid))
21. 76.195 76.195 ↓ 1.9 87 2,177

Index Scan using role_on_role_organization_uuid_idx on role_on_role rr (cost=0.42..181.35 rows=47 width=32) (actual time=0.005..0.035 rows=87 loops=2,177)

  • Index Cond: (organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid)
  • Filter: ((can_edit_person OR can_view_person OR can_use_person) AND upper_inf(valid_range))
  • Rows Removed by Filter: 4
22. 2.177 6.531 ↑ 100.0 1 2,177

Hash (cost=3.27..3.27 rows=100 width=16) (actual time=0.003..0.003 rows=1 loops=2,177)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
23. 2.177 4.354 ↑ 100.0 1 2,177

Result (cost=0.00..2.27 rows=100 width=16) (actual time=0.002..0.002 rows=1 loops=2,177)

24. 2.177 2.177 ↑ 100.0 1 2,177

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.001..0.001 rows=1 loops=2,177)

25. 0.000 0.000 ↑ 1.0 1 2,177

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=2,177)

26. 49.212 49.212 ↑ 1.0 1 16,404

Index Scan using person_roles_org_seq_vrange_uidx on person_roles tpr (cost=0.56..3.58 rows=1 width=33) (actual time=0.003..0.003 rows=1 loops=16,404)

  • Index Cond: ((organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid) AND (person_uuid = p_1.person_uuid))
  • Filter: upper_inf(valid_range)
27. 16.404 32.808 ↑ 50.0 2 16,404

Result (cost=0.00..2.27 rows=100 width=16) (actual time=0.001..0.002 rows=2 loops=16,404)

28. 16.404 16.404 ↑ 50.0 2 16,404

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.001..0.001 rows=2 loops=16,404)

29. 0.000 0.000 ↑ 1.0 1 16,404

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=16,404)

30. 6.507 6.507 ↑ 1.0 1 2,169

Index Scan using person_roles_org_seq_vrange_uidx on person_roles pr (cost=0.56..3.58 rows=1 width=77) (actual time=0.002..0.003 rows=1 loops=2,169)

  • Index Cond: ((organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid) AND (person_uuid = p_1.person_uuid))
  • Filter: upper_inf(valid_range)
31. 4.338 4.338 ↓ 0.0 0 2,169

Index Scan using person_roles_org_seq_vrange_uidx on person_roles (cost=0.56..3.58 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=2,169)

  • Index Cond: ((organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid) AND (person_uuid = p_1.person_uuid))
  • Filter: (is_super_admin AND upper_inf(valid_range))
  • Rows Removed by Filter: 1
32. 2.169 4.338 ↑ 50.0 2 2,169

Result (cost=0.00..2.27 rows=100 width=16) (actual time=0.001..0.002 rows=2 loops=2,169)

33. 2.169 2.169 ↑ 50.0 2 2,169

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.001..0.001 rows=2 loops=2,169)

34. 0.000 0.000 ↑ 1.0 1 2,169

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=2,169)

35. 0.028 0.145 ↓ 4.3 30 1

Hash (cost=33.70..33.70 rows=7 width=57) (actual time=0.145..0.145 rows=30 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
36. 0.088 0.117 ↓ 4.3 30 1

Bitmap Heap Scan on roles role (cost=1.96..33.70 rows=7 width=57) (actual time=0.042..0.117 rows=30 loops=1)

  • Recheck Cond: (organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid)
  • Filter: upper_inf(valid_range)
  • Heap Blocks: exact=3
37. 0.029 0.029 ↓ 1.4 30 1

Bitmap Index Scan on roles_organization_uuid_idx (cost=0.00..1.96 rows=22 width=0) (actual time=0.028..0.029 rows=30 loops=1)

  • Index Cond: (organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid)
Planning time : 2.533 ms
Execution time : 789.458 ms