explain.depesz.com

PostgreSQL's explain analyze made readable

Result: r1GJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.032 5,871.352 ↑ 6.5 31 1

Sort (cost=6,318,490.39..6,318,490.89 rows=200 width=40) (actual time=5,871.351..5,871.352 rows=31 loops=1)

  • Sort Key: tblmovements.commodity
  • Sort Method: quicksort Memory: 27kB
2. 1.362 5,871.320 ↑ 6.5 31 1

HashAggregate (cost=6,318,475.74..6,318,482.74 rows=200 width=40) (actual time=5,871.305..5,871.320 rows=31 loops=1)

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

Group (cost=1,726,110.57..5,526,468.39 rows=26,400,245 width=55) (actual time=4,940.498..5,869.958 rows=2,835 loops=1)

  • Group Key: tblmovements.trip_id, ((v_transit_movement_extras.mov_cst * '2'::double precision)), v_transit_movement_extras.total_km, v_transit_movement_extras.total_hr, v_transit_movement_extras.avg_road_speed
4. 0.000 5,873.525 ↑ 9,011.9 2,835 1

Gather Merge (cost=1,726,110.57..4,877,107.53 rows=25,548,624 width=47) (actual time=4,940.491..5,873.525 rows=2,835 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
5. 1,926.068 20,302.736 ↑ 12,011.6 709 4

Group (cost=1,725,110.53..1,874,144.17 rows=8,516,208 width=47) (actual time=4,201.487..5,075.684 rows=709 loops=4)

  • Group Key: tblmovements.trip_id, ((v_transit_movement_extras.mov_cst * '2'::double precision)), v_transit_movement_extras.total_km, v_transit_movement_extras.total_hr, v_transit_movement_extras.avg_road_speed
6. 7,903.824 18,376.668 ↑ 2.5 3,456,820 4

Sort (cost=1,725,110.53..1,746,401.05 rows=8,516,208 width=47) (actual time=4,201.483..4,594.167 rows=3,456,820 loops=4)

  • Sort Key: tblmovements.trip_id, ((v_transit_movement_extras.mov_cst * '2'::double precision)), v_transit_movement_extras.total_km, v_transit_movement_extras.total_hr, v_transit_movement_extras.avg_road_speed
  • Sort Method: external merge Disk: 209976kB
  • Worker 0: Sort Method: external merge Disk: 211992kB
  • Worker 1: Sort Method: external merge Disk: 196792kB
  • Worker 2: Sort Method: external merge Disk: 176000kB
7. 3,185.456 10,472.844 ↑ 2.5 3,456,820 4

Parallel Hash Left Join (cost=16,190.91..482,844.21 rows=8,516,208 width=47) (actual time=90.119..2,618.211 rows=3,456,820 loops=4)

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

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

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

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

10. 7.147 9.040 ↑ 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=1.948..2.260 rows=709 loops=4)

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

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

  • Index Cond: (link_id = 1464746470)
12. 2,148.930 2,148.930 ↓ 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.017..0.758 rows=4,877 loops=2,835)

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

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

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

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

15. 94.296 150.616 ↑ 2.4 74,662 4

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

  • Buckets: 524288 Batches: 1 Memory Usage: 20544kB
16. 56.320 56.320 ↑ 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=1.310..14.080 rows=74,662 loops=4)