explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cfXU

Settings
# exclusive inclusive rows x rows loops node
1. 0.126 6,551.027 ↑ 4.2 48 1

Sort (cost=465,902.14..465,902.64 rows=200 width=32) (actual time=6,551.026..6,551.027 rows=48 loops=1)

  • Sort Key: tblmovements.commodity
  • Sort Method: quicksort Memory: 28kB
2. 6.103 6,550.901 ↑ 4.2 48 1

HashAggregate (cost=465,888.99..465,894.49 rows=200 width=32) (actual time=6,550.882..6,550.901 rows=48 loops=1)

  • Group Key: tblmovements.commodity
3. 55.413 6,544.798 ↑ 12.8 23,437 1

Finalize HashAggregate (cost=450,850.59..458,369.79 rows=300,768 width=52) (actual time=6,535.825..6,544.798 rows=23,437 loops=1)

  • Group Key: tblmovements.trip_id
4. 104.103 6,489.385 ↑ 7.6 68,150 1

Gather (cost=389,067.87..443,127.75 rows=514,856 width=176) (actual time=6,453.453..6,489.385 rows=68,150 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 2,728.749 6,385.282 ↑ 11.3 22,717 3

Partial HashAggregate (cost=388,067.87..390,642.15 rows=257,428 width=176) (actual time=6,372.797..6,385.282 rows=22,717 loops=3)

  • Group Key: tblmovements.trip_id
6. 884.787 3,656.533 ↓ 10.0 2,576,841 3

Hash Left Join (cost=32,347.90..380,988.60 rows=257,428 width=35) (actual time=866.188..3,656.533 rows=2,576,841 loops=3)

  • Hash Cond: (tblsegments.trip_id = v_transit_movementcosts.trip_id)
7. 992.496 2,642.823 ↓ 10.0 2,576,841 3

Hash Left Join (cost=24,013.28..371,978.22 rows=257,428 width=39) (actual time=736.011..2,642.823 rows=2,576,841 loops=3)

  • Hash Cond: (tblsegments.trip_id = tblmovements.trip_id)
8. 770.253 924.254 ↓ 10.0 2,576,841 3

Nested Loop (cost=1.00..347,290.18 rows=257,428 width=12) (actual time=8.650..924.254 rows=2,576,841 loops=3)

9. 153.064 153.064 ↑ 1.2 658 3

Parallel Index Only Scan using idx_v_transit_roads_agg on v_transit_roads (cost=0.42..30,159.92 rows=762 width=12) (actual time=6.717..153.064 rows=658 loops=3)

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

Index Only Scan using idx_tblsegments_key on tblsegments (cost=0.57..318.48 rows=9,770 width=16) (actual time=0.186..0.937 rows=3,914 loops=1,975)

  • Index Cond: (link_id = v_transit_roads.link_id)
  • Heap Fetches: 0
11. 57.174 726.073 ↑ 1.0 300,768 3

Hash (cost=20,252.68..20,252.68 rows=300,768 width=27) (actual time=726.073..726.073 rows=300,768 loops=3)

  • Buckets: 524288 Batches: 1 Memory Usage: 22329kB
12. 668.899 668.899 ↑ 1.0 300,768 3

Seq Scan on tblmovements (cost=0.00..20,252.68 rows=300,768 width=27) (actual time=8.533..668.899 rows=300,768 loops=3)

13. 37.011 128.923 ↑ 1.0 298,650 3

Hash (cost=4,601.50..4,601.50 rows=298,650 width=12) (actual time=128.923..128.923 rows=298,650 loops=3)

  • Buckets: 524288 Batches: 1 Memory Usage: 18096kB
14. 91.912 91.912 ↑ 1.0 298,650 3

Seq Scan on v_transit_movementcosts (cost=0.00..4,601.50 rows=298,650 width=12) (actual time=0.055..91.912 rows=298,650 loops=3)