#	exclusive	inclusive	rows x	rows	loops	node
1.	2.002	49,651.769	↑ 4.9	41	1	→ GroupAggregate (cost=8,985,199.86..8,991,974.64 rows=200 width=40) (actual time=49,649.432..49,651.769 rows=41 loops=1) Group Key: trips.commodity
2.	2.579	49,649.767	↑ 45.0	6,691	1	→ Sort (cost=8,985,199.86..8,985,951.78 rows=300,768 width=60) (actual time=49,649.422..49,649.767 rows=6,691 loops=1) Sort Key: trips.commodity Sort Method: quicksort Memory: 1133kB
3.	0.839	49,647.188	↑ 45.0	6,691	1	→ Subquery Scan on trips (cost=8,863,587.00..8,957,832.54 rows=300,768 width=60) (actual time=49,626.734..49,647.188 rows=6,691 loops=1)
4.	0.000	49,646.349	↑ 45.0	6,691	1	→ Finalize GroupAggregate (cost=8,863,587.00..8,954,824.86 rows=300,768 width=68) (actual time=49,626.732..49,646.349 rows=6,691 loops=1) Group Key: tblmovements.trip_id
5.	0.000	49,715.172	↑ 30.0	20,073	1	→ Gather Merge (cost=8,863,587.00..8,933,771.10 rows=601,536 width=240) (actual time=49,626.715..49,715.172 rows=20,073 loops=1) Workers Planned: 2 Workers Launched: 2
6.	13.407	148,845.468	↑ 45.0	6,691	3	→ Sort (cost=8,862,586.98..8,863,338.90 rows=300,768 width=240) (actual time=49,614.352..49,615.156 rows=6,691 loops=3) Sort Key: tblmovements.trip_id Sort Method: quicksort Memory: 3643kB Worker 0: Sort Method: quicksort Memory: 3643kB Worker 1: Sort Method: quicksort Memory: 3643kB
7.	575.160	148,832.061	↑ 45.0	6,691	3	→ Partial HashAggregate (cost=8,832,211.98..8,835,219.66 rows=300,768 width=240) (actual time=49,604.546..49,610.687 rows=6,691 loops=3) Group Key: tblmovements.trip_id
8.	240.564	148,256.901	↑ 576.6	188,654	3	→ Parallel Hash Left Join (cost=766,532.79..5,024,752.84 rows=108,784,547 width=43) (actual time=1,263.544..49,418.967 rows=188,654 loops=3) Hash Cond: (tblsegments.trip_id = v_transit_movement_extras.trip_id)
9.	256.083	147,899.742	↑ 576.6	188,654	3	→ Parallel Hash Left Join (cost=760,677.07..4,733,333.64 rows=108,784,547 width=35) (actual time=1,223.374..49,299.914 rows=188,654 loops=3) Hash Cond: (tblsegments.trip_id = tblmovements.trip_id)
10.	28,627.401	147,462.135	↑ 576.6	188,654	3	→ Parallel Hash Join (cost=750,526.37..4,437,619.47 rows=108,784,547 width=8) (actual time=1,162.056..49,154.045 rows=188,654 loops=3) Hash Cond: (tblsegments.link_id = v_transit_roads.link_id)
11.	117,311.214	117,311.214	↑ 1.3	87,027,606	3	→ Parallel Seq Scan on tblsegments (cost=0.00..3,263,536.47 rows=108,784,547 width=16) (actual time=0.012..39,103.738 rows=87,027,606 loops=3)
12.	21.084	1,523.520	↑ 132.1	84	3	→ Parallel Hash (cost=750,387.70..750,387.70 rows=11,094 width=16) (actual time=507.839..507.840 rows=84 loops=3) Buckets: 32768 Batches: 1 Memory Usage: 352kB
13.	1,011.234	1,502.436	↑ 132.1	84	3	→ Parallel Hash Semi Join (cost=688,276.77..750,387.70 rows=11,094 width=16) (actual time=364.808..500.812 rows=84 loops=3) Hash Cond: (v_transit_roads.link_id = tblsegments_1.link_id)
14.	95.100	95.100	↑ 1.3	257,634	3	→ Parallel Index Only Scan using idx_v_transit_roads_link_id on v_transit_roads (cost=0.42..15,573.37 rows=322,043 width=8) (actual time=0.249..31.700 rows=257,634 loops=3) Heap Fetches: 0
15.	395.151	396.102	↑ 130,953.6	84	3	→ Parallel Hash (cost=507,805.07..507,805.07 rows=11,000,102 width=8) (actual time=132.033..132.034 rows=84 loops=3) Buckets: 8388608 Batches: 8 Memory Usage: 65568kB
16.	0.037	0.951	↑ 130,953.6	84	3	→ Nested Loop (cost=184.49..507,805.07 rows=11,000,102 width=8) (actual time=0.260..0.317 rows=84 loops=3)
17.	0.108	0.180	↓ 0.0	0	3	→ Parallel Bitmap Heap Scan on tblsegments tblsegments_2 (cost=183.92..37,098.93 rows=4,051 width=8) (actual time=0.060..0.060 rows=0 loops=3) Recheck Cond: (link_id = 1201733555)
18.	0.072	0.072	↑ 9,723.0	1	1	→ Bitmap Index Scan on seglink (cost=0.00..181.49 rows=9,723 width=0) (actual time=0.072..0.072 rows=1 loops=1) Index Cond: (link_id = 1201733555)
19.	0.734	0.734	↑ 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.591..0.734 rows=252 loops=1) Index Cond: (trip_id = tblsegments_2.trip_id) Heap Fetches: 0
20.	89.574	181.524	↑ 1.2	100,256	3	→ Parallel Hash (cost=8,584.20..8,584.20 rows=125,320 width=27) (actual time=60.508..60.508 rows=100,256 loops=3) Buckets: 524288 Batches: 1 Memory Usage: 23776kB
21.	91.950	91.950	↑ 1.2	100,256	3	→ Parallel Seq Scan on tblmovements (cost=0.00..8,584.20 rows=125,320 width=27) (actual time=0.210..30.650 rows=100,256 loops=3)
22.	74.241	116.595	↑ 1.8	99,550	3	→ Parallel Hash (cost=3,659.76..3,659.76 rows=175,676 width=24) (actual time=38.865..38.865 rows=99,550 loops=3) Buckets: 524288 Batches: 1 Memory Usage: 20512kB
23.	42.354	42.354	↑ 1.8	99,550	3	→ Parallel Seq Scan on v_transit_movement_extras (cost=0.00..3,659.76 rows=175,676 width=24) (actual time=0.012..14.118 rows=99,550 loops=3)

