explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yGtB

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 4,803.736 ↓ 2.0 12,951 1

Gather (cost=1,305.39..351,194.36 rows=6,420 width=246) (actual time=2.971..4,803.736 rows=12,951 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
2. 3.437 196.420 ↓ 1.6 4,317 3 / 3

Hash Join (cost=305.39..43,893.03 rows=2,675 width=94) (actual time=0.908..196.420 rows=4,317 loops=3)

  • Hash Cond: ((ue.realm_id)::text = (r.id)::text)
3. 6.682 192.967 ↑ 1.2 4,317 3 / 3

Nested Loop (cost=304.35..43,848.19 rows=5,350 width=94) (actual time=0.840..192.967 rows=4,317 loops=3)

4. 4.376 4.971 ↑ 1.2 4,317 3 / 3

Parallel Bitmap Heap Scan on user_attribute ua (cost=303.93..22,616.05 rows=5,350 width=46) (actual time=0.811..4.971 rows=4,317 loops=3)

  • Recheck Cond: ((name)::text = 'employeeId'::text)
  • Filter: (value IS NOT NULL)
  • Heap Blocks: exact=1
5. 0.595 0.595 ↓ 1.0 12,951 1 / 3

Bitmap Index Scan on name_idx (cost=0.00..300.72 rows=12,839 width=0) (actual time=1.786..1.786 rows=12,951 loops=1)

  • Index Cond: ((name)::text = 'employeeId'::text)
6. 181.314 181.314 ↑ 1.0 1 12,951 / 3

Index Scan using constraint_fb on user_entity ue (cost=0.42..3.97 rows=1 width=85) (actual time=0.042..0.042 rows=1 loops=12,951)

  • Index Cond: ((id)::text = (ua.user_id)::text)
7. 0.004 0.016 ↑ 1.0 1 3 / 3

Hash (cost=1.02..1.02 rows=1 width=14) (actual time=0.016..0.016 rows=1 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
8. 0.012 0.012 ↑ 1.0 1 3 / 3

Seq Scan on realm r (cost=0.00..1.02 rows=1 width=14) (actual time=0.011..0.012 rows=1 loops=3)

  • Filter: ((name)::text <> 'master'::text)
  • Rows Removed by Filter: 1
9.          

SubPlan (for Gather)

10. 543.942 543.942 ↑ 1.0 1 12,951

Index Scan using idx_event_entity_last_login_user_id on event_entity_last_login ee (cost=0.42..8.44 rows=1 width=8) (actual time=0.042..0.042 rows=1 loops=12,951)

  • Index Cond: ((user_id)::text = (ue.id)::text)
11. 0.000 38.853 ↓ 0.0 0 12,951

Limit (cost=8.17..8.18 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=12,951)

12. 25.902 38.853 ↓ 0.0 0 12,951

Sort (cost=8.17..8.18 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=12,951)

  • Sort Key: aee.admin_event_time DESC
  • Sort Method: quicksort Memory: 25kB
13. 12.951 12.951 ↓ 0.0 0 12,951

Index Scan using resource_type_idx on admin_event_entity aee (cost=0.14..8.16 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=12,951)

  • Index Cond: ((resource_type)::text = 'USER'::text)
  • Filter: ((representation ~~ '%"enabled":false%'::text) AND ((operation_type)::text = 'UPDATE'::text) AND ((resource_path)::text = concat('users/', ue.id)))
14. 25.902 4,131.369 ↑ 1.0 1 12,951

Aggregate (cost=30.97..30.98 rows=1 width=32) (actual time=0.319..0.319 rows=1 loops=12,951)

15. 14.706 4,105.467 ↑ 2.0 1 12,951

Nested Loop (cost=24.70..30.96 rows=2 width=11) (actual time=0.310..0.317 rows=1 loops=12,951)

16. 103.608 4,079.565 ↑ 2.0 1 12,951

Hash Join (cost=24.57..30.41 rows=2 width=18) (actual time=0.308..0.315 rows=1 loops=12,951)

  • Hash Cond: ((kr.id)::text = (urm.role_id)::text)
17. 207.216 207.216 ↑ 1.1 48 12,951

Seq Scan on keycloak_role kr (cost=0.00..5.71 rows=51 width=55) (actual time=0.001..0.016 rows=48 loops=12,951)

  • Filter: ((NOT client_role) AND ((name)::text <> ALL ('{offline_access,uma_authorization,user}'::text[])))
  • Rows Removed by Filter: 76
18. 25.902 3,768.741 ↑ 1.0 5 12,951

Hash (cost=24.51..24.51 rows=5 width=37) (actual time=0.291..0.291 rows=5 loops=12,951)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
19. 3,742.839 3,742.839 ↑ 1.0 5 12,951

Index Scan using idx_user_role_mapping on user_role_mapping urm (cost=0.43..24.51 rows=5 width=37) (actual time=0.242..0.289 rows=5 loops=12,951)

  • Index Cond: ((user_id)::text = (ue.id)::text)
20. 11.196 11.196 ↑ 1.0 1 11,196

Index Only Scan using constraint_4a on realm r_1 (cost=0.13..0.30 rows=1 width=7) (actual time=0.001..0.001 rows=1 loops=11,196)

  • Index Cond: (id = (kr.realm_id)::text)
  • Heap Fetches: 0
Planning time : 1.038 ms
Execution time : 4,805.643 ms