explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XwH : Optimization for: plan #QTgk

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.092 163.116 ↑ 7.3 51 1

Sort (cost=49,609.37..49,610.30 rows=372 width=2,789) (actual time=163.105..163.116 rows=51 loops=1)

  • Sort Key: "GlobalEventLog".created_at DESC, "GlobalEventLog".id
  • Sort Method: quicksort Memory: 73kB
2. 0.031 163.024 ↑ 7.3 51 1

Hash Left Join (cost=47,624.56..49,593.49 rows=372 width=2,789) (actual time=161.502..163.024 rows=51 loops=1)

  • Hash Cond: ("systemAlert".administrator_id = "systemAlert->administrator".id)
3. 0.051 162.686 ↑ 7.3 51 1

Hash Left Join (cost=47,592.87..49,556.68 rows=372 width=2,591) (actual time=161.185..162.686 rows=51 loops=1)

  • Hash Cond: ("systemAlert".id = "systemAlert->dockGroups->SystemAlertDockGroup".system_alert_id)
4. 0.030 161.669 ↑ 1.0 51 1

Nested Loop Left Join (cost=47,418.76..49,369.42 rows=51 width=2,528) (actual time=160.193..161.669 rows=51 loops=1)

  • Join Filter: ("systemAlert".id = "systemAlert->updates".system_alert_id)
  • Rows Removed by Join Filter: 204
5. 0.018 161.588 ↑ 1.0 51 1

Nested Loop Left Join (cost=47,418.76..49,357.35 rows=51 width=1,980) (actual time=160.168..161.588 rows=51 loops=1)

  • Join Filter: ("GlobalEventLog".system_alert_id = "systemAlert".id)
  • Rows Removed by Join Filter: 51
6. 0.042 161.519 ↑ 1.0 51 1

Nested Loop Left Join (cost=47,418.63..49,348.43 rows=51 width=888) (actual time=160.135..161.519 rows=51 loops=1)

7. 0.021 161.018 ↑ 1.0 51 1

Nested Loop Left Join (cost=47,418.21..48,921.48 rows=51 width=870) (actual time=160.107..161.018 rows=51 loops=1)

8. 0.031 160.538 ↑ 1.0 51 1

Nested Loop Left Join (cost=47,417.77..48,489.89 rows=51 width=833) (actual time=160.081..160.538 rows=51 loops=1)

9. 0.022 160.354 ↑ 1.0 51 1

Nested Loop Left Join (cost=47,417.48..48,121.83 rows=51 width=794) (actual time=160.068..160.354 rows=51 loops=1)

10. 0.026 160.230 ↑ 1.0 51 1

Nested Loop Left Join (cost=47,417.21..47,914.27 rows=51 width=758) (actual time=160.053..160.230 rows=51 loops=1)

11. 0.067 160.204 ↑ 1.0 51 1

Nested Loop Left Join (cost=47,417.06..47,793.48 rows=51 width=719) (actual time=160.049..160.204 rows=51 loops=1)

12. 0.008 160.035 ↑ 1.0 51 1

Limit (cost=47,416.77..47,416.90 rows=51 width=707) (actual time=160.011..160.035 rows=51 loops=1)

13. 13.564 160.027 ↑ 256.1 51 1

Sort (cost=47,416.77..47,449.43 rows=13,062 width=707) (actual time=160.009..160.027 rows=51 loops=1)

  • Sort Key: "GlobalEventLog".created_at DESC, "GlobalEventLog".id
  • Sort Method: top-N heapsort Memory: 64kB
14. 143.635 146.463 ↓ 1.3 17,520 1

Bitmap Heap Scan on global_event_log "GlobalEventLog" (cost=245.82..46,981.00 rows=13,062 width=707) (actual time=5.927..146.463 rows=17,520 loops=1)

  • Recheck Cond: ((dock_group_id = 425) AND (deleted_at IS NULL))
  • Filter: ((event_type)::text <> 'dock_created'::text)
  • Heap Blocks: exact=16666
15. 2.828 2.828 ↓ 1.3 17,520 1

Bitmap Index Scan on emergency_global_event_log_dock_group (cost=0.00..242.55 rows=13,082 width=0) (actual time=2.827..2.828 rows=17,520 loops=1)

  • Index Cond: (dock_group_id = 425)
16. 0.102 0.102 ↑ 1.0 1 51

Index Scan using dock_pkey on dock (cost=0.29..7.36 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=51)

  • Index Cond: ("GlobalEventLog".dock_id = id)
  • Filter: (deleted_at IS NULL)
