explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Na4O

Settings
# exclusive inclusive rows x rows loops node
1. 1.542 618.320 ↓ 1.9 203 1

Nested Loop (cost=0.41..407,259.20 rows=108 width=209) (actual time=2.217..618.320 rows=203 loops=1)

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

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

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

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

4. 5.895 5.895 ↑ 1.1 203 1

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

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

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

SubPlan (for Nested Loop)

7. 34.104 157.528 ↑ 1.0 1 203

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

8. 106.575 123.424 ↑ 1.0 721 203

Bitmap Heap Scan on event_entity ee (cost=41.09..3,608.69 rows=741 width=8) (actual time=0.098..0.608 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. 16.849 16.849 ↑ 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.083..0.083 rows=1,078 loops=203)

  • Index Cond: ((user_id)::text = (ue.id)::text)
10. 0.203 445.991 ↓ 0.0 0 203

Limit (cost=100.76..100.76 rows=1 width=8) (actual time=2.197..2.197 rows=0 loops=203)

11. 0.609 445.788 ↓ 0.0 0 203

Sort (cost=100.76..100.76 rows=1 width=8) (actual time=2.196..2.196 rows=0 loops=203)

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

Index Scan using operation_type_idx on admin_event_entity aee (cost=0.29..100.75 rows=1 width=8) (actual time=2.193..2.193 rows=0 loops=203)

  • Index Cond: ((operation_type)::text = 'UPDATE'::text)
  • Filter: ((representation ~~ '%"enabled":false%'::text) AND ((resource_type)::text = 'USER'::text) AND ((resource_path)::text = concat('users/', ue.id)))
  • Rows Removed by Filter: 212
13. 0.406 5.075 ↑ 1.0 1 203

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

14. 0.622 4.669 ↑ 1.0 1 203

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

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

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

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

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

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

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

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

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

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

Seq Scan on keycloak_role kr (cost=0.00..4.39 rows=27 width=56) (actual time=0.004..0.031 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.190 0.190 ↑ 1.0 2 190

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

Planning time : 1.158 ms
Execution time : 618.513 ms