explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TRq

Settings
# 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)