explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jiNc

Settings
# exclusive inclusive rows x rows loops node
1. 3.098 23,367.790 ↓ 1.9 203 1

Nested Loop (cost=0.41..2,772,191.79 rows=108 width=209) (actual time=109.925..23,367.790 rows=203 loops=1)

  • Join Filter: ((ue.realm_id)::text = (r.id)::text)
2. 0.006 0.006 ↑ 1.0 1 1

Seq Scan on realm r (cost=0.00..1.02 rows=1 width=14) (actual time=0.005..0.006 rows=1 loops=1)

  • Filter: ((name)::text <> 'master'::text)
  • Rows Removed by Filter: 1
3. 0.742 8.927 ↑ 1.1 203 1

Nested Loop (cost=0.41..3,236.30 rows=217 width=93) (actual time=0.022..8.927 rows=203 loops=1)

4. 6.155 6.155 ↑ 1.1 203 1

Seq Scan on user_attribute ua (cost=0.00..1,754.99 rows=217 width=43) (actual time=0.003..6.155 rows=203 loops=1)

  • Filter: ((value IS NOT NULL) AND ((name)::text = 'employeeId'::text))
  • Rows Removed by Filter: 58,938
5. 2.030 2.030 ↑ 1.0 1 203

Index Scan using constraint_fb on user_entity ue (cost=0.41..6.83 rows=1 width=87) (actual time=0.010..0.010 rows=1 loops=203)

  • Index Cond: ((id)::text = (ua.user_id)::text)
6.          

SubPlan (for Nested Loop)

7. 35.322 12,657.253 ↑ 1.0 1 203

Aggregate (cost=20,722.98..20,722.99 rows=1 width=8) (actual time=62.351..62.351 rows=1 loops=203)

8. 12,621.931 12,621.931 ↑ 1.0 721 203

Seq Scan on event_entity ee (cost=0.00..20,721.13 rows=741 width=8) (actual time=36.908..62.177 rows=721 loops=203)

  • Filter: (((user_id)::text = (ue.id)::text) AND ((type)::text = 'LOGIN'::text))
  • Rows Removed by Filter: 235,320
9. 0.812 10,689.574 ↓ 0.0 0 203

Limit (cost=4,885.85..4,885.85 rows=1 width=8) (actual time=52.658..52.658 rows=0 loops=203)

10. 2.842 10,688.762 ↓ 0.0 0 203

Sort (cost=4,885.85..4,885.85 rows=1 width=8) (actual time=52.654..52.654 rows=0 loops=203)

  • Sort Key: aee.admin_event_time DESC
  • Sort Method: quicksort Memory: 25kB
11. 10,685.920 10,685.920 ↓ 0.0 0 203

Seq Scan on admin_event_entity aee (cost=0.00..4,885.84 rows=1 width=8) (actual time=52.640..52.640 rows=0 loops=203)

  • Filter: ((representation ~~ '%"enabled":false%'::text) AND ((operation_type)::text = 'UPDATE'::text) AND ((resource_type)::text = 'USER'::text) AND ((resource_path)::text = concat('users/', ue.id)))
  • Rows Removed by Filter: 61,726
12. 1.218 8.932 ↑ 1.0 1 203

Aggregate (cost=29.58..29.59 rows=1 width=32) (actual time=0.044..0.044 rows=1 loops=203)

13. 1.041 7.714 ↑ 1.0 1 203

Nested Loop (cost=9.19..29.57 rows=1 width=12) (actual time=0.036..0.038 rows=1 loops=203)

  • Join Filter: ((kr.realm_id)::text = (r_1.id)::text)
  • Rows Removed by Join Filter: 1
14. 1.767 6.293 ↑ 1.0 1 203

Hash Join (cost=9.19..28.53 rows=1 width=19) (actual time=0.029..0.031 rows=1 loops=203)

  • Hash Cond: ((urm.role_id)::text = (kr.id)::text)
15. 1.624 4.466 ↑ 1.0 5 203

Bitmap Heap Scan on user_role_mapping urm (cost=4.46..23.79 rows=5 width=37) (actual time=0.020..0.022 rows=5 loops=203)

  • Recheck Cond: ((user_id)::text = (ue.id)::text)
  • Heap Blocks: exact=315
16. 2.842 2.842 ↑ 1.0 5 203

Bitmap Index Scan on idx_user_role_mapping (cost=0.00..4.46 rows=5 width=0) (actual time=0.014..0.014 rows=5 loops=203)

  • Index Cond: ((user_id)::text = (ue.id)::text)
17. 0.025 0.060 ↓ 1.6 43 1

Hash (cost=4.39..4.39 rows=27 width=56) (actual time=0.059..0.060 rows=43 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
18. 0.035 0.035 ↓ 1.6 43 1

Seq Scan on keycloak_role kr (cost=0.00..4.39 rows=27 width=56) (actual time=0.008..0.035 rows=43 loops=1)

  • Filter: ((NOT client_role) AND ((name)::text <> ALL ('{offline_access,uma_authorization,user}'::text[])))
  • Rows Removed by Filter: 78
19. 0.380 0.380 ↑ 1.0 2 190

Seq Scan on realm r_1 (cost=0.00..1.02 rows=2 width=7) (actual time=0.002..0.002 rows=2 loops=190)

Planning time : 1.142 ms
Execution time : 23,368.412 ms