explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nV9W

Settings
# exclusive inclusive rows x rows loops node
1. 1.916 129,964.328 ↑ 4.9 41 1

GroupAggregate (cost=21,379,757.16..21,386,531.94 rows=200 width=40) (actual time=129,962.079..129,964.328 rows=41 loops=1)

  • Group Key: trips.commodity
2. 3.415 129,962.412 ↑ 45.0 6,691 1

Sort (cost=21,379,757.16..21,380,509.08 rows=300,768 width=60) (actual time=129,962.066..129,962.412 rows=6,691 loops=1)

  • Sort Key: trips.commodity
  • Sort Method: quicksort Memory: 1133kB
3. 0.849 129,958.997 ↑ 45.0 6,691 1

Subquery Scan on trips (cost=19,204,869.81..21,341,081.34 rows=300,768 width=60) (actual time=129,784.971..129,958.997 rows=6,691 loops=1)

4. 0.000 129,958.148 ↑ 45.0 6,691 1

Finalize GroupAggregate (cost=19,204,869.81..21,338,073.66 rows=300,768 width=68) (actual time=129,784.970..129,958.148 rows=6,691 loops=1)

  • Group Key: tblmovements.trip_id
5. 0.000 131,714.846 ↑ 64.8 9,289 1

Gather Merge (cost=19,204,869.81..21,317,019.90 rows=601,536 width=240) (actual time=129,784.910..131,714.846 rows=9,289 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 351.705 389,622.567 ↑ 97.1 3,096 3

Partial GroupAggregate (cost=19,203,869.78..21,246,587.70 rows=300,768 width=240) (actual time=129,728.983..129,874.189 rows=3,096 loops=3)

  • Group Key: tblmovements.trip_id
7. 364.332 389,270.862 ↑ 288.3 188,654 3

Sort (cost=19,203,869.78..19,339,850.46 rows=54,392,273 width=43) (actual time=129,728.902..129,756.954 rows=188,654 loops=3)

  • Sort Key: tblmovements.trip_id
  • Sort Method: external merge Disk: 9352kB
  • Worker 0: Sort Method: external merge Disk: 9264kB
  • Worker 1: Sort Method: external merge Disk: 12256kB
8. 2,169.543 388,906.530 ↑ 288.3 188,654 3

Parallel Hash Left Join (cost=1,233,190.93..7,195,697.22 rows=54,392,273 width=43) (actual time=129,371.223..129,635.510 rows=188,654 loops=3)

  • Hash Cond: (tblsegments.trip_id = tblmovements.trip_id)
9. 4,791.234 385,236.801 ↑ 288.3 188,654 3

Parallel Hash Left Join (cost=1,222,183.23..6,403,640.79 rows=54,392,273 width=24) (actual time=128,005.468..128,412.267 rows=188,654 loops=3)

  • Hash Cond: (tblsegments.trip_id = v_transit_movement_extras.trip_id)
10. 165,153.243 378,820.440 ↑ 288.3 188,654 3

Parallel Hash Left Join (cost=1,215,297.51..5,828,003.34 rows=54,392,273 width=8) (actual time=91,571.257..126,273.480 rows=188,654 loops=3)

  • Hash Cond: (tblsegments.link_id = v_transit_roads.link_id)
  • Filter: (hashed SubPlan 1)
  • Rows Removed by Filter: 86838952
11. 210,531.954 210,531.954 ↑ 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.014..70,177.318 rows=87,027,606 loops=3)

12. 2,370.561 3,113.538 ↑ 1.3 257,634 3

Parallel Hash (cost=15,573.37..15,573.37 rows=322,043 width=8) (actual time=1,037.846..1,037.846 rows=257,634 loops=3)

  • Buckets: 131072 Batches: 16 Memory Usage: 2976kB
13. 742.977 742.977 ↑ 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=7.490..247.659 rows=257,634 loops=3)

  • Heap Fetches: 0
14.          

SubPlan (forParallel Hash Left Join)

15. 0.744 21.705 ↑ 105.7 252 3

HashAggregate (cost=1,194,107.78..1,194,374.04 rows=26,626 width=8) (actual time=7.094..7.235 rows=252 loops=3)

  • Group Key: tblsegments_1.link_id
16. 0.084 20.961 ↑ 104,762.9 252 3

Nested Loop (cost=1.14..1,128,107.17 rows=26,400,245 width=8) (actual time=6.838..6.987 rows=252 loops=3)

17. 20.343 20.343 ↑ 9,723.0 1 3

Index Only Scan using idx_tblsegments_key on tblsegments tblsegments_2 (cost=0.57..322.72 rows=9,723 width=8) (actual time=6.780..6.781 rows=1 loops=3)

  • Index Cond: (link_id = 1201733555)
  • Heap Fetches: 0
18. 0.534 0.534 ↑ 10.6 252 3

Index Only Scan using idx_tblsegments_keyrev on tblsegments tblsegments_1 (cost=0.57..89.21 rows=2,678 width=16) (actual time=0.053..0.178 rows=252 loops=3)

  • Index Cond: (trip_id = tblsegments_2.trip_id)
  • Heap Fetches: 0
19. 1,571.703 1,625.127 ↑ 1.8 99,550 3

Parallel Hash (cost=3,659.76..3,659.76 rows=175,676 width=24) (actual time=541.709..541.709 rows=99,550 loops=3)

  • Buckets: 65536 Batches: 8 Memory Usage: 2624kB
20. 53.424 53.424 ↑ 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.017..17.808 rows=99,550 loops=3)

21. 775.515 1,500.186 ↑ 1.2 100,256 3

Parallel Hash (cost=8,584.20..8,584.20 rows=125,320 width=27) (actual time=500.062..500.062 rows=100,256 loops=3)

  • Buckets: 65536 Batches: 8 Memory Usage: 3040kB
22. 724.671 724.671 ↑ 1.2 100,256 3

Parallel Seq Scan on tblmovements (cost=0.00..8,584.20 rows=125,320 width=27) (actual time=6.152..241.557 rows=100,256 loops=3)