17. 0.000 0.000 ↓ 0.0 0 51

Index Scan using administrator_pkey on administrator (cost=0.14..2.36 rows=1 width=39) (actual time=0.000..0.000 rows=0 loops=51)

  • Index Cond: ("GlobalEventLog".administrator_id = id)
  • Filter: (deleted_at IS NULL)
18. 0.102 0.102 ↑ 1.0 1 51

Index Scan using dock_group_pkey on dock_group "dockGroup" (cost=0.28..4.06 rows=1 width=36) (actual time=0.002..0.002 rows=1 loops=51)

  • Index Cond: ("GlobalEventLog".dock_group_id = id)
  • Filter: (deleted_at IS NULL)
19. 0.153 0.153 ↑ 1.0 1 51

Index Scan using vehicle_pkey on vehicle (cost=0.29..7.21 rows=1 width=39) (actual time=0.003..0.003 rows=1 loops=51)

  • Index Cond: ("GlobalEventLog".vehicle_id = id)
  • Filter: (deleted_at IS NULL)
20. 0.459 0.459 ↑ 1.0 1 51

Index Scan using trip_pkey on trip (cost=0.43..8.45 rows=1 width=37) (actual time=0.008..0.009 rows=1 loops=51)

  • Index Cond: ("GlobalEventLog".trip_id = id)
  • Filter: (deleted_at IS NULL)
21. 0.459 0.459 ↑ 1.0 1 51

Index Scan using subscriber_pkey on "user" (cost=0.42..8.36 rows=1 width=18) (actual time=0.009..0.009 rows=1 loops=51)

  • Index Cond: ("GlobalEventLog".user_id = id)
  • Filter: (deleted_at IS NULL)
22. 0.032 0.051 ↑ 1.0 1 51

Materialize (cost=0.14..8.15 rows=1 width=1,092) (actual time=0.001..0.001 rows=1 loops=51)

23. 0.019 0.019 ↑ 1.0 1 1

Index Scan using "System ID and incident start time decreasing" on system_alert "systemAlert" (cost=0.14..8.15 rows=1 width=1,092) (actual time=0.018..0.019 rows=1 loops=1)

24. 0.036 0.051 ↓ 4.0 4 51

Materialize (cost=0.00..11.31 rows=1 width=548) (actual time=0.000..0.001 rows=4 loops=51)

25. 0.015 0.015 ↓ 4.0 4 1

Seq Scan on system_alert_update "systemAlert->updates" (cost=0.00..11.30 rows=1 width=548) (actual time=0.015..0.015 rows=4 loops=1)

  • Filter: (deleted_at IS NULL)
26. 0.003 0.966 ↑ 729.5 2 1

Hash (cost=155.87..155.87 rows=1,459 width=63) (actual time=0.966..0.966 rows=2 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
27. 0.022 0.963 ↑ 729.5 2 1

Hash Join (cost=107.91..155.87 rows=1,459 width=63) (actual time=0.960..0.963 rows=2 loops=1)

  • Hash Cond: ("systemAlert->dockGroups->SystemAlertDockGroup".dock_group_id = "systemAlert->dockGroups".id)
28. 0.027 0.027 ↑ 850.0 2 1

Seq Scan on system_alert_dock_group "systemAlert->dockGroups->SystemAlertDockGroup" (cost=0.00..27.00 rows=1,700 width=20) (actual time=0.027..0.027 rows=2 loops=1)

29. 0.209 0.914 ↑ 1.0 1,112 1

Hash (cost=93.98..93.98 rows=1,114 width=43) (actual time=0.914..0.914 rows=1,112 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 90kB
30. 0.705 0.705 ↑ 1.0 1,112 1

Seq Scan on dock_group "systemAlert->dockGroups" (cost=0.00..93.98 rows=1,114 width=43) (actual time=0.007..0.705 rows=1,112 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 186
31. 0.091 0.307 ↑ 1.0 253 1

Hash (cost=28.53..28.53 rows=253 width=178) (actual time=0.306..0.307 rows=253 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 60kB
32. 0.216 0.216 ↑ 1.0 253 1

Seq Scan on administrator "systemAlert->administrator" (cost=0.00..28.53 rows=253 width=178) (actual time=0.010..0.216 rows=253 loops=1)

  • Filter: (deleted_at IS NULL)
Planning time : 4.123 ms
Execution time : 163.702 ms