#	exclusive	inclusive	rows x	rows	loops	node
1.	13.513	10,152.123	↑ 1.0	1	1	→ Aggregate (cost=1,185,828.16..1,185,828.17 rows=1 width=32) (actual time=10,152.123..10,152.123 rows=1 loops=1)
2.	1.348	10,138.610	↓ 1,333.0	1,333	1	→ Subquery Scan on q (cost=1,185,812.05..1,185,828.16 rows=1 width=68) (actual time=7,963.529..10,138.610 rows=1,333 loops=1)
3.	2,046.151	10,137.262	↓ 1,333.0	1,333	1	→ GroupAggregate (cost=1,185,812.05..1,185,828.15 rows=1 width=233) (actual time=7,963.523..10,137.262 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
4.						CTE boundaries
5.	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)
6.	3,427.650	8,091.111	↓ 2,204.1	744,980	1	→ Sort (cost=1,185,735.42..1,185,736.26 rows=338 width=200) (actual time=7,963.338..8,091.111 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
7.	438.764	4,663.461	↓ 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=478.212..4,663.461 rows=744,980 loops=1) Join Filter: NULL::boolean
8.	174.540	4,224.697	↓ 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=478.211..4,224.697 rows=744,980 loops=1)
9.	425.816	2,560.197	↓ 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=478.202..2,560.197 rows=744,980 loops=1)
10.	57.023	644.421	↓ 2,204.1	744,980	1	→ Nested Loop (cost=1,180,832.68..1,181,291.06 rows=338 width=193) (actual time=478.191..644.421 rows=744,980 loops=1)
11.	28.663	502.086	↓ 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=478.165..502.086 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
12.	0.508	0.508	↓ 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=0.507..0.508 rows=2,605 loops=1) Index Cond: ((geometry && '0103000020110F000001000000050000003D0A977A991F7041AE47A194164A47C13D0A977A991F7041CDCCCC56F6E247C1E17ADC72B5327041CDCCCC56F6E247C1E17ADC72B5327041AE47A194164A47C13D0A977A991F7041AE47A194164A47C1'::geometry) AND (geometry && '0103000020110F000001000000050000003D0A977A991F7041AE47A194164A47C13D0A977A991F7041CDCCCC56F6E247C1E17ADC72B5327041CDCCCC56F6E247C1E17ADC72B5327041AE47A194164A47C13D0A977A991F7041AE47A194164A47C1'::geometry))
13.						SubPlan (forBitmap Heap Scan)
14.	227.448	472.915	↓ 1.3	35,084	1	→ HashAggregate (cost=1,180,493.70..1,180,760.93 rows=26,723 width=8) (actual time=468.431..472.915 rows=35,084 loops=1) Group Key: tblsegments_1.link_id
15.	75.643	245.467	↑ 16.9	1,763,521	1	→ Nested Loop (cost=1.14..1,105,998.94 rows=29,797,906 width=8) (actual time=0.033..245.467 rows=1,763,521 loops=1)
16.	0.160	0.160	↑ 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.014..0.160 rows=964 loops=1) Index Cond: (link_id = 945895182) Heap Fetches: 0
17.	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
18.	85.312	85.312	↑ 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.064 rows=559 loops=1,333) Index Cond: (link_id = v_transit_roads.link_id) Heap Fetches: 0
19.	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)
20.	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)
21.	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

"Aggregate  (cost=1185828.16..1185828.17 rows=1 width=32) (actual time=10152.123..10152.123 rows=1 loops=1)"
"  ->  Subquery Scan on q  (cost=1185812.05..1185828.16 rows=1 width=68) (actual time=7963.529..10138.610 rows=1333 loops=1)"
"        ->  GroupAggregate  (cost=1185812.05..1185828.15 rows=1 width=233) (actual time=7963.523..10137.262 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=7963.338..8091.111 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=478.212..4663.461 rows=744980 loops=1)"
"                          Join Filter: NULL::boolean"
"                          ->  Nested Loop Left Join  (cost=1180833.52..1185717.84 rows=338 width=200) (actual time=478.211..4224.697 rows=744980 loops=1)"
"                                ->  Nested Loop Left Join  (cost=1180833.10..1183504.39 rows=338 width=204) (actual time=478.202..2560.197 rows=744980 loops=1)"
"                                      ->  Nested Loop  (cost=1180832.68..1181291.06 rows=338 width=193) (actual time=478.191..644.421 rows=744980 loops=1)"
"                                            ->  Bitmap Heap Scan on v_transit_roads  (cost=1180832.11..1180865.81 rows=1 width=185) (actual time=478.165..502.086 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=0.507..0.508 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=468.431..472.915 rows=35084 loops=1)"
"                                                          Group Key: tblsegments_1.link_id"
"                                                          ->  Nested Loop  (cost=1.14..1105998.94 rows=29797906 width=8) (actual time=0.033..245.467 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.014..0.160 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.064 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: 1.607 ms"
"Execution time: 10171.468 ms"

Per node type stats

node type	count	sum of times	% of query
Aggregate	1	13.513 ms	0.1 %
Bitmap Heap Scan	1	28.663 ms	0.3 %
Bitmap Index Scan	1	0.508 ms	0.0 %
GroupAggregate	1	2,046.151 ms	20.2 %
HashAggregate	1	227.448 ms	2.2 %
Index Only Scan	3	255.136 ms	2.5 %
Index Scan	2	2,979.920 ms	29.4 %
Nested Loop	2	132.666 ms	1.3 %
Nested Loop Left Join	3	1,039.120 ms	10.2 %
Result	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %
Sort	1	3,427.650 ms	33.8 %
Subquery Scan	1	1.348 ms	0.0 %

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	255.136 ms	2.5 %
Index Only Scan	3	255.136 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	28.663 ms	0.3 %
Bitmap Heap Scan	1	28.663 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Q737 : springtales-dp CLA road tile test

Color mode:

Visible columns:

Per node type stats

Per table stats