#	exclusive	inclusive	rows x	rows	loops	node
1.	1.909	51,314.328	↑ 4.9	41	1	→ GroupAggregate (cost=5,095,985.88..5,102,760.66 rows=200 width=40) (actual time=51,312.055..51,314.328 rows=41 loops=1) Group Key: trips.commodity
2.	3.128	51,312.419	↑ 45.0	6,691	1	→ Sort (cost=5,095,985.88..5,096,737.80 rows=300,768 width=60) (actual time=51,312.043..51,312.419 rows=6,691 loops=1) Sort Key: trips.commodity Sort Method: quicksort Memory: 1133kB
3.	0.718	51,309.291	↑ 45.0	6,691	1	→ Subquery Scan on trips (cost=5,056,587.84..5,068,618.56 rows=300,768 width=60) (actual time=51,304.017..51,309.291 rows=6,691 loops=1)
4.	8.483	51,308.573	↑ 45.0	6,691	1	→ Finalize HashAggregate (cost=5,056,587.84..5,065,610.88 rows=300,768 width=68) (actual time=51,304.016..51,308.573 rows=6,691 loops=1) Group Key: tblmovements.trip_id
5.	142.725	51,300.090	↑ 39.6	53,172	1	→ Gather (cost=4,800,935.04..5,014,480.32 rows=2,105,376 width=240) (actual time=51,182.206..51,300.090 rows=53,172 loops=1) Workers Planned: 7 Workers Launched: 7
6.	103.022	51,157.365	↑ 45.3	6,646	8	→ Partial HashAggregate (cost=4,799,935.04..4,802,942.72 rows=300,768 width=240) (actual time=51,149.718..51,157.365 rows=6,646 loops=8) Group Key: tblmovements.trip_id
7.	41.962	51,054.343	↑ 527.2	70,745	8	→ Parallel Hash Left Join (cost=606,961.14..3,494,520.48 rows=37,297,559 width=43) (actual time=1,954.287..51,054.343 rows=70,745 loops=8) Hash Cond: (tblsegments.trip_id = v_transit_movement_extras.trip_id)
8.	43.471	50,977.750	↑ 527.2	70,745	8	→ Parallel Hash Left Join (cost=601,105.42..3,390,757.28 rows=37,297,559 width=35) (actual time=1,919.182..50,977.750 rows=70,745 loops=8) Hash Cond: (tblsegments.trip_id = tblmovements.trip_id)
9.	3,621.258	50,878.699	↑ 527.2	70,745	8	→ Parallel Hash Join (cost=590,954.72..3,282,699.11 rows=37,297,559 width=8) (actual time=1,862.762..50,878.699 rows=70,745 loops=8) Hash Cond: (tblsegments.link_id = v_transit_roads.link_id)
10.	47,032.813	47,032.813	↑ 1.1	32,635,352	8	→ Parallel Seq Scan on tblsegments (cost=0.00..2,548,666.59 rows=37,297,559 width=16) (actual time=0.792..47,032.813 rows=32,635,352 loops=8)
11.	0.272	224.628	↑ 208.0	32	8	→ Parallel Hash (cost=590,871.52..590,871.52 rows=6,656 width=16) (actual time=224.628..224.628 rows=32 loops=8) Buckets: 32768 Batches: 1 Memory Usage: 512kB
12.	54.681	224.356	↑ 208.0	32	8	→ Parallel Hash Semi Join (cost=541,195.19..590,871.52 rows=6,656 width=16) (actual time=178.111..224.356 rows=32 loops=8) Hash Cond: (v_transit_roads.link_id = tblsegments_1.link_id)
13.	40.189	40.189	↑ 2.0	96,613	8	→ Parallel Index Only Scan using idx_v_transit_roads_link_id on v_transit_roads (cost=0.42..14,285.20 rows=193,226 width=8) (actual time=5.385..40.189 rows=96,613 loops=8) Heap Fetches: 0
14.	129.372	129.486	↑ 266,131.5	32	8	→ Parallel Hash (cost=401,475.16..401,475.16 rows=8,516,208 width=8) (actual time=129.485..129.486 rows=32 loops=8) Buckets: 33554432 Batches: 1 Memory Usage: 262176kB
15.	0.000	0.114	↑ 266,131.5	32	8	→ Nested Loop (cost=184.49..401,475.16 rows=8,516,208 width=8) (actual time=0.099..0.114 rows=32 loops=8)
16.	0.000	0.040	↓ 0.0	0	8	→ Parallel Bitmap Heap Scan on tblsegments tblsegments_2 (cost=183.92..37,087.49 rows=3,136 width=8) (actual time=0.040..0.040 rows=0 loops=8) Recheck Cond: (link_id = 1201733555) Heap Blocks: exact=1
17.	0.061	0.061	↑ 9,723.0	1	1	→ Bitmap Index Scan on seglink (cost=0.00..181.49 rows=9,723 width=0) (actual time=0.061..0.061 rows=1 loops=1) Index Cond: (link_id = 1201733555)
18.	0.558	0.558	↑ 10.6	252	1	→ Index Only Scan using idx_tblsegments_keyrev on tblsegments tblsegments_1 (cost=0.57..89.42 rows=2,678 width=16) (actual time=0.461..0.558 rows=252 loops=1) Index Cond: (trip_id = tblsegments_2.trip_id) Heap Fetches: 0
19.	41.311	55.580	↑ 3.3	37,596	8	→ Parallel Hash (cost=8,584.20..8,584.20 rows=125,320 width=27) (actual time=55.580..55.580 rows=37,596 loops=8) Buckets: 524288 Batches: 1 Memory Usage: 23936kB
20.	14.269	14.269	↑ 3.3	37,596	8	→ Parallel Seq Scan on tblmovements (cost=0.00..8,584.20 rows=125,320 width=27) (actual time=1.060..14.269 rows=37,596 loops=8)
21.	28.142	34.631	↑ 4.7	37,331	8	→ Parallel Hash (cost=3,659.76..3,659.76 rows=175,676 width=24) (actual time=34.631..34.631 rows=37,331 loops=8) Buckets: 524288 Batches: 1 Memory Usage: 20640kB
22.	6.489	6.489	↑ 4.7	37,331	8	→ Parallel Seq Scan on v_transit_movement_extras (cost=0.00..3,659.76 rows=175,676 width=24) (actual time=0.013..6.489 rows=37,331 loops=8)

