explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hUsd

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 11,578.431 12,859.767 ↑ 1.1 32,970 1

Hash Left Join (cost=191,404.29..583,202.98 rows=36,549 width=3,564) (actual time=267.891..12,859.767 rows=32,970 loops=1)

  • Hash Cond: (vs.operator_id = o.id)
2.          

CTE fleets

3. 2.419 2.419 ↑ 1.0 1,403 1

Seq Scan on mvw_fleet_schedule f_1 (cost=0.00..211.67 rows=1,403 width=385) (actual time=0.017..2.419 rows=1,403 loops=1)

  • Filter: enabled
  • Rows Removed by Filter: 358
4.          

CTE states

5. 228.222 275.151 ↑ 1.2 32,970 1

Hash Join (cost=45.60..50,758.18 rows=40,952 width=56) (actual time=0.594..275.151 rows=32,970 loops=1)

  • Hash Cond: (v_1.fleet_id = f_2.fleet_id)
6. 46.386 46.386 ↑ 1.3 35,781 1

Seq Scan on vehicle v_1 (cost=0.00..37,141.85 rows=45,885 width=1,115) (actual time=0.002..46.386 rows=35,781 loops=1)

7. 0.306 0.543 ↑ 1.0 1,403 1

Hash (cost=28.06..28.06 rows=1,403 width=44) (actual time=0.542..0.543 rows=1,403 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 121kB
8. 0.237 0.237 ↑ 1.0 1,403 1

CTE Scan on fleets f_2 (cost=0.00..28.06 rows=1,403 width=44) (actual time=0.001..0.237 rows=1,403 loops=1)

9. 19.516 1,276.744 ↑ 1.1 32,970 1

Hash Left Join (cost=140,148.21..502,022.41 rows=36,549 width=4,452) (actual time=262.974..1,276.744 rows=32,970 loops=1)

  • Hash Cond: (v.vehicle_group_id = vg.id)
10. 194.607 1,257.175 ↑ 1.1 32,970 1

Hash Left Join (cost=140,145.92..501,882.13 rows=36,549 width=4,442) (actual time=262.904..1,257.175 rows=32,970 loops=1)

  • Hash Cond: (i.sensor_id = s.id)
11. 38.131 942.762 ↑ 1.1 32,970 1

Nested Loop Left Join (cost=48,887.07..374,555.73 rows=36,549 width=3,654) (actual time=142.969..942.762 rows=32,970 loops=1)

12. 29.934 640.871 ↑ 1.1 32,970 1

Hash Join (cost=48,886.78..70,102.56 rows=36,549 width=3,646) (actual time=142.950..640.871 rows=32,970 loops=1)

  • Hash Cond: (v.vehicle_type_id = vt.id)
13. 38.719 609.138 ↑ 1.1 32,970 1

Hash Join (cost=48,740.01..69,535.61 rows=36,549 width=3,633) (actual time=141.105..609.138 rows=32,970 loops=1)

  • Hash Cond: (v.fleet_id = f.fleet_id)
14. 127.431 565.788 ↑ 1.2 32,970 1

Hash Join (cost=48,694.41..61,855.54 rows=40,952 width=1,981) (actual time=136.460..565.788 rows=32,970 loops=1)

  • Hash Cond: (vs.vehicle_id = v.id)
15. 302.911 302.911 ↑ 1.2 32,970 1

CTE Scan on states vs (cost=0.00..819.04 rows=40,952 width=56) (actual time=0.597..302.911 rows=32,970 loops=1)

16. 72.273 135.446 ↑ 1.3 35,781 1

Hash (cost=37,141.85..37,141.85 rows=45,885 width=1,929) (actual time=135.446..135.446 rows=35,781 loops=1)

  • Buckets: 16384 Batches: 8 Memory Usage: 5923kB
17. 63.173 63.173 ↑ 1.3 35,781 1

Seq Scan on vehicle v (cost=0.00..37,141.85 rows=45,885 width=1,929) (actual time=0.003..63.173 rows=35,781 loops=1)

18. 0.787 4.631 ↑ 1.0 1,403 1

Hash (cost=28.06..28.06 rows=1,403 width=1,656) (actual time=4.631..4.631 rows=1,403 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 581kB
19. 3.844 3.844 ↑ 1.0 1,403 1

CTE Scan on fleets f (cost=0.00..28.06 rows=1,403 width=1,656) (actual time=0.022..3.844 rows=1,403 loops=1)

20. 0.946 1.799 ↓ 1.1 4,332 1

Hash (cost=98.01..98.01 rows=3,901 width=17) (actual time=1.799..1.799 rows=4,332 loops=1)

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 283kB
21. 0.853 0.853 ↓ 1.1 4,332 1

Seq Scan on vehicle_type vt (cost=0.00..98.01 rows=3,901 width=17) (actual time=0.003..0.853 rows=4,332 loops=1)

22. 32.970 263.760 ↑ 1.0 1 32,970

Limit (cost=0.29..8.31 rows=1 width=64) (actual time=0.008..0.008 rows=1 loops=32,970)

23. 230.790 230.790 ↑ 1.0 1 32,970

Index Scan Backward using ak_installation on installation i (cost=0.29..8.31 rows=1 width=64) (actual time=0.007..0.007 rows=1 loops=32,970)

  • Index Cond: ((vehicle_id = v.id) AND (commission_date_utc <= '2019-11-27 00:38:11.353691'::timestamp without time zone))
  • Filter: (COALESCE(decommission_date_utc, 'infinity'::timestamp without time zone) > '2019-11-27 00:38:11.353691'::timestamp without time zone)
  • Rows Removed by Filter: 0
24. 33.639 119.806 ↑ 1.0 27,066 1

Hash (cost=88,189.71..88,189.71 rows=27,371 width=792) (actual time=119.806..119.806 rows=27,066 loops=1)

  • Buckets: 32768 Batches: 2 Memory Usage: 9705kB
25. 86.167 86.167 ↑ 1.0 27,066 1

Seq Scan on sensor s (cost=0.00..88,189.71 rows=27,371 width=792) (actual time=0.003..86.167 rows=27,066 loops=1)

26. 0.029 0.053 ↓ 6.5 84 1

Hash (cost=2.13..2.13 rows=13 width=14) (actual time=0.053..0.053 rows=84 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
27. 0.024 0.024 ↓ 6.5 84 1

Seq Scan on vehicle_group vg (cost=0.00..2.13 rows=13 width=14) (actual time=0.004..0.024 rows=84 loops=1)

28. 2.385 4.592 ↑ 1.0 7,596 1

Hash (cost=190.55..190.55 rows=7,655 width=28) (actual time=4.592..4.592 rows=7,596 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 518kB
29. 2.207 2.207 ↑ 1.0 7,596 1

Seq Scan on operator o (cost=0.00..190.55 rows=7,655 width=28) (actual time=0.005..2.207 rows=7,596 loops=1)

Planning time : 7.006 ms
Execution time : 12,870.445 ms