explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xsi4

Settings
# exclusive inclusive rows x rows loops node
1. 2.844 80,751.422 ↑ 4.9 41 1

GroupAggregate (cost=35,929,081.98..35,935,856.76 rows=200 width=40) (actual time=80,748.193..80,751.422 rows=41 loops=1)

  • Group Key: trips.commodity
2. 3.056 80,748.578 ↑ 45.0 6,691 1

Sort (cost=35,929,081.98..35,929,833.90 rows=300,768 width=60) (actual time=80,748.181..80,748.578 rows=6,691 loops=1)

  • Sort Key: trips.commodity
  • Sort Method: quicksort Memory: 1133kB
3. 0.824 80,745.522 ↑ 45.0 6,691 1

Subquery Scan on trips (cost=31,714,484.35..35,890,406.16 rows=300,768 width=60) (actual time=80,575.276..80,745.522 rows=6,691 loops=1)

4. 0.000 80,744.698 ↑ 45.0 6,691 1

Finalize GroupAggregate (cost=31,714,484.35..35,887,398.48 rows=300,768 width=68) (actual time=80,575.275..80,744.698 rows=6,691 loops=1)

  • Group Key: tblmovements.trip_id
5. 0.000 82,019.806 ↑ 64.7 9,292 1

Gather Merge (cost=31,714,484.35..35,866,344.72 rows=601,536 width=240) (actual time=80,575.221..82,019.806 rows=9,292 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 362.337 242,142.309 ↑ 97.1 3,097 3

Partial GroupAggregate (cost=31,713,484.33..35,795,912.52 rows=300,768 width=240) (actual time=80,565.022..80,714.103 rows=3,097 loops=3)

  • Group Key: tblmovements.trip_id
7. 375.357 241,779.972 ↑ 576.6 188,654 3

Sort (cost=31,713,484.33..31,985,445.69 rows=108,784,547 width=43) (actual time=80,564.934..80,593.324 rows=188,654 loops=3)

  • Sort Key: tblmovements.trip_id
  • Sort Method: external merge Disk: 11072kB
  • Worker 0: Sort Method: external merge Disk: 9952kB
  • Worker 1: Sort Method: external merge Disk: 9848kB
8. 1,645.710 241,404.615 ↑ 576.6 188,654 3

Parallel Hash Left Join (cost=768,419.79..7,153,226.84 rows=108,784,547 width=43) (actual time=80,290.025..80,468.205 rows=188,654 loops=3)

  • Hash Cond: (tblsegments.trip_id = tblmovements.trip_id)
9. 2,179.848 239,239.932 ↑ 576.6 188,654 3

Parallel Hash Left Join (cost=757,412.09..5,580,978.67 rows=108,784,547 width=24) (actual time=79,566.783..79,746.644 rows=188,654 loops=3)

  • Hash Cond: (tblsegments.trip_id = v_transit_movement_extras.trip_id)
10. 30,376.380 236,683.092 ↑ 576.6 188,654 3

Parallel Hash Join (cost=750,526.37..4,437,619.47 rows=108,784,547 width=8) (actual time=14,848.616..78,894.364 rows=188,654 loops=3)

  • Hash Cond: (tblsegments.link_id = v_transit_roads.link_id)
11. 162,933.774 162,933.774 ↑ 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..54,311.258 rows=87,027,606 loops=3)

12. 1.353 43,372.938 ↑ 132.1 84 3

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

  • Buckets: 32768 Batches: 1 Memory Usage: 352kB
13. 38,238.804 43,371.585 ↑ 132.1 84 3

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

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

  • Heap Fetches: 0
15. 4,891.302 4,982.082 ↑ 130,953.6 84 3

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

  • Buckets: 131072 Batches: 512 Memory Usage: 1056kB
16. 0.100 90.780 ↑ 130,953.6 84 3

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

17. 60.155 90.258 ↓ 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=30.085..30.086 rows=0 loops=3)

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

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

  • Index Cond: (link_id = 1201733555)
19. 0.422 0.422 ↑ 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.179..0.422 rows=252 loops=1)

  • Index Cond: (trip_id = tblsegments_2.trip_id)
  • Heap Fetches: 0
20. 331.635 376.992 ↑ 1.8 99,550 3

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

  • Buckets: 65536 Batches: 8 Memory Usage: 2624kB
21. 45.357 45.357 ↑ 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.016..15.119 rows=99,550 loops=3)

22. 422.889 518.973 ↑ 1.2 100,256 3

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

  • Buckets: 65536 Batches: 8 Memory Usage: 3008kB
23. 96.084 96.084 ↑ 1.2 100,256 3

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