explain.depesz.com

PostgreSQL's explain analyze made readable

Result: I3T

Settings
# exclusive inclusive rows x rows loops node
1. 1.940 50,469.790 ↑ 4.9 41 1

GroupAggregate (cost=5,095,985.88..5,102,760.66 rows=200 width=40) (actual time=50,467.483..50,469.790 rows=41 loops=1)

  • Group Key: trips.commodity
2. 3.224 50,467.850 ↑ 45.0 6,691 1

Sort (cost=5,095,985.88..5,096,737.80 rows=300,768 width=60) (actual time=50,467.471..50,467.850 rows=6,691 loops=1)

  • Sort Key: trips.commodity
  • Sort Method: quicksort Memory: 1133kB
3. 0.726 50,464.626 ↑ 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=50,459.463..50,464.626 rows=6,691 loops=1)

4. 22.156 50,463.900 ↑ 45.0 6,691 1

Finalize HashAggregate (cost=5,056,587.84..5,065,610.88 rows=300,768 width=68) (actual time=50,459.462..50,463.900 rows=6,691 loops=1)

  • Group Key: tblmovements.trip_id
5. 102.846 50,441.744 ↑ 39.8 52,893 1

Gather (cost=4,800,935.04..5,014,480.32 rows=2,105,376 width=240) (actual time=50,360.784..50,441.744 rows=52,893 loops=1)

  • Workers Planned: 7
  • Workers Launched: 7
6. 93.638 50,338.898 ↑ 45.5 6,612 8

Partial HashAggregate (cost=4,799,935.04..4,802,942.72 rows=300,768 width=240) (actual time=50,332.524..50,338.898 rows=6,612 loops=8)

  • Group Key: tblmovements.trip_id
7. 46.923 50,245.260 ↑ 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=984.801..50,245.260 rows=70,745 loops=8)

  • Hash Cond: (tblsegments.trip_id = v_transit_movement_extras.trip_id)
8. 47.338 50,164.905 ↑ 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=950.369..50,164.905 rows=70,745 loops=8)

  • Hash Cond: (tblsegments.trip_id = tblmovements.trip_id)
9. 3,633.653 50,054.423 ↑ 527.2 70,745 8

Parallel Hash Join (cost=590,954.72..3,282,699.11 rows=37,297,559 width=8) (actual time=886.533..50,054.423 rows=70,745 loops=8)

  • Hash Cond: (tblsegments.link_id = v_transit_roads.link_id)
10. 46,193.615 46,193.615 ↑ 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.701..46,193.615 rows=32,635,352 loops=8)

11. 1.404 227.155 ↑ 208.0 32 8

Parallel Hash (cost=590,871.52..590,871.52 rows=6,656 width=16) (actual time=227.155..227.155 rows=32 loops=8)

  • Buckets: 32768 Batches: 1 Memory Usage: 512kB
12. 55.832 225.751 ↑ 208.0 32 8

Parallel Hash Semi Join (cost=541,195.19..590,871.52 rows=6,656 width=16) (actual time=184.732..225.751 rows=32 loops=8)

  • Hash Cond: (v_transit_roads.link_id = tblsegments_1.link_id)
13. 37.692 37.692 ↑ 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=2.845..37.692 rows=96,613 loops=8)

  • Heap Fetches: 0
14. 132.097 132.227 ↑ 266,131.5 32 8

Parallel Hash (cost=401,475.16..401,475.16 rows=8,516,208 width=8) (actual time=132.227..132.227 rows=32 loops=8)

  • Buckets: 33554432 Batches: 1 Memory Usage: 262176kB
15. 0.000 0.130 ↑ 266,131.5 32 8

Nested Loop (cost=184.49..401,475.16 rows=8,516,208 width=8) (actual time=0.109..0.130 rows=32 loops=8)

16. 0.000 0.047 ↓ 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.046..0.047 rows=0 loops=8)

  • Recheck Cond: (link_id = 1201733555)
17. 0.073 0.073 ↑ 9,723.0 1 1

Bitmap Index Scan on seglink (cost=0.00..181.49 rows=9,723 width=0) (actual time=0.073..0.073 rows=1 loops=1)

  • Index Cond: (link_id = 1201733555)
18. 0.628 0.628 ↑ 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.491..0.628 rows=252 loops=1)

  • Index Cond: (trip_id = tblsegments_2.trip_id)
  • Heap Fetches: 0
19. 48.116 63.144 ↑ 3.3 37,596 8

Parallel Hash (cost=8,584.20..8,584.20 rows=125,320 width=27) (actual time=63.144..63.144 rows=37,596 loops=8)

  • Buckets: 524288 Batches: 1 Memory Usage: 23936kB
20. 15.028 15.028 ↑ 3.3 37,596 8

Parallel Seq Scan on tblmovements (cost=0.00..8,584.20 rows=125,320 width=27) (actual time=0.116..15.028 rows=37,596 loops=8)

21. 23.645 33.432 ↑ 4.7 37,331 8

Parallel Hash (cost=3,659.76..3,659.76 rows=175,676 width=24) (actual time=33.432..33.432 rows=37,331 loops=8)

  • Buckets: 524288 Batches: 1 Memory Usage: 20640kB
22. 9.787 9.787 ↑ 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.011..9.787 rows=37,331 loops=8)