explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 66aG

Settings
# exclusive inclusive rows x rows loops node
1. 0.049 50,328.613 ↑ 4.2 48 1

Sort (cost=462,485.88..462,486.38 rows=200 width=32) (actual time=50,328.611..50,328.613 rows=48 loops=1)

  • Sort Key: tblmovements.commodity
  • Sort Method: quicksort Memory: 28kB
2. 22.667 50,328.564 ↑ 4.2 48 1

HashAggregate (cost=462,472.73..462,478.23 rows=200 width=32) (actual time=50,328.549..50,328.564 rows=48 loops=1)

  • Group Key: tblmovements.commodity
3. 119.159 50,305.897 ↑ 12.8 23,437 1

Finalize HashAggregate (cost=447,434.33..454,953.53 rows=300,768 width=52) (actual time=50,265.624..50,305.897 rows=23,437 loops=1)

  • Group Key: tblmovements.trip_id
4. 21.922 50,186.738 ↑ 22.6 23,437 1

Gather (cost=383,895.05..439,491.92 rows=529,494 width=176) (actual time=50,124.170..50,186.738 rows=23,437 loops=1)

  • Workers Planned: 2
  • Workers Launched: 0
5. 19,504.087 50,164.816 ↑ 11.3 23,437 1

Partial HashAggregate (cost=382,895.05..385,542.52 rows=264,747 width=176) (actual time=50,123.755..50,164.816 rows=23,437 loops=1)

  • Group Key: tblmovements.trip_id
6. 9,110.064 30,660.729 ↓ 29.2 7,730,523 1

Parallel Hash Left Join (cost=16,007.41..374,952.64 rows=264,747 width=35) (actual time=1,150.690..30,660.729 rows=7,730,523 loops=1)

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

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

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

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

9. 714.605 714.605 ↓ 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=60.328..714.605 rows=1,975 loops=1)

  • Filter: ((street_name)::text = ANY ('{"Warrego Hwy"}'::text[]))
  • Rows Removed by Filter: 770928
  • Heap Fetches: 0
10. 3.610 3.610 ↑ 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.105..3.610 rows=3,914 loops=1,975)

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

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

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

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

13. 213.118 387.318 ↓ 1.7 298,650 1

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

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

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