explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dHFY

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 381,212.491 ↑ 40.0 1 1

Limit (cost=1,126,896,107.91..1,126,896,108.11 rows=40 width=569) (actual time=381,212.490..381,212.491 rows=1 loops=1)

2. 0.003 381,212.488 ↑ 384,379,300.0 1 1

Unique (cost=1,126,896,107.91..1,128,818,004.41 rows=384,379,300 width=569) (actual time=381,212.487..381,212.488 rows=1 loops=1)

3. 0.104 381,212.485 ↑ 384,379,300.0 1 1

Sort (cost=1,126,896,107.91..1,127,857,056.16 rows=384,379,300 width=569) (actual time=381,212.485..381,212.485 rows=1 loops=1)

  • Sort Key: p.person_uuid, p.first_name, p.last_name, (lower(pr.valid_range)) DESC, pr.sequence DESC
  • Sort Method: quicksort Memory: 25kB
4. 0.110 381,212.381 ↑ 384,379,300.0 1 1

GroupAggregate (cost=853,227,829.98..882,056,277.48 rows=384,379,300 width=569) (actual time=381,212.381..381,212.381 rows=1 loops=1)

  • Group Key: p.revision_uuid, pr.valid_range, pr.sequence
5. 0.039 381,212.271 ↑ 384,379,300.0 1 1

Sort (cost=853,227,829.98..854,188,778.23 rows=384,379,300 width=570) (actual time=381,212.270..381,212.271 rows=1 loops=1)

  • Sort Key: p.revision_uuid, pr.valid_range, pr.sequence DESC
  • Sort Method: quicksort Memory: 25kB
6. 0.044 381,212.232 ↑ 384,379,300.0 1 1

Hash Left Join (cost=34.35..608,387,999.56 rows=384,379,300 width=570) (actual time=136,755.056..381,212.232 rows=1 loops=1)

  • Hash Cond: ((((jsonb_array_elements_text(pr.roles)))::uuid) = role.role_uuid)
7. 0.010 381,171.393 ↑ 384,379,300.0 1 1

Nested Loop Left Join (cost=0.56..604,062,722.01 rows=384,379,300 width=529) (actual time=136,714.225..381,171.393 rows=1 loops=1)

8. 0.013 381,171.267 ↑ 3,843,793.0 1 1

Nested Loop Left Join (cost=0.56..587,659,335.38 rows=3,843,793 width=546) (actual time=136,714.105..381,171.267 rows=1 loops=1)

9. 380,321.059 381,171.198 ↑ 3,843,793.0 1 1

Seq Scan on persons p (cost=0.00..584,533,426.04 rows=3,843,793 width=485) (actual time=136,714.043..381,171.198 rows=1 loops=1)

  • Filter: (((organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid) AND (valid_range @> '2019-08-08 22:57:26+00'::timestamp with time zone) AND (is_visible IS TRUE) AND (((first_name)::text ~~* '%hpomi%'::text) OR (
  • Rows Removed by Filter: 79845054
10.          

SubPlan (forSeq Scan)

11. 0.000 0.000 ↓ 0.0 0 12,401,069

Result (cost=0.56..3.58 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=12,401,069)

  • One-Time Filter: upper_inf(p.valid_range)
12. 1,886.080 1,886.080 ↓ 0.0 0 471,520

Index Scan using person_roles_org_seq_vrange_uidx on person_roles (cost=0.56..3.58 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=471,520)

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

Index Scan using person_roles_org_seq_vrange_uidx on person_roles person_roles_1 (cost=0.56..3.58 rows=1 width=0) (never executed)

  • Index Cond: ((organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid) AND (person_uuid = p.person_uuid))
  • Filter: (is_super_admin AND upper_inf(valid_range))
14. 850.139 850.139 ↓ 0.0 0 1

Index Scan using person_roles_organization_uuid_idx on person_roles person_roles_2 (cost=0.56..2,525.45 rows=1 width=16) (actual time=850.139..850.139 rows=0 loops=1)

  • Index Cond: (organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid)
  • Filter: (is_super_admin AND upper_inf(valid_range))
  • Rows Removed by Filter: 3081
15. 0.056 0.056 ↑ 1.0 1 1

Index Scan using person_roles_org_seq_vrange_uidx on person_roles pr (cost=0.56..0.80 rows=1 width=77) (actual time=0.053..0.056 rows=1 loops=1)

  • Index Cond: ((organization_uuid = '02ea59bf-4c3c-42e1-8f47-1d037f8755b9'::uuid) AND (person_uuid = p.person_uuid))
  • Filter: upper_inf(valid_range)
16. 0.009 0.116 ↑ 100.0 1 1

Result (cost=0.00..2.27 rows=100 width=16) (actual time=0.112..0.116 rows=1 loops=1)

17. 0.106 0.107 ↑ 100.0 1 1

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.104..0.107 rows=1 loops=1)

18. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)

19. 0.020 40.795 ↓ 4.3 30 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
20. 0.061 40.775 ↓ 4.3 30 1

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

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

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

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