explain.depesz.com

PostgreSQL's explain analyze made readable

Result: F7ml

Settings
# exclusive inclusive rows x rows loops node
1. 0.032 4,742.101 ↑ 6.5 31 1

Sort (cost=6,405,679.92..6,405,680.42 rows=200 width=40) (actual time=4,742.099..4,742.101 rows=31 loops=1)

  • Sort Key: tblmovements.commodity
  • Sort Method: quicksort Memory: 27kB
2. 1.273 4,742.069 ↑ 6.5 31 1

HashAggregate (cost=6,405,665.27..6,405,672.27 rows=200 width=40) (actual time=4,742.056..4,742.069 rows=31 loops=1)

  • Group Key: tblmovements.commodity
3. 0.000 4,740.796 ↑ 9,312.3 2,835 1

Finalize GroupAggregate (cost=1,442,845.05..5,613,657.92 rows=26,400,245 width=55) (actual time=3,721.199..4,740.796 rows=2,835 loops=1)

  • Group Key: tblmovements.trip_id, v_transit_movement_extras.total_km, v_transit_movement_extras.total_hr, v_transit_movement_extras.avg_road_speed
4. 0.000 4,744.255 ↑ 9,011.9 2,835 1

Gather Merge (cost=1,442,845.05..4,700,294.61 rows=25,548,624 width=71) (actual time=3,720.421..4,744.255 rows=2,835 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
5. 2,822.400 17,494.148 ↑ 12,011.6 709 4

Partial GroupAggregate (cost=1,441,845.01..1,697,331.25 rows=8,516,208 width=71) (actual time=3,493.834..4,373.537 rows=709 loops=4)

  • Group Key: tblmovements.trip_id, v_transit_movement_extras.total_km, v_transit_movement_extras.total_hr, v_transit_movement_extras.avg_road_speed
6. 4,444.304 14,671.748 ↑ 2.5 3,456,820 4

Sort (cost=1,441,845.01..1,463,135.53 rows=8,516,208 width=43) (actual time=3,493.249..3,667.937 rows=3,456,820 loops=4)

  • Sort Key: tblmovements.trip_id, v_transit_movement_extras.total_km, v_transit_movement_extras.total_hr, v_transit_movement_extras.avg_road_speed
  • Sort Method: quicksort Memory: 350123kB
  • Worker 0: Sort Method: quicksort Memory: 388974kB
  • Worker 1: Sort Method: quicksort Memory: 367190kB
  • Worker 2: Sort Method: quicksort Memory: 389438kB
7. 3,000.824 10,227.444 ↑ 2.5 3,456,820 4

Parallel Hash Left Join (cost=16,190.91..461,553.69 rows=8,516,208 width=43) (actual time=85.409..2,556.861 rows=3,456,820 loops=4)

  • Hash Cond: (tblsegments.trip_id = v_transit_movement_extras.trip_id)
8. 3,319.640 7,092.164 ↑ 2.5 3,456,820 4

Parallel Hash Join (cost=10,335.19..433,342.60 rows=8,516,208 width=35) (actual time=50.464..1,773.041 rows=3,456,820 loops=4)

  • Hash Cond: (tblsegments.trip_id = tblmovements.trip_id)
9. 1,432.052 3,579.348 ↑ 2.5 3,456,820 4

Nested Loop (cost=184.49..400,836.55 rows=8,516,208 width=16) (actual time=1.514..894.837 rows=3,456,820 loops=4)

10. 3.363 4.036 ↑ 4.4 709 4

Parallel Bitmap Heap Scan on tblsegments tblsegments_1 (cost=183.92..37,087.49 rows=3,136 width=8) (actual time=0.732..1.009 rows=709 loops=4)

  • Recheck Cond: (link_id = 1464746470)
  • Heap Blocks: exact=5
11. 0.673 0.673 ↑ 3.4 2,835 1

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

  • Index Cond: (link_id = 1464746470)
12. 2,143.260 2,143.260 ↓ 1.8 4,877 2,835

Index Only Scan using idx_tblsegments_keyrev on tblsegments (cost=0.57..89.21 rows=2,678 width=16) (actual time=0.018..0.756 rows=4,877 loops=2,835)

  • Index Cond: (trip_id = tblsegments_1.trip_id)
  • Heap Fetches: 0
13. 99.000 193.176 ↑ 1.7 75,192 4

Parallel Hash (cost=8,584.20..8,584.20 rows=125,320 width=27) (actual time=48.294..48.294 rows=75,192 loops=4)

  • Buckets: 524288 Batches: 1 Memory Usage: 23776kB
14. 94.176 94.176 ↑ 1.7 75,192 4

Parallel Seq Scan on tblmovements (cost=0.00..8,584.20 rows=125,320 width=27) (actual time=0.364..23.544 rows=75,192 loops=4)

15. 80.208 134.456 ↑ 2.4 74,662 4

Parallel Hash (cost=3,659.76..3,659.76 rows=175,676 width=24) (actual time=33.614..33.614 rows=74,662 loops=4)

  • Buckets: 524288 Batches: 1 Memory Usage: 20512kB
16. 54.248 54.248 ↑ 2.4 74,662 4

Parallel Seq Scan on v_transit_movement_extras (cost=0.00..3,659.76 rows=175,676 width=24) (actual time=0.532..13.562 rows=74,662 loops=4)