#	exclusive	inclusive	rows x	rows	loops	node
1.	13.659	50,399.486	↑ 1.0	1	1	→ Aggregate (cost=6,670.14..6,670.15 rows=1 width=32) (actual time=50,399.486..50,399.486 rows=1 loops=1)
2.	1.685	50,385.827	↓ 417.0	417	1	→ Subquery Scan on q (cost=6,632.54..6,670.13 rows=1 width=44) (actual time=29,399.653..50,385.827 rows=417 loops=1)
3.	18,929.864	50,384.142	↓ 417.0	417	1	→ GroupAggregate (cost=6,632.54..6,670.12 rows=1 width=253) (actual time=29,399.634..50,384.142 rows=417 loops=1) Group Key: v_transit_roads.link_id, v_transit_roads.street_name, v_transit_roads.road_speed, v_transit_roads.road_rank, v_transit_roads.truck, v_transit_roads.surface, v_transit_roads.road_km, v_transit_roads.geometry
4.						CTE boundaries
5.	0.000	0.000	↓ 0.0		0	→ Seq Scan on v_transit_boundaries (cost=0.00..910.46 rows=3,346 width=75,373) (never executed)
6.	11,930.518	31,454.278	↓ 5,599.9	3,819,120	1	→ Sort (cost=5,722.08..5,723.79 rows=682 width=209) (actual time=29,392.342..31,454.278 rows=3,819,120 loops=1) Sort Key: v_transit_roads.link_id, v_transit_roads.street_name, v_transit_roads.road_speed, v_transit_roads.road_rank, v_transit_roads.truck, v_transit_roads.surface, v_transit_roads.road_km, v_transit_roads.geometry Sort Method: external merge Disk: 672944kB
7.	1,747.763	19,523.760	↓ 5,599.9	3,819,120	1	→ Nested Loop Left Join (cost=5.73..5,689.98 rows=682 width=209) (actual time=0.343..19,523.760 rows=3,819,120 loops=1) Join Filter: NULL::boolean
8.	3,277.325	17,775.997	↓ 5,599.9	3,819,120	1	→ Nested Loop Left Join (cost=5.73..5,683.16 rows=682 width=209) (actual time=0.340..17,775.997 rows=3,819,120 loops=1)
9.	708.522	3,041.312	↓ 5,599.9	3,819,120	1	→ Nested Loop (cost=5.31..463.10 rows=682 width=193) (actual time=0.320..3,041.312 rows=3,819,120 loops=1)
10.	30.410	30.533	↓ 417.0	417	1	→ Bitmap Heap Scan on v_transit_roads (cost=4.73..174.86 rows=1 width=185) (actual time=0.244..30.533 rows=417 loops=1) Recheck Cond: ((street_name)::text = ANY ('{"Gore Hwy"}'::text[])) Filter: ((geometry && '0103000020110F00000100000005000000EC511814C3F26F41CDCCCC56F6E247C1EC511814C3F26F410AD723DBB51449C13D0A977A991F70410AD723DBB51449C13D0A977A991F7041CDCCCC56F6E247C1EC511814C3F26F41CDCCCC56F6E247C1'::geometry) AND _st_intersects(geometry, '0103000020110F00000100000005000000EC511814C3F26F41CDCCCC56F6E247C1EC511814C3F26F410AD723DBB51449C13D0A977A991F70410AD723DBB51449C13D0A977A991F7041CDCCCC56F6E247C1EC511814C3F26F41CDCCCC56F6E247C1'::geometry)) Rows Removed by Filter: 32 Heap Blocks: exact=119
11.	0.123	0.123	↓ 11.0	449	1	→ Bitmap Index Scan on idx_v_transit_roads_street_name (cost=0.00..4.73 rows=41 width=0) (actual time=0.122..0.123 rows=449 loops=1) Index Cond: ((street_name)::text = ANY ('{"Gore Hwy"}'::text[]))
12.	2,302.257	2,302.257	↓ 1.8	9,159	417	→ Index Only Scan using idx_road_segments_key on road_segments (cost=0.57..236.00 rows=5,224 width=16) (actual time=0.023..5.521 rows=9,159 loops=417) Index Cond: (link_id = v_transit_roads.link_id) Heap Fetches: 0
13.	11,457.360	11,457.360	↑ 1.0	1	3,819,120	→ Index Scan using trip on road_movements (cost=0.42..7.65 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=3,819,120) Index Cond: (road_segments.trip_id = trip_id)
14.	0.000	0.000	↓ 0.0	0	3,819,120	→ Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.000..0.000 rows=0 loops=3,819,120) One-Time Filter: false

Planning time	:	2.188 ms

