explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JI75

Settings
# exclusive inclusive rows x rows loops node
1. 2.002 49,651.769 ↑ 4.9 41 1

GroupAggregate (cost=8,985,199.86..8,991,974.64 rows=200 width=40) (actual time=49,649.432..49,651.769 rows=41 loops=1)

  • Group Key: trips.commodity
2. 2.579 49,649.767 ↑ 45.0 6,691 1

Sort (cost=8,985,199.86..8,985,951.78 rows=300,768 width=60) (actual time=49,649.422..49,649.767 rows=6,691 loops=1)

  • Sort Key: trips.commodity
  • Sort Method: quicksort Memory: 1133kB
3. 0.839 49,647.188 ↑ 45.0 6,691 1

Subquery Scan on trips (cost=8,863,587.00..8,957,832.54 rows=300,768 width=60) (actual time=49,626.734..49,647.188 rows=6,691 loops=1)

4. 0.000 49,646.349 ↑ 45.0 6,691 1

Finalize GroupAggregate (cost=8,863,587.00..8,954,824.86 rows=300,768 width=68) (actual time=49,626.732..49,646.349 rows=6,691 loops=1)

  • Group Key: tblmovements.trip_id
5. 0.000 49,715.172 ↑ 30.0 20,073 1

Gather Merge (cost=8,863,587.00..8,933,771.10 rows=601,536 width=240) (actual time=49,626.715..49,715.172 rows=20,073 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 13.407 148,845.468 ↑ 45.0 6,691 3

Sort (cost=8,862,586.98..8,863,338.90 rows=300,768 width=240) (actual time=49,614.352..49,615.156 rows=6,691 loops=3)

  • Sort Key: tblmovements.trip_id
  • Sort Method: quicksort Memory: 3643kB
  • Worker 0: Sort Method: quicksort Memory: 3643kB
  • Worker 1: Sort Method: quicksort Memory: 3643kB
7. 575.160 148,832.061 ↑ 45.0 6,691 3

Partial HashAggregate (cost=8,832,211.98..8,835,219.66 rows=300,768 width=240) (actual time=49,604.546..49,610.687 rows=6,691 loops=3)

  • Group Key: tblmovements.trip_id
8. 240.564 148,256.901 ↑ 576.6 188,654 3

Parallel Hash Left Join (cost=766,532.79..5,024,752.84 rows=108,784,547 width=43) (actual time=1,263.544..49,418.967 rows=188,654 loops=3)

  • Hash Cond: (tblsegments.trip_id = v_transit_movement_extras.trip_id)
9. 256.083 147,899.742 ↑ 576.6 188,654 3

Parallel Hash Left Join (cost=760,677.07..4,733,333.64 rows=108,784,547 width=35) (actual time=1,223.374..49,299.914 rows=188,654 loops=3)

  • Hash Cond: (tblsegments.trip_id = tblmovements.trip_id)
10. 28,627.401 147,462.135 ↑ 576.6 188,654 3

Parallel Hash Join (cost=750,526.37..4,437,619.47 rows=108,784,547 width=8) (actual time=1,162.056..49,154.045 rows=188,654 loops=3)

  • Hash Cond: (tblsegments.link_id = v_transit_roads.link_id)
11. 117,311.214 117,311.214 ↑ 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.012..39,103.738 rows=87,027,606 loops=3)

12. 21.084 1,523.520 ↑ 132.1 84 3

Parallel Hash (cost=750,387.70..750,387.70 rows=11,094 width=16) (actual time=507.839..507.840 rows=84 loops=3)

  • Buckets: 32768 Batches: 1 Memory Usage: 352kB
13. 1,011.234 1,502.436 ↑ 132.1 84 3

Parallel Hash Semi Join (cost=688,276.77..750,387.70 rows=11,094 width=16) (actual time=364.808..500.812 rows=84 loops=3)

  • Hash Cond: (v_transit_roads.link_id = tblsegments_1.link_id)
14. 95.100 95.100 ↑ 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=0.249..31.700 rows=257,634 loops=3)

  • Heap Fetches: 0
15. 395.151 396.102 ↑ 130,953.6 84 3

Parallel Hash (cost=507,805.07..507,805.07 rows=11,000,102 width=8) (actual time=132.033..132.034 rows=84 loops=3)

  • Buckets: 8388608 Batches: 8 Memory Usage: 65568kB
16. 0.037 0.951 ↑ 130,953.6 84 3

Nested Loop (cost=184.49..507,805.07 rows=11,000,102 width=8) (actual time=0.260..0.317 rows=84 loops=3)

17. 0.108 0.180 ↓ 0.0 0 3

Parallel Bitmap Heap Scan on tblsegments tblsegments_2 (cost=183.92..37,098.93 rows=4,051 width=8) (actual time=0.060..0.060 rows=0 loops=3)

  • Recheck Cond: (link_id = 1201733555)
18. 0.072 0.072 ↑ 9,723.0 1 1

Bitmap Index Scan on seglink (cost=0.00..181.49 rows=9,723 width=0) (actual time=0.072..0.072 rows=1 loops=1)

  • Index Cond: (link_id = 1201733555)
19. 0.734 0.734 ↑ 10.6 252 1

Index Only Scan using idx_tblsegments_keyrev on tblsegments tblsegments_1 (cost=0.57..89.42 rows=2,678 width=16) (actual time=0.591..0.734 rows=252 loops=1)

  • Index Cond: (trip_id = tblsegments_2.trip_id)
  • Heap Fetches: 0
20. 89.574 181.524 ↑ 1.2 100,256 3

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

  • Buckets: 524288 Batches: 1 Memory Usage: 23776kB
21. 91.950 91.950 ↑ 1.2 100,256 3

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

22. 74.241 116.595 ↑ 1.8 99,550 3

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

  • Buckets: 524288 Batches: 1 Memory Usage: 20512kB
23. 42.354 42.354 ↑ 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.012..14.118 rows=99,550 loops=3)