explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 371.156 879.374 ↑ 3.9 32,972 1

Hash Join (cost=107,514.82..153,281.40 rows=129,273 width=3,417) (actual time=127.458..879.374 rows=32,972 loops=1)

  • Hash Cond: (v.fleet_id = f.fleet_id)
2.          

CTE fleets

3. 3.035 3.035 ↑ 2.0 1,403 1

Seq Scan on mvw_fleet_schedule f_1 (cost=0.00..332.34 rows=2,806 width=385) (actual time=0.097..3.035 rows=1,403 loops=1)

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

CTE states

5. 204.083 242.478 ↑ 2.2 32,972 1

Hash Join (cost=91.20..60,979.57 rows=72,146 width=56) (actual time=1.097..242.478 rows=32,972 loops=1)

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

Seq Scan on vehicle v_1 (cost=0.00..37,085.64 rows=40,264 width=1,115) (actual time=0.003..37.364 rows=35,783 loops=1)

7. 0.484 1.031 ↑ 2.0 1,403 1

Hash (cost=56.12..56.12 rows=2,806 width=44) (actual time=1.031..1.031 rows=1,403 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 137kB
8. 0.547 0.547 ↑ 2.0 1,403 1

CTE Scan on fleets f_2 (cost=0.00..56.12 rows=2,806 width=44) (actual time=0.001..0.547 rows=1,403 loops=1)

9. 18.589 502.599 ↑ 2.2 32,972 1

Hash Join (cost=46,111.71..59,246.63 rows=72,146 width=1,741) (actual time=121.792..502.599 rows=32,972 loops=1)

  • Hash Cond: (v.vehicle_type_id = vt.id)
10. 97.525 482.319 ↑ 2.2 32,972 1

Hash Join (cost=45,964.94..58,185.87 rows=72,146 width=1,728) (actual time=120.070..482.319 rows=32,972 loops=1)

  • Hash Cond: (vs.vehicle_id = v.id)
11. 266.089 266.089 ↑ 2.2 32,972 1

CTE Scan on states vs (cost=0.00..1,442.92 rows=72,146 width=52) (actual time=1.101..266.089 rows=32,972 loops=1)

12. 68.991 118.705 ↑ 1.1 35,783 1

Hash (cost=37,085.64..37,085.64 rows=40,264 width=1,680) (actual time=118.705..118.705 rows=35,783 loops=1)

  • Buckets: 16384 Batches: 4 Memory Usage: 11831kB
13. 49.714 49.714 ↑ 1.1 35,783 1

Seq Scan on vehicle v (cost=0.00..37,085.64 rows=40,264 width=1,680) (actual time=0.005..49.714 rows=35,783 loops=1)

14. 0.927 1.691 ↓ 1.1 4,332 1

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

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

16. 0.915 5.619 ↑ 2.0 1,403 1

Hash (cost=56.12..56.12 rows=2,806 width=1,656) (actual time=5.619..5.619 rows=1,403 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 597kB
17. 4.704 4.704 ↑ 2.0 1,403 1

CTE Scan on fleets f (cost=0.00..56.12 rows=2,806 width=1,656) (actual time=0.102..4.704 rows=1,403 loops=1)

Planning time : 1.565 ms
Execution time : 885.532 ms