"GroupAggregate  (cost=8985199.86..8991974.64 rows=200 width=40) (actual time=49649.432..49651.769 rows=41 loops=1)"
"  Group Key: trips.commodity"
"  ->  Sort  (cost=8985199.86..8985951.78 rows=300768 width=60) (actual time=49649.422..49649.767 rows=6691 loops=1)"
"        Sort Key: trips.commodity"
"        Sort Method: quicksort  Memory: 1133kB"
"        ->  Subquery Scan on trips  (cost=8863587.00..8957832.54 rows=300768 width=60) (actual time=49626.734..49647.188 rows=6691 loops=1)"
"              ->  Finalize GroupAggregate  (cost=8863587.00..8954824.86 rows=300768 width=68) (actual time=49626.732..49646.349 rows=6691 loops=1)"
"                    Group Key: tblmovements.trip_id"
"                    ->  Gather Merge  (cost=8863587.00..8933771.10 rows=601536 width=240) (actual time=49626.715..49715.172 rows=20073 loops=1)"
"                          Workers Planned: 2"
"                          Workers Launched: 2"
"                          ->  Sort  (cost=8862586.98..8863338.90 rows=300768 width=240) (actual time=49614.352..49615.156 rows=6691 loops=3)"
"                                Sort Key: tblmovements.trip_id"
"                                Sort Method: quicksort  Memory: 3643kB"
"                                Worker 0:  Sort Method: quicksort  Memory: 3643kB"
"                                Worker 1:  Sort Method: quicksort  Memory: 3643kB"
"                                ->  Partial HashAggregate  (cost=8832211.98..8835219.66 rows=300768 width=240) (actual time=49604.546..49610.687 rows=6691 loops=3)"
"                                      Group Key: tblmovements.trip_id"
"                                      ->  Parallel Hash Left Join  (cost=766532.79..5024752.84 rows=108784547 width=43) (actual time=1263.544..49418.967 rows=188654 loops=3)"
"                                            Hash Cond: (tblsegments.trip_id = v_transit_movement_extras.trip_id)"
"                                            ->  Parallel Hash Left Join  (cost=760677.07..4733333.64 rows=108784547 width=35) (actual time=1223.374..49299.914 rows=188654 loops=3)"
"                                                  Hash Cond: (tblsegments.trip_id = tblmovements.trip_id)"
"                                                  ->  Parallel Hash Join  (cost=750526.37..4437619.47 rows=108784547 width=8) (actual time=1162.056..49154.045 rows=188654 loops=3)"
"                                                        Hash Cond: (tblsegments.link_id = v_transit_roads.link_id)"
"                                                        ->  Parallel Seq Scan on tblsegments  (cost=0.00..3263536.47 rows=108784547 width=16) (actual time=0.012..39103.738 rows=87027606 loops=3)"
"                                                        ->  Parallel Hash  (cost=750387.70..750387.70 rows=11094 width=16) (actual time=507.839..507.840 rows=84 loops=3)"
"                                                              Buckets: 32768  Batches: 1  Memory Usage: 352kB"
"                                                              ->  Parallel Hash Semi Join  (cost=688276.77..750387.70 rows=11094 width=16) (actual time=364.808..500.812 rows=84 loops=3)"
"                                                                    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..15573.37 rows=322043 width=8) (actual time=0.249..31.700 rows=257634 loops=3)"
"                                                                          Heap Fetches: 0"
"                                                                    ->  Parallel Hash  (cost=507805.07..507805.07 rows=11000102 width=8) (actual time=132.033..132.034 rows=84 loops=3)"
"                                                                          Buckets: 8388608  Batches: 8  Memory Usage: 65568kB"
"                                                                          ->  Nested Loop  (cost=184.49..507805.07 rows=11000102 width=8) (actual time=0.260..0.317 rows=84 loops=3)"
"                                                                                ->  Parallel Bitmap Heap Scan on tblsegments tblsegments_2  (cost=183.92..37098.93 rows=4051 width=8) (actual time=0.060..0.060 rows=0 loops=3)"
"                                                                                      Recheck Cond: (link_id = 1201733555)"
"                                                                                      ->  Bitmap Index Scan on seglink  (cost=0.00..181.49 rows=9723 width=0) (actual time=0.072..0.072 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.591..0.734 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=60.508..60.508 rows=100256 loops=3)"
"                                                        Buckets: 524288  Batches: 1  Memory Usage: 23776kB"
"                                                        ->  Parallel Seq Scan on tblmovements  (cost=0.00..8584.20 rows=125320 width=27) (actual time=0.210..30.650 rows=100256 loops=3)"
"                                            ->  Parallel Hash  (cost=3659.76..3659.76 rows=175676 width=24) (actual time=38.865..38.865 rows=99550 loops=3)"
"                                                  Buckets: 524288  Batches: 1  Memory Usage: 20512kB"
"                                                  ->  Parallel Seq Scan on v_transit_movement_extras  (cost=0.00..3659.76 rows=175676 width=24) (actual time=0.012..14.118 rows=99550 loops=3)"
"Planning Time: 1.686 ms"
"Execution Time: 49741.538 ms"

