#	exclusive	inclusive	rows x	rows	loops	node
1.	2,057.693	10,159.653	↓ 1,333.0	1,333	1	→ GroupAggregate (cost=1,185,812.05..1,185,828.15 rows=1 width=233) (actual time=7,970.567..10,159.653 rows=1,333 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
2.						CTE boundaries
3.	0.000	0.000	↓ 0.0		0	→ Seq Scan on v_transit_boundaries (cost=0.00..76.63 rows=563 width=102,301) (never executed)
4.	3,438.788	8,101.960	↓ 2,204.1	744,980	1	→ Sort (cost=1,185,735.42..1,185,736.26 rows=338 width=200) (actual time=7,970.322..8,101.960 rows=744,980 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 sort Disk: 136520kB
5.	437.462	4,663.172	↓ 2,204.1	744,980	1	→ Nested Loop Left Join (cost=1,180,833.52..1,185,721.22 rows=338 width=200) (actual time=477.824..4,663.172 rows=744,980 loops=1) Join Filter: NULL::boolean
6.	184.189	4,225.710	↓ 2,204.1	744,980	1	→ Nested Loop Left Join (cost=1,180,833.52..1,185,717.84 rows=338 width=200) (actual time=477.823..4,225.710 rows=744,980 loops=1)
7.	423.294	2,551.561	↓ 2,204.1	744,980	1	→ Nested Loop Left Join (cost=1,180,833.10..1,183,504.39 rows=338 width=204) (actual time=477.815..2,551.561 rows=744,980 loops=1)
8.	54.546	638.307	↓ 2,204.1	744,980	1	→ Nested Loop (cost=1,180,832.68..1,181,291.06 rows=338 width=193) (actual time=477.794..638.307 rows=744,980 loops=1)
9.	30.101	502.448	↓ 1,333.0	1,333	1	→ Bitmap Heap Scan on v_transit_roads (cost=1,180,832.11..1,180,865.81 rows=1 width=185) (actual time=477.773..502.448 rows=1,333 loops=1) Recheck Cond: ((geometry && '0103000020110F000001000000050000003D0A977A991F7041AE47A194164A47C13D0A977A991F7041CDCCCC56F6E247C1E17ADC72B5327041CDCCCC56F6E247C1E17ADC72B5327041AE47A194164A47C13D0A977A991F7041AE47A194164A47C1'::geometry) AND (geometry && '0103000020110F000001000000050000003D0A977A991F7041AE47A194164A47C13D0A977A991F7041CDCCCC56F6E247C1E17ADC72B5327041CDCCCC56F6E247C1E17ADC72B5327041AE47A194164A47C13D0A977A991F7041AE47A194164A47C1'::geometry)) Filter: ((hashed SubPlan 2) AND _st_intersects(geometry, '0103000020110F000001000000050000003D0A977A991F7041AE47A194164A47C13D0A977A991F7041CDCCCC56F6E247C1E17ADC72B5327041CDCCCC56F6E247C1E17ADC72B5327041AE47A194164A47C13D0A977A991F7041AE47A194164A47C1'::geometry)) Rows Removed by Filter: 1272 Heap Blocks: exact=563
10.	1.850	1.850	↓ 325.6	2,605	1	→ Bitmap Index Scan on v_transit_roads_geometry_geom_idx (cost=0.00..4.37 rows=8 width=0) (actual time=1.850..1.850 rows=2,605 loops=1) Index Cond: ((geometry && '0103000020110F000001000000050000003D0A977A991F7041AE47A194164A47C13D0A977A991F7041CDCCCC56F6E247C1E17ADC72B5327041CDCCCC56F6E247C1E17ADC72B5327041AE47A194164A47C13D0A977A991F7041AE47A194164A47C1'::geometry) AND (geometry && '0103000020110F000001000000050000003D0A977A991F7041AE47A194164A47C13D0A977A991F7041CDCCCC56F6E247C1E17ADC72B5327041CDCCCC56F6E247C1E17ADC72B5327041AE47A194164A47C13D0A977A991F7041AE47A194164A47C1'::geometry))
11.						SubPlan (forBitmap Heap Scan)
12.	230.980	470.497	↓ 1.3	35,084	1	→ HashAggregate (cost=1,180,493.70..1,180,760.93 rows=26,723 width=8) (actual time=465.957..470.497 rows=35,084 loops=1) Group Key: tblsegments_1.link_id
13.	69.638	239.517	↑ 16.9	1,763,521	1	→ Nested Loop (cost=1.14..1,105,998.94 rows=29,797,906 width=8) (actual time=0.119..239.517 rows=1,763,521 loops=1)
14.	0.215	0.215	↑ 9.9	964	1	→ Index Only Scan using idx_tblsegments_key on tblsegments tblsegments_2 (cost=0.57..320.32 rows=9,586 width=8) (actual time=0.074..0.215 rows=964 loops=1) Index Cond: (link_id = 945895182) Heap Fetches: 0
15.	169.664	169.664	↑ 1.5	1,829	964	→ Index Only Scan using idx_tblsegments_keyrev on tblsegments tblsegments_1 (cost=0.57..88.81 rows=2,653 width=16) (actual time=0.008..0.176 rows=1,829 loops=964) Index Cond: (trip_id = tblsegments_2.trip_id) Heap Fetches: 0
16.	81.313	81.313	↑ 17.5	559	1,333	→ Index Only Scan using idx_tblsegments_key on tblsegments (cost=0.57..327.54 rows=9,770 width=16) (actual time=0.006..0.061 rows=559 loops=1,333) Index Cond: (link_id = v_transit_roads.link_id) Heap Fetches: 0
17.	1,489.960	1,489.960	↑ 1.0	1	744,980	→ Index Scan using idx_tblmovements_agg on tblmovements (cost=0.42..6.55 rows=1 width=19) (actual time=0.002..0.002 rows=1 loops=744,980) Index Cond: (tblsegments.trip_id = trip_id)
18.	1,489.960	1,489.960	↑ 1.0	1	744,980	→ Index Scan using v_transit_movementcosts_trip_id on v_transit_movementcosts (cost=0.42..6.55 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=744,980) Index Cond: (tblsegments.trip_id = trip_id)
19.	0.000	0.000	↓ 0.0	0	744,980	→ Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.000..0.000 rows=0 loops=744,980) One-Time Filter: false

"GroupAggregate  (cost=1185812.05..1185828.15 rows=1 width=233) (actual time=7970.567..10159.653 rows=1333 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..76.63 rows=563 width=102301) (never executed)"
"  ->  Sort  (cost=1185735.42..1185736.26 rows=338 width=200) (actual time=7970.322..8101.960 rows=744980 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 sort  Disk: 136520kB"
"        ->  Nested Loop Left Join  (cost=1180833.52..1185721.22 rows=338 width=200) (actual time=477.824..4663.172 rows=744980 loops=1)"
"              Join Filter: NULL::boolean"
"              ->  Nested Loop Left Join  (cost=1180833.52..1185717.84 rows=338 width=200) (actual time=477.823..4225.710 rows=744980 loops=1)"
"                    ->  Nested Loop Left Join  (cost=1180833.10..1183504.39 rows=338 width=204) (actual time=477.815..2551.561 rows=744980 loops=1)"
"                          ->  Nested Loop  (cost=1180832.68..1181291.06 rows=338 width=193) (actual time=477.794..638.307 rows=744980 loops=1)"
"                                ->  Bitmap Heap Scan on v_transit_roads  (cost=1180832.11..1180865.81 rows=1 width=185) (actual time=477.773..502.448 rows=1333 loops=1)"
"                                      Recheck Cond: ((geometry && '0103000020110F000001000000050000003D0A977A991F7041AE47A194164A47C13D0A977A991F7041CDCCCC56F6E247C1E17ADC72B5327041CDCCCC56F6E247C1E17ADC72B5327041AE47A194164A47C13D0A977A991F7041AE47A194164A47C1'::geometry) AND (geometry && '0103000020110F000001000000050000003D0A977A991F7041AE47A194164A47C13D0A977A991F7041CDCCCC56F6E247C1E17ADC72B5327041CDCCCC56F6E247C1E17ADC72B5327041AE47A194164A47C13D0A977A991F7041AE47A194164A47C1'::geometry))"
"                                      Filter: ((hashed SubPlan 2) AND _st_intersects(geometry, '0103000020110F000001000000050000003D0A977A991F7041AE47A194164A47C13D0A977A991F7041CDCCCC56F6E247C1E17ADC72B5327041CDCCCC56F6E247C1E17ADC72B5327041AE47A194164A47C13D0A977A991F7041AE47A194164A47C1'::geometry))"
"                                      Rows Removed by Filter: 1272"
"                                      Heap Blocks: exact=563"
"                                      ->  Bitmap Index Scan on v_transit_roads_geometry_geom_idx  (cost=0.00..4.37 rows=8 width=0) (actual time=1.850..1.850 rows=2605 loops=1)"
"                                            Index Cond: ((geometry && '0103000020110F000001000000050000003D0A977A991F7041AE47A194164A47C13D0A977A991F7041CDCCCC56F6E247C1E17ADC72B5327041CDCCCC56F6E247C1E17ADC72B5327041AE47A194164A47C13D0A977A991F7041AE47A194164A47C1'::geometry) AND (geometry && '0103000020110F000001000000050000003D0A977A991F7041AE47A194164A47C13D0A977A991F7041CDCCCC56F6E247C1E17ADC72B5327041CDCCCC56F6E247C1E17ADC72B5327041AE47A194164A47C13D0A977A991F7041AE47A194164A47C1'::geometry))"
"                                      SubPlan 2"
"                                        ->  HashAggregate  (cost=1180493.70..1180760.93 rows=26723 width=8) (actual time=465.957..470.497 rows=35084 loops=1)"
"                                              Group Key: tblsegments_1.link_id"
"                                              ->  Nested Loop  (cost=1.14..1105998.94 rows=29797906 width=8) (actual time=0.119..239.517 rows=1763521 loops=1)"
"                                                    ->  Index Only Scan using idx_tblsegments_key on tblsegments tblsegments_2  (cost=0.57..320.32 rows=9586 width=8) (actual time=0.074..0.215 rows=964 loops=1)"
"                                                          Index Cond: (link_id = 945895182)"
"                                                          Heap Fetches: 0"
"                                                    ->  Index Only Scan using idx_tblsegments_keyrev on tblsegments tblsegments_1  (cost=0.57..88.81 rows=2653 width=16) (actual time=0.008..0.176 rows=1829 loops=964)"
"                                                          Index Cond: (trip_id = tblsegments_2.trip_id)"
"                                                          Heap Fetches: 0"
"                                ->  Index Only Scan using idx_tblsegments_key on tblsegments  (cost=0.57..327.54 rows=9770 width=16) (actual time=0.006..0.061 rows=559 loops=1333)"
"                                      Index Cond: (link_id = v_transit_roads.link_id)"
"                                      Heap Fetches: 0"
"                          ->  Index Scan using idx_tblmovements_agg on tblmovements  (cost=0.42..6.55 rows=1 width=19) (actual time=0.002..0.002 rows=1 loops=744980)"
"                                Index Cond: (tblsegments.trip_id = trip_id)"
"                    ->  Index Scan using v_transit_movementcosts_trip_id on v_transit_movementcosts  (cost=0.42..6.55 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=744980)"
"                          Index Cond: (tblsegments.trip_id = trip_id)"
"              ->  Result  (cost=0.00..0.00 rows=0 width=0) (actual time=0.000..0.000 rows=0 loops=744980)"
"                    One-Time Filter: false"
"Planning time: 2.840 ms"
"Execution time: 10179.982 ms"

Per node type stats

node type	count	sum of times	% of query
Bitmap Heap Scan	1	30.101 ms	0.3 %
Bitmap Index Scan	1	1.850 ms	0.0 %
GroupAggregate	1	2,057.693 ms	20.3 %
HashAggregate	1	230.980 ms	2.3 %
Index Only Scan	3	251.192 ms	2.5 %
Index Scan	2	2,979.920 ms	29.3 %
Nested Loop	2	124.184 ms	1.2 %
Nested Loop Left Join	3	1,044.945 ms	10.3 %
Result	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %
Sort	1	3,438.788 ms	33.8 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
tblmovements	1	1,489.960 ms	14.7 %
Index Scan	1	1,489.960 ms	100.0 %
tblsegments	3	251.192 ms	2.5 %
Index Only Scan	3	251.192 ms	100.0 %
v_transit_boundaries	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %
v_transit_movementcosts	1	1,489.960 ms	14.7 %
Index Scan	1	1,489.960 ms	100.0 %
v_transit_roads	1	30.101 ms	0.3 %
Bitmap Heap Scan	1	30.101 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eElw : springtales-dp

Color mode:

Visible columns:

Per node type stats

Per table stats