explain.depesz.com

PostgreSQL's explain analyze made readable

Result: txWU

Settings
# exclusive inclusive rows x rows loops node
1. 99.672 34,281.268 ↓ 2.0 12,949 1

Merge Left Join (cost=2,356,028.66..2,374,273.13 rows=6,628 width=246) (actual time=33,149.476..34,281.268 rows=12,949 loops=1)

  • Merge Cond: ((ue.id)::text = (ee.user_id)::text)
2. 66.043 1,250.440 ↓ 2.0 12,949 1

Gather Merge (cost=206,222.30..223,316.16 rows=6,628 width=134) (actual time=1,222.149..1,250.440 rows=12,949 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 7.385 1,184.397 ↓ 1.6 4,316 3 / 3

Merge Left Join (cost=205,222.28..221,551.10 rows=2,762 width=134) (actual time=1,165.977..1,184.397 rows=4,316 loops=3)

  • Merge Cond: ((ue.id)::text = (urm.user_id)::text)
4. 27.533 107.097 ↓ 1.6 4,316 3 / 3

Sort (cost=44,163.16..44,169.95 rows=2,718 width=102) (actual time=106.117..107.097 rows=4,316 loops=3)

  • Sort Key: ue.id
  • Sort Method: quicksort Memory: 1,002kB
5. 4.834 79.564 ↓ 1.6 4,316 3 / 3

Hash Left Join (cost=315.20..44,008.12 rows=2,718 width=102) (actual time=0.886..79.564 rows=4,316 loops=3)

  • Hash Cond: (concat('users/', ue.id) = (deactivation.resource_path)::text)
6. 2.046 74.717 ↓ 1.6 4,316 3 / 3

Hash Join (cost=306.99..43,992.26 rows=2,718 width=94) (actual time=0.851..74.717 rows=4,316 loops=3)

  • Hash Cond: ((ue.realm_id)::text = (r.id)::text)
7. 8.347 72.657 ↑ 1.3 4,316 3 / 3

Nested Loop (cost=305.96..43,946.72 rows=5,436 width=94) (actual time=0.816..72.657 rows=4,316 loops=3)

8. 11.931 12.514 ↑ 1.3 4,316 3 / 3

Parallel Bitmap Heap Scan on user_attribute ua (cost=305.53..22,709.30 rows=5,436 width=46) (actual time=0.793..12.514 rows=4,316 loops=3)

  • Recheck Cond: ((name)::text = 'employeeId'::text)
  • Filter: (value IS NOT NULL)
  • Heap Blocks: exact=1,117
9. 0.583 0.583 ↑ 1.0 12,949 1 / 3

Bitmap Index Scan on name_idx (cost=0.00..302.27 rows=13,046 width=0) (actual time=1.749..1.749 rows=12,949 loops=1)

  • Index Cond: ((name)::text = 'employeeId'::text)
10. 51.796 51.796 ↑ 1.0 1 12,949 / 3

Index Scan using constraint_fb on user_entity ue (cost=0.42..3.91 rows=1 width=85) (actual time=0.012..0.012 rows=1 loops=12,949)

  • Index Cond: ((id)::text = (ua.user_id)::text)
11. 0.003 0.014 ↑ 1.0 1 3 / 3

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

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

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

  • Filter: ((name)::text <> 'master'::text)
  • Rows Removed by Filter: 1
13. 0.000 0.013 ↓ 0.0 0 3 / 3

Hash (cost=8.20..8.20 rows=1 width=524) (actual time=0.013..0.013 rows=0 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
14. 0.001 0.013 ↓ 0.0 0 3 / 3

Subquery Scan on deactivation (cost=8.17..8.20 rows=1 width=524) (actual time=0.013..0.013 rows=0 loops=3)

15. 0.000 0.012 ↓ 0.0 0 3 / 3

GroupAggregate (cost=8.17..8.19 rows=1 width=524) (actual time=0.012..0.012 rows=0 loops=3)

  • Group Key: aee.resource_path
16. 0.007 0.012 ↓ 0.0 0 3 / 3

Sort (cost=8.17..8.17 rows=1 width=524) (actual time=0.011..0.012 rows=0 loops=3)

  • Sort Key: aee.resource_path
  • Sort Method: quicksort Memory: 25kB
17. 0.005 0.005 ↓ 0.0 0 3 / 3

Index Scan using resource_type_idx on admin_event_entity aee (cost=0.14..8.16 rows=1 width=524) (actual time=0.005..0.005 rows=0 loops=3)

  • Index Cond: ((resource_type)::text = 'USER'::text)
  • Filter: ((representation ~~ '%"enabled":false%'::text) AND ((operation_type)::text = 'UPDATE'::text))
18. 7.806 1,069.915 ↑ 38.4 10,700 3 / 3

GroupAggregate (cost=161,059.12..172,216.84 rows=410,391 width=69) (actual time=1,059.852..1,069.915 rows=10,700 loops=3)

  • Group Key: urm.user_id
19. 40.037 1,062.109 ↑ 71.0 11,318 3 / 3

Sort (cost=161,059.12..163,068.40 rows=803,711 width=48) (actual time=1,059.842..1,062.109 rows=11,318 loops=3)

  • Sort Key: urm.user_id
  • Sort Method: quicksort Memory: 1,269kB
20. 5.400 1,022.072 ↑ 71.0 11,321 3 / 3

Hash Join (cost=7.39..57,505.87 rows=803,711 width=48) (actual time=0.108..1,022.072 rows=11,321 loops=3)

  • Hash Cond: ((kr.realm_id)::text = (r_1.id)::text)
21. 636.603 1,016.662 ↑ 71.0 11,321 3 / 3

Hash Join (cost=6.34..50,924.44 rows=803,711 width=55) (actual time=0.086..1,016.662 rows=11,321 loops=3)

  • Hash Cond: ((urm.role_id)::text = (kr.id)::text)
22. 379.995 379.995 ↓ 1.0 1,954,321 3 / 3

Seq Scan on user_role_mapping urm (cost=0.00..45,613.21 rows=1,954,121 width=74) (actual time=0.009..379.995 rows=1,954,321 loops=3)

23. 0.021 0.064 ↑ 1.1 48 3 / 3

Hash (cost=5.71..5.71 rows=51 width=55) (actual time=0.064..0.064 rows=48 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
24. 0.043 0.043 ↑ 1.1 48 3 / 3

Seq Scan on keycloak_role kr (cost=0.00..5.71 rows=51 width=55) (actual time=0.009..0.043 rows=48 loops=3)

  • Filter: ((NOT client_role) AND ((name)::text <> ALL ('{offline_access,uma_authorization,user}'::text[])))
  • Rows Removed by Filter: 76
25. 0.003 0.010 ↑ 1.0 2 3 / 3

Hash (cost=1.02..1.02 rows=2 width=7) (actual time=0.010..0.010 rows=2 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
26. 0.007 0.007 ↑ 1.0 2 3 / 3

Seq Scan on realm r_1 (cost=0.00..1.02 rows=2 width=7) (actual time=0.006..0.007 rows=2 loops=3)

27. 153.862 32,931.156 ↓ 11.7 327,066 1

Finalize GroupAggregate (cost=2,149,806.35..2,150,504.10 rows=27,910 width=45) (actual time=31,927.271..32,931.156 rows=327,066 loops=1)

  • Group Key: ee.user_id
28. 3,370.733 32,777.294 ↓ 14.1 786,569 1

Sort (cost=2,149,806.35..2,149,945.90 rows=55,820 width=45) (actual time=31,927.262..32,777.294 rows=786,569 loops=1)

  • Sort Key: ee.user_id
  • Sort Method: external merge Disk: 44,696kB
29. 249.283 29,406.561 ↓ 14.1 786,588 1

Gather (cost=2,139,544.27..2,145,405.37 rows=55,820 width=45) (actual time=29,040.041..29,406.561 rows=786,588 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
30. 2,730.402 29,157.278 ↓ 9.4 262,196 3 / 3

Partial HashAggregate (cost=2,138,544.27..2,138,823.37 rows=27,910 width=45) (actual time=29,035.771..29,157.278 rows=262,196 loops=3)

  • Group Key: ee.user_id
31. 26,426.876 26,426.876 ↑ 1.2 5,640,148 3 / 3

Parallel Seq Scan on event_entity ee (cost=0.00..2,103,435.65 rows=7,021,724 width=45) (actual time=0.064..26,426.876 rows=5,640,148 loops=3)

  • Filter: ((type)::text = 'LOGIN'::text)
  • Rows Removed by Filter: 3,719,799
Planning time : 1.296 ms
Execution time : 34,291.214 ms