explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BLpEP : Optimization for: Optimization for: plan #hUsd; plan #dnup

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 3,920.905 4,508.965 ↓ 1.1 32,972 1

Hash Left Join (cost=93,089.14..110,005.99 rows=28,707 width=3,427) (actual time=140.687..4,508.965 rows=32,972 loops=1)

  • Hash Cond: (v.vehicle_group_id = vg.id)
2.          

CTE fleets

3. 2.502 2.502 ↑ 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.015..2.502 rows=1,403 loops=1)

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

CTE states

5. 223.590 265.773 ↓ 1.0 32,972 1

Hash Join (cost=45.60..47,680.10 rows=32,042 width=56) (actual time=0.691..265.773 rows=32,972 loops=1)

  • Hash Cond: (v_1.fleet_id = f_2.fleet_id)
6. 41.542 41.542 ↓ 1.0 35,783 1

Seq Scan on vehicle v_1 (cost=0.00..37,040.65 rows=35,765 width=1,115) (actual time=0.002..41.542 rows=35,783 loops=1)

7. 0.310 0.641 ↑ 1.0 1,403 1

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

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

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

9. 23.988 588.015 ↓ 1.1 32,972 1

Hash Join (cost=45,120.08..60,636.75 rows=28,707 width=3,393) (actual time=140.618..588.015 rows=32,972 loops=1)

  • Hash Cond: (v.vehicle_type_id = vt.id)
10. 29.507 562.265 ↓ 1.1 32,972 1

Hash Join (cost=44,973.31..60,095.25 rows=28,707 width=3,380) (actual time=138.774..562.265 rows=32,972 loops=1)

  • Hash Cond: (v.fleet_id = f.fleet_id)
11. 102.560 527.984 ↓ 1.0 32,972 1

Hash Join (cost=44,927.71..54,075.13 rows=32,042 width=1,728) (actual time=133.975..527.984 rows=32,972 loops=1)

  • Hash Cond: (vs.vehicle_id = v.id)
12. 292.424 292.424 ↓ 1.0 32,972 1

CTE Scan on states vs (cost=0.00..640.84 rows=32,042 width=52) (actual time=0.692..292.424 rows=32,972 loops=1)

13. 74.456 133.000 ↓ 1.0 35,783 1

Hash (cost=37,040.65..37,040.65 rows=35,765 width=1,680) (actual time=133.000..133.000 rows=35,783 loops=1)

  • Buckets: 16384 Batches: 4 Memory Usage: 11831kB
14. 58.544 58.544 ↓ 1.0 35,783 1

Seq Scan on vehicle v (cost=0.00..37,040.65 rows=35,765 width=1,680) (actual time=0.003..58.544 rows=35,783 loops=1)

15. 0.818 4.774 ↑ 1.0 1,403 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 581kB
16. 3.956 3.956 ↑ 1.0 1,403 1

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

17. 0.938 1.762 ↓ 1.1 4,332 1

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

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 283kB
18. 0.824 0.824 ↓ 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.824 rows=4,332 loops=1)

19. 0.028 0.045 ↓ 6.5 84 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
20. 0.017 0.017 ↓ 6.5 84 1

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

Planning time : 13.233 ms
Execution time : 4,515.364 ms