explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gI1b

Settings
# exclusive inclusive rows x rows loops node
1. 2.652 10,893.136 ↓ 1.9 203 1

Nested Loop (cost=0.41..924,048.48 rows=108 width=209) (actual time=51.717..10,893.136 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.745 8.663 ↑ 1.1 203 1

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

4. 6.091 6.091 ↑ 1.1 203 1

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

  • Filter: ((value IS NOT NULL) AND ((name)::text = 'employeeId'::text))
  • Rows Removed by Filter: 58,938
5. 1.827 1.827 ↑ 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.009..0.009 rows=1 loops=203)

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

SubPlan (for Nested Loop)

7. 34.307 165.851 ↑ 1.0 1 203

Aggregate (cost=3,610.54..3,610.55 rows=1 width=8) (actual time=0.817..0.817 rows=1 loops=203)

8. 107.184 131.544 ↑ 1.0 721 203

Bitmap Heap Scan on event_entity ee (cost=41.09..3,608.69 rows=741 width=8) (actual time=0.137..0.648 rows=721 loops=203)

  • Recheck Cond: ((user_id)::text = (ue.id)::text)
  • Filter: ((type)::text = 'LOGIN'::text)
  • Rows Removed by Filter: 358
  • Heap Blocks: exact=23,213
9. 24.360 24.360 ↑ 1.0 1,078 203

Bitmap Index Scan on idx_user_id (cost=0.00..40.90 rows=1,131 width=0) (actual time=0.120..0.120 rows=1,078 loops=203)

  • Index Cond: ((user_id)::text = (ue.id)::text)
10. 0.406 10,707.641 ↓ 0.0 0 203

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

11. 1.624 10,707.235 ↓ 0.0 0 203

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

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

Seq Scan on admin_event_entity aee (cost=0.00..4,885.84 rows=1 width=8) (actual time=52.737..52.737 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
13. 1.015 8.323 ↑ 1.0 1 203

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

14. 1.041 7.308 ↑ 1.0 1 203

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

  • Join Filter: ((kr.realm_id)::text = (r_1.id)::text)
  • Rows Removed by Join Filter: 1
15. 1.758 5.887 ↑ 1.0 1 203

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

  • Hash Cond: ((urm.role_id)::text = (kr.id)::text)
16. 1.421 4.060 ↑ 1.0 5 203

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

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

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

  • Index Cond: ((user_id)::text = (ue.id)::text)
18. 0.033 0.069 ↓ 1.6 43 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
19. 0.036 0.036 ↓ 1.6 43 1

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

  • Filter: ((NOT client_role) AND ((name)::text <> ALL ('{offline_access,uma_authorization,user}'::text[])))
  • Rows Removed by Filter: 78
20. 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.233 ms
Execution time : 10,893.756 ms