#	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: 4,096 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: 16,384 Batches: 4 Memory Usage: 11,831kB
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: 8,192 (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: 4,096 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

QUERY PLAN
Hash Join  (cost=107514.82..153281.40 rows=129273 width=3417) (actual time=127.458..879.374 rows=32972 loops=1)
  Hash Cond: (v.fleet_id = f.fleet_id)
  CTE fleets
    ->  Seq Scan on mvw_fleet_schedule f_1  (cost=0.00..332.34 rows=2806 width=385) (actual time=0.097..3.035 rows=1403 loops=1)
          Filter: enabled
          Rows Removed by Filter: 358
  CTE states
    ->  Hash Join  (cost=91.20..60979.57 rows=72146 width=56) (actual time=1.097..242.478 rows=32972 loops=1)
          Hash Cond: (v_1.fleet_id = f_2.fleet_id)
          ->  Seq Scan on vehicle v_1  (cost=0.00..37085.64 rows=40264 width=1115) (actual time=0.003..37.364 rows=35783 loops=1)
          ->  Hash  (cost=56.12..56.12 rows=2806 width=44) (actual time=1.031..1.031 rows=1403 loops=1)
                Buckets: 4096  Batches: 1  Memory Usage: 137kB
                ->  CTE Scan on fleets f_2  (cost=0.00..56.12 rows=2806 width=44) (actual time=0.001..0.547 rows=1403 loops=1)
  ->  Hash Join  (cost=46111.71..59246.63 rows=72146 width=1741) (actual time=121.792..502.599 rows=32972 loops=1)
        Hash Cond: (v.vehicle_type_id = vt.id)
        ->  Hash Join  (cost=45964.94..58185.87 rows=72146 width=1728) (actual time=120.070..482.319 rows=32972 loops=1)
              Hash Cond: (vs.vehicle_id = v.id)
              ->  CTE Scan on states vs  (cost=0.00..1442.92 rows=72146 width=52) (actual time=1.101..266.089 rows=32972 loops=1)
              ->  Hash  (cost=37085.64..37085.64 rows=40264 width=1680) (actual time=118.705..118.705 rows=35783 loops=1)
                    Buckets: 16384  Batches: 4  Memory Usage: 11831kB
                    ->  Seq Scan on vehicle v  (cost=0.00..37085.64 rows=40264 width=1680) (actual time=0.005..49.714 rows=35783 loops=1)
        ->  Hash  (cost=98.01..98.01 rows=3901 width=17) (actual time=1.691..1.691 rows=4332 loops=1)
              Buckets: 8192 (originally 4096)  Batches: 1 (originally 1)  Memory Usage: 283kB
              ->  Seq Scan on vehicle_type vt  (cost=0.00..98.01 rows=3901 width=17) (actual time=0.003..0.764 rows=4332 loops=1)
  ->  Hash  (cost=56.12..56.12 rows=2806 width=1656) (actual time=5.619..5.619 rows=1403 loops=1)
        Buckets: 4096  Batches: 1  Memory Usage: 597kB
        ->  CTE Scan on fleets f  (cost=0.00..56.12 rows=2806 width=1656) (actual time=0.102..4.704 rows=1403 loops=1)
Planning time: 1.565 ms
Execution time: 885.532 ms

Per node type stats

node type	count	sum of times	% of query
CTE Scan	3	271.340 ms	30.9 %
Hash	4	71.317 ms	8.1 %
Hash Join	4	691.353 ms	78.6 %
Seq Scan	4	90.877 ms	10.3 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
mvw_fleet_schedule	1	3.035 ms	0.3 %
Seq Scan	1	3.035 ms	100.0 %
vehicle	2	87.078 ms	9.9 %
Seq Scan	2	87.078 ms	100.0 %
vehicle_type	1	0.764 ms	0.1 %
Seq Scan	1	0.764 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

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

Color mode:

Visible columns:

Optimization path:

Per node type stats

Per table stats