explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dnup : Optimization for: plan #hUsd

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 797.609 1,343.020 ↑ 1.1 32,972 1

Hash Left Join (cost=98,997.19..122,451.86 rows=36,863 width=3,463) (actual time=133.619..1,343.020 rows=32,972 loops=1)

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

CTE fleets

3. 2.679 2.679 ↑ 1.0 1,403 1

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

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

CTE states

5. 201.519 249.803 ↑ 1.3 32,972 1

Hash Join (cost=45.60..50,878.37 rows=41,303 width=56) (actual time=0.768..249.803 rows=32,972 loops=1)

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

Seq Scan on vehicle v_1 (cost=0.00..37,145.78 rows=46,278 width=1,115) (actual time=0.002..47.566 rows=35,783 loops=1)

7. 0.366 0.718 ↑ 1.0 1,403 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 121kB
8. 0.352 0.352 ↑ 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.352 rows=1,403 loops=1)

9. 9.088 542.281 ↑ 1.1 32,972 1

Hash Left Join (cost=47,545.92..67,636.84 rows=36,863 width=3,407) (actual time=130.431..542.281 rows=32,972 loops=1)

  • Hash Cond: (v.vehicle_group_id = vg.id)
10. 19.046 533.160 ↑ 1.1 32,972 1

Hash Join (cost=47,543.62..67,495.45 rows=36,863 width=3,397) (actual time=130.388..533.160 rows=32,972 loops=1)

  • Hash Cond: (v.vehicle_type_id = vt.id)
11. 22.433 512.493 ↑ 1.1 32,972 1

Hash Join (cost=47,396.85..66,925.74 rows=36,863 width=3,384) (actual time=128.703..512.493 rows=32,972 loops=1)

  • Hash Cond: (v.fleet_id = f.fleet_id)
12. 90.000 485.026 ↑ 1.3 32,972 1

Hash Join (cost=47,351.25..59,180.23 rows=41,303 width=1,732) (actual time=123.656..485.026 rows=32,972 loops=1)

  • Hash Cond: (vs.vehicle_id = v.id)
13. 272.371 272.371 ↑ 1.3 32,972 1

CTE Scan on states vs (cost=0.00..826.06 rows=41,303 width=56) (actual time=0.769..272.371 rows=32,972 loops=1)

14. 71.045 122.655 ↑ 1.3 35,783 1

Hash (cost=37,145.78..37,145.78 rows=46,278 width=1,680) (actual time=122.655..122.655 rows=35,783 loops=1)

  • Buckets: 16384 Batches: 4 Memory Usage: 11831kB
15. 51.610 51.610 ↑ 1.3 35,783 1

Seq Scan on vehicle v (cost=0.00..37,145.78 rows=46,278 width=1,680) (actual time=0.004..51.610 rows=35,783 loops=1)

16. 0.829 5.034 ↑ 1.0 1,403 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 581kB
17. 4.205 4.205 ↑ 1.0 1,403 1

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

18. 0.858 1.621 ↓ 1.1 4,332 1

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

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

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

20. 0.022 0.033 ↓ 6.5 84 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
21. 0.011 0.011 ↓ 6.5 84 1

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

22. 1.767 3.130 ↑ 1.0 7,596 1

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

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

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

Planning time : 3.472 ms
Execution time : 1,349.024 ms