Per node type stats

node type	count	sum of times	% of query
Bitmap Index Scan	1	0.072 ms	0.0 %
Finalize GroupAggregate	1	0.000 ms	0.0 %
Gather Merge	1	0.000 ms	0.0 %
GroupAggregate	1	2.002 ms	0.0 %
Index Only Scan	1	0.734 ms	0.0 %
Nested Loop	1	0.037 ms	0.0 %
Parallel Bitmap Heap Scan	1	0.108 ms	0.0 %
Parallel Hash	4	580.050 ms	1.2 %
Parallel Hash Join	1	28,627.401 ms	57.7 %
Parallel Hash Left Join	2	496.647 ms	1.0 %
Parallel Hash Semi Join	1	1,011.234 ms	2.0 %
Parallel Index Only Scan	1	95.100 ms	0.2 %
Parallel Seq Scan	3	117,445.518 ms	236.5 %
Partial HashAggregate	1	575.160 ms	1.2 %
Sort	2	15.986 ms	0.0 %
Subquery Scan	1	0.839 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
tblmovements	1	91.950 ms	0.2 %
Parallel Seq Scan	1	91.950 ms	100.0 %
tblsegments	3	117,312.056 ms	236.3 %
Index Only Scan	1	0.734 ms	0.0 %
Parallel Bitmap Heap Scan	1	0.108 ms	0.0 %
Parallel Seq Scan	1	117,311.214 ms	100.0 %
v_transit_movement_extras	1	42.354 ms	0.1 %
Parallel Seq Scan	1	42.354 ms	100.0 %
v_transit_roads	1	95.100 ms	0.2 %
Parallel Index Only Scan	1	95.100 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JI75

Color mode:

Visible columns:

Per node type stats

Per table stats