explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dVbk

Settings
# exclusive inclusive rows x rows loops node
1. 0.084 55,852.980 ↑ 4.2 48 1

Sort (cost=469,466.76..469,467.26 rows=200 width=40) (actual time=55,852.978..55,852.980 rows=48 loops=1)

  • Sort Key: tblmovements.commodity
  • Sort Method: quicksort Memory: 28kB
2. 23.985 55,852.896 ↑ 4.2 48 1

HashAggregate (cost=469,451.62..469,459.12 rows=200 width=40) (actual time=55,852.877..55,852.896 rows=48 loops=1)

  • Group Key: tblmovements.commodity
3. 116.379 55,828.911 ↑ 12.8 23,437 1

Finalize HashAggregate (cost=451,405.54..460,428.58 rows=300,768 width=68) (actual time=55,737.581..55,828.911 rows=23,437 loops=1)

  • Group Key: tblmovements.trip_id
4. 24.922 55,712.532 ↑ 22.6 23,437 1

Gather (cost=385,218.79..440,815.66 rows=529,494 width=240) (actual time=55,559.274..55,712.532 rows=23,437 loops=1)

  • Workers Planned: 2
  • Workers Launched: 0
5. 24,116.121 55,687.610 ↑ 11.3 23,437 1

Partial HashAggregate (cost=384,218.79..386,866.26 rows=264,747 width=240) (actual time=55,558.828..55,687.610 rows=23,437 loops=1)

  • Group Key: tblmovements.trip_id
6. 9,783.636 31,571.489 ↓ 29.2 7,730,523 1

Parallel Hash Left Join (cost=16,007.41..374,952.64 rows=264,747 width=43) (actual time=1,211.304..31,571.489 rows=7,730,523 loops=1)

  • Hash Cond: (tblsegments.trip_id = v_transit_movement_extras.trip_id)
7. 9,648.096 21,337.760 ↓ 29.2 7,730,523 1

Parallel Hash Left Join (cost=10,151.70..366,546.80 rows=264,747 width=35) (actual time=758.609..21,337.760 rows=7,730,523 loops=1)

  • Hash Cond: (tblsegments.trip_id = tblmovements.trip_id)
8. 10,230.857 10,999.670 ↓ 29.2 7,730,523 1

Nested Loop (cost=1.00..355,701.13 rows=264,747 width=8) (actual time=66.076..10,999.670 rows=7,730,523 loops=1)

9. 765.236 765.236 ↓ 2.5 1,975 1

Parallel Index Only Scan using idx_v_transit_roads_agg on v_transit_roads (cost=0.42..30,159.92 rows=784 width=8) (actual time=59.253..765.236 rows=1,975 loops=1)

  • Filter: ((street_name)::text = ANY ('{"Warrego Hwy"}'::text[]))
  • Rows Removed by Filter: 770928
  • Heap Fetches: 0
10. 3.577 3.577 ↑ 2.5 3,914 1,975

Index Only Scan using idx_tblsegments_key on tblsegments (cost=0.57..317.82 rows=9,741 width=16) (actual time=0.121..3.577 rows=3,914 loops=1,975)

  • Index Cond: (link_id = v_transit_roads.link_id)
  • Heap Fetches: 0
11. 318.542 689.994 ↓ 2.4 300,768 1

Parallel Hash (cost=8,584.20..8,584.20 rows=125,320 width=27) (actual time=689.993..689.994 rows=300,768 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 23744kB
12. 371.452 371.452 ↓ 2.4 300,768 1

Parallel Seq Scan on tblmovements (cost=0.00..8,584.20 rows=125,320 width=27) (actual time=0.019..371.452 rows=300,768 loops=1)

13. 279.939 450.093 ↓ 1.7 298,650 1

Parallel Hash (cost=3,659.76..3,659.76 rows=175,676 width=24) (actual time=450.093..450.093 rows=298,650 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 20480kB
14. 170.154 170.154 ↓ 1.7 298,650 1

Parallel Seq Scan on v_transit_movement_extras (cost=0.00..3,659.76 rows=175,676 width=24) (actual time=0.015..170.154 rows=298,650 loops=1)