explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Yz9u

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 33,627.973 ↑ 1.0 13 1

Finalize GroupAggregate (cost=6,873,231.31..6,873,234.70 rows=13 width=35) (actual time=33,627.941..33,627.973 rows=13 loops=1)

  • Output: p.region_code, avg(s.value)
  • Group Key: p.region_code
  • Buffers: shared hit=607,443 read=1,050,143, temp read=225,387 written=230,916
  • I/O Timings: read=2,005.957
2. 23,900.825 57,523.086 ↓ 1.5 39 1

Gather Merge (cost=6,873,231.31..6,873,234.34 rows=26 width=35) (actual time=33,627.930..57,523.086 rows=39 loops=1)

  • Output: p.region_code, (PARTIAL avg(s.value))
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=1,672,047 read=3,150,202, temp read=675,754 written=680,492
  • I/O Timings: read=6,002.227
3. 0.047 33,622.261 ↑ 1.0 13 3 / 3

Sort (cost=6,872,231.29..6,872,231.32 rows=13 width=35) (actual time=33,622.260..33,622.261 rows=13 loops=3)

  • Output: p.region_code, (PARTIAL avg(s.value))
  • Sort Key: p.region_code
  • Sort Method: quicksort Memory: 26kB
  • Worker 0: Sort Method: quicksort Memory: 26kB
  • Worker 1: Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=1,672,047 read=3,150,202, temp read=675,754 written=680,492
  • I/O Timings: read=6,002.227
  • Worker 0: actual time=33,619.648..33619.649 rows=13 loops=1
  • Buffers: shared hit=531,855 read=1,047,194, temp read=222,976 written=224,544
  • I/O Timings: read=1,994.656
  • Worker 1: actual time=33,620.154..33620.155 rows=13 loops=1
  • Buffers: shared hit=532,749 read=1,052,865, temp read=227,391 written=225,032
  • I/O Timings: read=2,001.614
4. 5,002.562 33,622.214 ↑ 1.0 13 3 / 3

Partial HashAggregate (cost=6,872,230.88..6,872,231.05 rows=13 width=35) (actual time=33,622.209..33,622.214 rows=13 loops=3)

  • Output: p.region_code, PARTIAL avg(s.value)
  • Group Key: p.region_code
  • Buffers: shared hit=1,672,029 read=3,150,202, temp read=675,754 written=680,492
  • I/O Timings: read=6,002.227
  • Worker 0: actual time=33,619.587..33619.591 rows=13 loops=1
  • Buffers: shared hit=531,846 read=1,047,194, temp read=222,976 written=224,544
  • I/O Timings: read=1,994.656
  • Worker 1: actual time=33,620.082..33620.086 rows=13 loops=1
  • Buffers: shared hit=532,740 read=1,052,865, temp read=227,391 written=225,032
  • I/O Timings: read=2,001.614
5. 13,840.389 28,619.652 ↑ 1.3 28,411,014 3 / 3

Parallel Hash Join (cost=4,947,343.57..6,694,648.38 rows=35,516,500 width=14) (actual time=19,379.473..28,619.652 rows=28,411,014 loops=3)

  • Output: p.region_code, s.value
  • Inner Unique: true
  • Hash Cond: (s.id_ref_parcel_fk = p.id)
  • Buffers: shared hit=1,672,029 read=3,150,202, temp read=675,754 written=680,492
  • I/O Timings: read=6,002.227
  • Worker 0: actual time=19,365.367..28651.518 rows=28,197,403 loops=1
  • Buffers: shared hit=531,846 read=1,047,194, temp read=222,976 written=224,544
  • I/O Timings: read=1,994.656
  • Worker 1: actual time=19,382.867..28615.262 rows=28,676,493 loops=1
  • Buffers: shared hit=532,740 read=1,052,865, temp read=227,391 written=225,032
  • I/O Timings: read=2,001.614
6. 3,235.745 3,235.745 ↑ 1.3 28,411,014 3 / 3

Parallel Seq Scan on public.parcel_surface s (cost=0.00..1,168,417.00 rows=35,516,500 width=15) (actual time=0.029..3,235.745 rows=28,411,014 loops=3)

  • Output: s.value, s.id_ref_parcel_fk
  • Buffers: shared hit=5,793 read=807,459
  • I/O Timings: read=1,618.326
  • Worker 0: actual time=0.028..3240.280 rows=28,322,473 loops=1
  • Buffers: shared hit=1,875 read=268,364
  • I/O Timings: read=536.648
  • Worker 1: actual time=0.028..3241.195 rows=28,411,900 loops=1
  • Buffers: shared hit=1,921 read=269,171
  • I/O Timings: read=538.559
7. 4,638.740 11,543.518 ↑ 1.3 28,411,014 3 / 3

Parallel Hash (cost=4,364,321.03..4,364,321.03 rows=35,536,603 width=7) (actual time=11,543.518..11,543.518 rows=28,411,014 loops=3)

  • Output: p.region_code, p.id
  • Buckets: 524,288 Batches: 512 Memory Usage: 10,688kB
  • Buffers: shared hit=1,666,212 read=2,342,743, temp written=294,096
  • I/O Timings: read=4,383.901
  • Worker 0: actual time=11,542.936..11542.936 rows=27,845,470 loops=1
  • Buffers: shared hit=529,959 read=778,830, temp written=96,088
  • I/O Timings: read=1,458.008
  • Worker 1: actual time=11,543.549..11543.549 rows=27,952,503 loops=1
  • Buffers: shared hit=530,807 read=783,694, temp written=96,196
  • I/O Timings: read=1,463.055
8. 6,904.778 6,904.778 ↑ 1.3 28,411,014 3 / 3

Parallel Seq Scan on public.parcel p (cost=0.00..4,364,321.03 rows=35,536,603 width=7) (actual time=0.033..6,904.778 rows=28,411,014 loops=3)

  • Output: p.region_code, p.id
  • Buffers: shared hit=1,666,212 read=2,342,743
  • I/O Timings: read=4,383.901
  • Worker 0: actual time=0.045..6960.724 rows=27,845,470 loops=1
  • Buffers: shared hit=529,959 read=778,830
  • I/O Timings: read=1,458.008
  • Worker 1: actual time=0.042..6960.001 rows=27,952,503 loops=1
  • Buffers: shared hit=530,807 read=783,694
  • I/O Timings: read=1,463.055
Planning time : 0.462 ms
Execution time : 57,523.488 ms