explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LhkS

Settings
# exclusive inclusive rows x rows loops node
1. 0.065 5,796.042 ↑ 4.2 48 1

Sort (cost=465,902.14..465,902.64 rows=200 width=32) (actual time=5,796.041..5,796.042 rows=48 loops=1)

  • Sort Key: tblmovements.commodity
  • Sort Method: quicksort Memory: 28kB
2. 6.165 5,795.977 ↑ 4.2 48 1

HashAggregate (cost=465,888.99..465,894.49 rows=200 width=32) (actual time=5,795.961..5,795.977 rows=48 loops=1)

  • Group Key: tblmovements.commodity
3. 51.487 5,789.812 ↑ 12.8 23,437 1

Finalize HashAggregate (cost=450,850.59..458,369.79 rows=300,768 width=52) (actual time=5,783.426..5,789.812 rows=23,437 loops=1)

  • Group Key: tblmovements.trip_id
4. 188.466 5,738.325 ↑ 7.5 68,899 1

Gather (cost=389,067.87..443,127.75 rows=514,856 width=176) (actual time=5,703.114..5,738.325 rows=68,899 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 2,707.445 5,549.859 ↑ 11.2 22,966 3

Partial HashAggregate (cost=388,067.87..390,642.15 rows=257,428 width=176) (actual time=5,536.740..5,549.859 rows=22,966 loops=3)

  • Group Key: tblmovements.trip_id
6. 861.204 2,842.414 ↓ 10.0 2,576,841 3

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

  • Hash Cond: (tblsegments.trip_id = v_transit_movementcosts.trip_id)
7. 979.795 1,882.051 ↓ 10.0 2,576,841 3

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

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

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

9. 62.133 62.133 ↑ 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=8.996..62.133 rows=658 loops=3)

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

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

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

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

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

13. 36.517 99.159 ↑ 1.0 298,650 3

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

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

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