"Aggregate  (cost=6670.14..6670.15 rows=1 width=32) (actual time=50399.486..50399.486 rows=1 loops=1)"
"  ->  Subquery Scan on q  (cost=6632.54..6670.13 rows=1 width=44) (actual time=29399.653..50385.827 rows=417 loops=1)"
"        ->  GroupAggregate  (cost=6632.54..6670.12 rows=1 width=253) (actual time=29399.634..50384.142 rows=417 loops=1)"
"              Group Key: v_transit_roads.link_id, v_transit_roads.street_name, v_transit_roads.road_speed, v_transit_roads.road_rank, v_transit_roads.truck, v_transit_roads.surface, v_transit_roads.road_km, v_transit_roads.geometry"
"              CTE boundaries"
"                ->  Seq Scan on v_transit_boundaries  (cost=0.00..910.46 rows=3346 width=75373) (never executed)"
"              ->  Sort  (cost=5722.08..5723.79 rows=682 width=209) (actual time=29392.342..31454.278 rows=3819120 loops=1)"
"                    Sort Key: v_transit_roads.link_id, v_transit_roads.street_name, v_transit_roads.road_speed, v_transit_roads.road_rank, v_transit_roads.truck, v_transit_roads.surface, v_transit_roads.road_km, v_transit_roads.geometry"
"                    Sort Method: external merge  Disk: 672944kB"
"                    ->  Nested Loop Left Join  (cost=5.73..5689.98 rows=682 width=209) (actual time=0.343..19523.760 rows=3819120 loops=1)"
"                          Join Filter: NULL::boolean"
"                          ->  Nested Loop Left Join  (cost=5.73..5683.16 rows=682 width=209) (actual time=0.340..17775.997 rows=3819120 loops=1)"
"                                ->  Nested Loop  (cost=5.31..463.10 rows=682 width=193) (actual time=0.320..3041.312 rows=3819120 loops=1)"
"                                      ->  Bitmap Heap Scan on v_transit_roads  (cost=4.73..174.86 rows=1 width=185) (actual time=0.244..30.533 rows=417 loops=1)"
"                                            Recheck Cond: ((street_name)::text = ANY ('{"Gore Hwy"}'::text[]))"
"                                            Filter: ((geometry && '0103000020110F00000100000005000000EC511814C3F26F41CDCCCC56F6E247C1EC511814C3F26F410AD723DBB51449C13D0A977A991F70410AD723DBB51449C13D0A977A991F7041CDCCCC56F6E247C1EC511814C3F26F41CDCCCC56F6E247C1'::geometry) AND _st_intersects(geometry, '0103000020110F00000100000005000000EC511814C3F26F41CDCCCC56F6E247C1EC511814C3F26F410AD723DBB51449C13D0A977A991F70410AD723DBB51449C13D0A977A991F7041CDCCCC56F6E247C1EC511814C3F26F41CDCCCC56F6E247C1'::geometry))"
"                                            Rows Removed by Filter: 32"
"                                            Heap Blocks: exact=119"
"                                            ->  Bitmap Index Scan on idx_v_transit_roads_street_name  (cost=0.00..4.73 rows=41 width=0) (actual time=0.122..0.123 rows=449 loops=1)"
"                                                  Index Cond: ((street_name)::text = ANY ('{"Gore Hwy"}'::text[]))"
"                                      ->  Index Only Scan using idx_road_segments_key on road_segments  (cost=0.57..236.00 rows=5224 width=16) (actual time=0.023..5.521 rows=9159 loops=417)"
"                                            Index Cond: (link_id = v_transit_roads.link_id)"
"                                            Heap Fetches: 0"
"                                ->  Index Scan using trip on road_movements  (cost=0.42..7.65 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=3819120)"
"                                      Index Cond: (road_segments.trip_id = trip_id)"
"                          ->  Result  (cost=0.00..0.00 rows=0 width=0) (actual time=0.000..0.000 rows=0 loops=3819120)"
"                                One-Time Filter: false"
"Planning Time: 2.188 ms"
"Execution Time: 50557.734 ms"

Per node type stats

node type	count	sum of times	% of query
Aggregate	1	13.659 ms	0.0 %
Bitmap Heap Scan	1	30.410 ms	0.1 %
Bitmap Index Scan	1	0.123 ms	0.0 %
GroupAggregate	1	18,929.864 ms	37.6 %
Index Only Scan	1	2,302.257 ms	4.6 %
Index Scan	1	11,457.360 ms	22.7 %
Nested Loop	1	708.522 ms	1.4 %
Nested Loop Left Join	2	5,025.088 ms	10.0 %
Result	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %
Sort	1	11,930.518 ms	23.7 %
Subquery Scan	1	1.685 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
road_movements	1	11,457.360 ms	22.7 %
Index Scan	1	11,457.360 ms	100.0 %
road_segments	1	2,302.257 ms	4.6 %
Index Only Scan	1	2,302.257 ms	100.0 %
v_transit_boundaries	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %
v_transit_roads	1	30.410 ms	0.1 %
Bitmap Heap Scan	1	30.410 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BaJT

Color mode:

Visible columns:

Per node type stats

Per table stats