"GroupAggregate  (cost=5095985.88..5102760.66 rows=200 width=40) (actual time=51312.055..51314.328 rows=41 loops=1)"
"  Group Key: trips.commodity"
"  ->  Sort  (cost=5095985.88..5096737.80 rows=300768 width=60) (actual time=51312.043..51312.419 rows=6691 loops=1)"
"        Sort Key: trips.commodity"
"        Sort Method: quicksort  Memory: 1133kB"
"        ->  Subquery Scan on trips  (cost=5056587.84..5068618.56 rows=300768 width=60) (actual time=51304.017..51309.291 rows=6691 loops=1)"
"              ->  Finalize HashAggregate  (cost=5056587.84..5065610.88 rows=300768 width=68) (actual time=51304.016..51308.573 rows=6691 loops=1)"
"                    Group Key: tblmovements.trip_id"
"                    ->  Gather  (cost=4800935.04..5014480.32 rows=2105376 width=240) (actual time=51182.206..51300.090 rows=53172 loops=1)"
"                          Workers Planned: 7"
"                          Workers Launched: 7"
"                          ->  Partial HashAggregate  (cost=4799935.04..4802942.72 rows=300768 width=240) (actual time=51149.718..51157.365 rows=6646 loops=8)"
"                                Group Key: tblmovements.trip_id"
"                                ->  Parallel Hash Left Join  (cost=606961.14..3494520.48 rows=37297559 width=43) (actual time=1954.287..51054.343 rows=70745 loops=8)"
"                                      Hash Cond: (tblsegments.trip_id = v_transit_movement_extras.trip_id)"
"                                      ->  Parallel Hash Left Join  (cost=601105.42..3390757.28 rows=37297559 width=35) (actual time=1919.182..50977.750 rows=70745 loops=8)"
"                                            Hash Cond: (tblsegments.trip_id = tblmovements.trip_id)"
"                                            ->  Parallel Hash Join  (cost=590954.72..3282699.11 rows=37297559 width=8) (actual time=1862.762..50878.699 rows=70745 loops=8)"
"                                                  Hash Cond: (tblsegments.link_id = v_transit_roads.link_id)"
"                                                  ->  Parallel Seq Scan on tblsegments  (cost=0.00..2548666.59 rows=37297559 width=16) (actual time=0.792..47032.813 rows=32635352 loops=8)"
"                                                  ->  Parallel Hash  (cost=590871.52..590871.52 rows=6656 width=16) (actual time=224.628..224.628 rows=32 loops=8)"
"                                                        Buckets: 32768  Batches: 1  Memory Usage: 512kB"
"                                                        ->  Parallel Hash Semi Join  (cost=541195.19..590871.52 rows=6656 width=16) (actual time=178.111..224.356 rows=32 loops=8)"
"                                                              Hash Cond: (v_transit_roads.link_id = tblsegments_1.link_id)"
"                                                              ->  Parallel Index Only Scan using idx_v_transit_roads_link_id on v_transit_roads  (cost=0.42..14285.20 rows=193226 width=8) (actual time=5.385..40.189 rows=96613 loops=8)"
"                                                                    Heap Fetches: 0"
"                                                              ->  Parallel Hash  (cost=401475.16..401475.16 rows=8516208 width=8) (actual time=129.485..129.486 rows=32 loops=8)"
"                                                                    Buckets: 33554432  Batches: 1  Memory Usage: 262176kB"
"                                                                    ->  Nested Loop  (cost=184.49..401475.16 rows=8516208 width=8) (actual time=0.099..0.114 rows=32 loops=8)"
"                                                                          ->  Parallel Bitmap Heap Scan on tblsegments tblsegments_2  (cost=183.92..37087.49 rows=3136 width=8) (actual time=0.040..0.040 rows=0 loops=8)"
"                                                                                Recheck Cond: (link_id = 1201733555)"
"                                                                                Heap Blocks: exact=1"
"                                                                                ->  Bitmap Index Scan on seglink  (cost=0.00..181.49 rows=9723 width=0) (actual time=0.061..0.061 rows=1 loops=1)"
"                                                                                      Index Cond: (link_id = 1201733555)"
"                                                                          ->  Index Only Scan using idx_tblsegments_keyrev on tblsegments tblsegments_1  (cost=0.57..89.42 rows=2678 width=16) (actual time=0.461..0.558 rows=252 loops=1)"
"                                                                                Index Cond: (trip_id = tblsegments_2.trip_id)"
"                                                                                Heap Fetches: 0"
"                                            ->  Parallel Hash  (cost=8584.20..8584.20 rows=125320 width=27) (actual time=55.580..55.580 rows=37596 loops=8)"
"                                                  Buckets: 524288  Batches: 1  Memory Usage: 23936kB"
"                                                  ->  Parallel Seq Scan on tblmovements  (cost=0.00..8584.20 rows=125320 width=27) (actual time=1.060..14.269 rows=37596 loops=8)"
"                                      ->  Parallel Hash  (cost=3659.76..3659.76 rows=175676 width=24) (actual time=34.631..34.631 rows=37331 loops=8)"
"                                            Buckets: 524288  Batches: 1  Memory Usage: 20640kB"
"                                            ->  Parallel Seq Scan on v_transit_movement_extras  (cost=0.00..3659.76 rows=175676 width=24) (actual time=0.013..6.489 rows=37331 loops=8)"
"Planning Time: 8.367 ms"
"Execution Time: 51382.156 ms"

