explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eyFi

Settings
# exclusive inclusive rows x rows loops node
1. 99.305 33,515.501 ↓ 2.0 12,949 1

Merge Left Join (cost=2,355,983.85..2,374,228.33 rows=6,628 width=246) (actual time=32,366.354..33,515.501 rows=12,949 loops=1)

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

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

  • Workers Planned: 2
  • Workers Launched: 2
3. 6.893 1,168.237 ↓ 1.6 4,316 3 / 3

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

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

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

  • Sort Key: ue.id
  • Sort Method: quicksort Memory: 1,618kB
5. 7.340 95.587 ↓ 1.6 4,316 3 / 3

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

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

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

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

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

8. 6.449 7.472 ↑ 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=1.250..7.472 rows=4,316 loops=3)

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

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

  • Index Cond: ((name)::text = 'employeeId'::text)
10. 69.061 69.061 ↑ 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.016..0.016 rows=1 loops=12,949)

  • Index Cond: ((id)::text = (ua.user_id)::text)
11. 0.005 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
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.011..0.011 rows=1 loops=3)

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

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

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

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

15. 0.000 0.017 ↓ 0.0 0 3 / 3

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

  • Group Key: aee.resource_path
16. 0.009 0.017 ↓ 0.0 0 3 / 3

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

  • Sort Key: aee.resource_path
  • Sort Method: quicksort Memory: 25kB
17. 0.008 0.008 ↓ 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.008..0.008 rows=0 loops=3)

  • Index Cond: ((resource_type)::text = 'USER'::text)
  • Filter: ((representation ~~ '%"enabled":false%'::text) AND ((operation_type)::text = 'UPDATE'::text))
18. 7.568 1,033.300 ↑ 38.3 10,702 3 / 3

GroupAggregate (cost=161,059.12..172,216.84 rows=410,391 width=69) (actual time=1,023.940..1,033.300 rows=10,702 loops=3)

  • Group Key: urm.user_id
19. 38.494 1,025.732 ↑ 71.0 11,320 3 / 3

Sort (cost=161,059.12..163,068.40 rows=803,711 width=48) (actual time=1,023.931..1,025.732 rows=11,320 loops=3)

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

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

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

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

  • Hash Cond: ((urm.role_id)::text = (kr.id)::text)
22. 332.331 332.331 ↓ 1.0 1,954,308 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..332.331 rows=1,954,308 loops=3)

23. 0.022 0.067 ↑ 1.1 48 3 / 3

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
24. 0.045 0.045 ↑ 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.045 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.004 0.011 ↑ 1.0 2 3 / 3

Hash (cost=1.02..1.02 rows=2 width=7) (actual time=0.011..0.011 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. 152.006 32,125.927 ↓ 11.7 327,058 1

Finalize GroupAggregate (cost=2,149,761.55..2,150,459.30 rows=27,910 width=45) (actual time=31,104.075..32,125.927 rows=327,058 loops=1)

  • Group Key: ee.user_id
28. 3,412.324 31,973.921 ↓ 14.1 786,148 1

Sort (cost=2,149,761.55..2,149,901.10 rows=55,820 width=45) (actual time=31,104.066..31,973.921 rows=786,148 loops=1)

  • Sort Key: ee.user_id
  • Sort Method: external merge Disk: 44,664kB
29. 281.523 28,561.597 ↓ 14.1 786,167 1

Gather (cost=2,139,499.46..2,145,360.56 rows=55,820 width=45) (actual time=28,160.327..28,561.597 rows=786,167 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
30. 2,729.447 28,280.074 ↓ 9.4 262,056 3 / 3

Partial HashAggregate (cost=2,138,499.46..2,138,778.56 rows=27,910 width=45) (actual time=28,154.676..28,280.074 rows=262,056 loops=3)

  • Group Key: ee.user_id
31. 25,550.627 25,550.627 ↑ 1.2 5,640,028 3 / 3

Parallel Seq Scan on event_entity ee (cost=0.00..2,103,391.58 rows=7,021,577 width=45) (actual time=0.062..25,550.627 rows=5,640,028 loops=3)

  • Filter: ((type)::text = 'LOGIN'::text)
  • Rows Removed by Filter: 3,719,713
Planning time : 2.054 ms
Execution time : 33,529.075 ms