explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8SUJ : Optimization for: plan #VZqr

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 66.703 10,776.578 ↑ 1.0 1 1

Aggregate (cost=10,000,387,426.50..10,000,387,426.51 rows=1 width=32) (actual time=10,776.578..10,776.578 rows=1 loops=1)

2. 5.648 10,709.875 ↓ 19.9 13,355 1

Hash Join (cost=10,000,386,562.47..10,000,387,253.97 rows=670 width=93) (actual time=10,700.820..10,709.875 rows=13,355 loops=1)

  • Hash Cond: ((stop.durak_no)::text = s.boarding)
3. 3.433 3.433 ↑ 1.0 14,438 1

Index Scan using stop_idx on stop (cost=0.29..653.85 rows=14,438 width=53) (actual time=0.015..3.433 rows=14,438 loops=1)

4. 0.925 10,700.794 ↓ 17.8 3,561 1

Hash (cost=10,000,386,559.68..10,000,386,559.68 rows=200 width=45) (actual time=10,700.794..10,700.794 rows=3,561 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 210kB
5. 84.513 10,699.869 ↓ 17.8 3,561 1

GroupAggregate (cost=10,000,384,190.86..10,000,386,557.68 rows=200 width=45) (actual time=10,564.865..10,699.869 rows=3,561 loops=1)

  • Group Key: s.boarding
6. 831.801 10,615.356 ↓ 2.2 517,093 1

Sort (cost=10,000,384,190.86..10,000,384,781.94 rows=236,432 width=13) (actual time=10,561.070..10,615.356 rows=517,093 loops=1)

  • Sort Key: s.boarding
  • Sort Method: quicksort Memory: 36527kB
7. 50.883 9,783.555 ↓ 2.2 517,093 1

Subquery Scan on s (cost=10,000,328,805.36..10,000,363,088.05 rows=236,432 width=13) (actual time=8,885.976..9,783.555 rows=517,093 loops=1)

8. 528.855 9,732.672 ↓ 2.2 517,093 1

GroupAggregate (cost=10,000,328,805.36..10,000,360,723.73 rows=236,432 width=48) (actual time=8,885.975..9,732.672 rows=517,093 loops=1)

  • Group Key: trips_with_zones_mt."TRIP_ID", trips_with_zones_mt."BOARDING
9. 8,691.838 9,203.817 ↑ 1.0 2,364,324 1

Sort (cost=10,000,328,805.36..10,000,334,716.17 rows=2,364,324 width=46) (actual time=8,885.947..9,203.817 rows=2,364,324 loops=1)

  • Sort Key: trips_with_zones_mt."TRIP_ID", trips_with_zones_mt."BOARDING
  • Sort Method: external merge Disk: 132584kB
10. 511.979 511.979 ↑ 1.0 2,364,324 1

Seq Scan on trips_with_zones_mt (cost=10,000,000,000.00..10,000,078,506.24 rows=2,364,324 width=46) (actual time=0.008..511.979 rows=2,364,324 loops=1)