Per node type stats

node type	count	sum of times	% of query
Bitmap Index Scan	1	0.061 ms	0.0 %
Finalize HashAggregate	1	8.483 ms	0.0 %
Gather	1	142.725 ms	0.3 %
GroupAggregate	1	1.909 ms	0.0 %
Index Only Scan	1	0.558 ms	0.0 %
Nested Loop	1	0.000 ms	0.0 %
Parallel Bitmap Heap Scan	1	0.000 ms	0.0 %
Parallel Hash	4	199.097 ms	0.4 %
Parallel Hash Join	1	3,621.258 ms	7.1 %
Parallel Hash Left Join	2	85.433 ms	0.2 %
Parallel Hash Semi Join	1	54.681 ms	0.1 %
Parallel Index Only Scan	1	40.189 ms	0.1 %
Parallel Seq Scan	3	47,053.571 ms	91.7 %
Partial HashAggregate	1	103.022 ms	0.2 %
Sort	1	3.128 ms	0.0 %
Subquery Scan	1	0.718 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
tblmovements	1	14.269 ms	0.0 %
Parallel Seq Scan	1	14.269 ms	100.0 %
tblsegments	3	47,033.371 ms	91.7 %
Index Only Scan	1	0.558 ms	0.0 %
Parallel Bitmap Heap Scan	1	0.000 ms	0.0 %
Parallel Seq Scan	1	47,032.813 ms	100.0 %
v_transit_movement_extras	1	6.489 ms	0.0 %
Parallel Seq Scan	1	6.489 ms	100.0 %
v_transit_roads	1	40.189 ms	0.1 %
Parallel Index Only Scan	1	40.189 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TRq

Color mode:

Visible columns:

Per node type stats

Per table stats