explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9avs

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 311,551.342 ↑ 1.0 13 1

Finalize GroupAggregate (cost=9,667,528.11..9,667,531.51 rows=13 width=35) (actual time=311,551.299..311,551.342 rows=13 loops=1)

  • Output: p.region_code, avg(s.value)
  • Group Key: p.region_code
  • Buffers: shared hit=2,988,459 read=1,759,535
  • I/O Timings: read=89,037.455
2. 527.063 312,063.314 ↓ 1.5 39 1

Gather Merge (cost=9,667,528.11..9,667,531.15 rows=26 width=35) (actual time=311,551.259..312,063.314 rows=39 loops=1)

  • Output: p.region_code, (PARTIAL avg(s.value))
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=8,993,497 read=5,250,761
  • I/O Timings: read=266,186.617
3. 0.107 311,536.251 ↑ 1.0 13 3 / 3

Sort (cost=9,666,528.09..9,666,528.12 rows=13 width=35) (actual time=311,536.250..311,536.251 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=8,993,497 read=5,250,761
  • I/O Timings: read=266,186.617
  • Worker 0: actual time=311,530.997..311530.998 rows=13 loops=1
  • Buffers: shared hit=3,003,679 read=1,744,347
  • I/O Timings: read=88,354.859
  • Worker 1: actual time=311,530.505..311530.506 rows=13 loops=1
  • Buffers: shared hit=3,001,359 read=1,746,879
  • I/O Timings: read=88,794.303
4. 8,994.076 311,536.144 ↑ 1.0 13 3 / 3

Partial HashAggregate (cost=9,666,527.69..9,666,527.85 rows=13 width=35) (actual time=311,536.136..311,536.144 rows=13 loops=3)

  • Output: p.region_code, PARTIAL avg(s.value)
  • Group Key: p.region_code
  • Buffers: shared hit=8,993,481 read=5,250,761
  • I/O Timings: read=266,186.617
  • Worker 0: actual time=311,530.880..311530.888 rows=13 loops=1
  • Buffers: shared hit=3,003,671 read=1,744,347
  • I/O Timings: read=88,354.859
  • Worker 1: actual time=311,530.390..311530.397 rows=13 loops=1
  • Buffers: shared hit=3,001,351 read=1,746,879
  • I/O Timings: read=88,794.303
5. 21,455.553 302,542.068 ↑ 1.3 28,411,014 3 / 3

Merge Join (cost=603.35..9,488,958.12 rows=35,513,913 width=14) (actual time=6.543..302,542.068 rows=28,411,014 loops=3)

  • Output: p.region_code, s.value
  • Inner Unique: true
  • Merge Cond: (s.id_ref_parcel_fk = p.id)
  • Buffers: shared hit=8,993,481 read=5,250,761
  • I/O Timings: read=266,186.617
  • Worker 0: actual time=10.006..302566.180 rows=28,401,600 loops=1
  • Buffers: shared hit=3,003,671 read=1,744,347
  • I/O Timings: read=88,354.859
  • Worker 1: actual time=9.590..302523.120 rows=28,422,889 loops=1
  • Buffers: shared hit=3,001,351 read=1,746,879
  • I/O Timings: read=88,794.303
6. 16,392.897 16,392.897 ↑ 1.3 28,411,014 3 / 3

Parallel Index Scan using parcel_surface_new_id_ref_parcel_fk on public.parcel_surface_new s (cost=0.57..2,529,373.66 rows=35,513,913 width=15) (actual time=0.358..16,392.897 rows=28,411,014 loops=3)

  • Output: s.id, s.id_ref_parcel_fk, s.value, s.id_ref_confidence_fk, s.id_ref_sourcefolder_fk, s.id_ref_method_fk, s.id_attribute_fk, s.dept_code, s.last_update
  • Buffers: shared hit=264,861 read=1,011,407
  • I/O Timings: read=22,826.940
  • Worker 0: actual time=0.600..16434.617 rows=28,401,600 loops=1
  • Buffers: shared hit=88,281 read=337,075
  • I/O Timings: read=7,645.672
  • Worker 1: actual time=0.459..16480.479 rows=28,422,889 loops=1
  • Buffers: shared hit=88,323 read=337,241
  • I/O Timings: read=7,637.427
7. 264,693.618 264,693.618 ↓ 1.0 85,232,880 3 / 3

Index Scan using parcel_new_pkey on public.parcel_new p (cost=0.57..6,302,859.49 rows=85,217,328 width=7) (actual time=0.362..264,693.618 rows=85,232,880 loops=3)

  • Output: p.id, p.id_source, p.the_geom, p.id_ref_sourcefolder_fk, p.id_ref_confidence_fk, p.id_ref_method_fk, p.id_entity_fk, p.iris_code, p.municipality_code, p.dept_code, p.region_code, p.last_update
  • Buffers: shared hit=8,728,620 read=4,239,354
  • I/O Timings: read=243,359.677
  • Worker 0: actual time=0.468..264727.741 rows=85,232,982 loops=1
  • Buffers: shared hit=2,915,390 read=1,407,272
  • I/O Timings: read=80,709.187
  • Worker 1: actual time=0.607..264542.372 rows=85,233,043 loops=1
  • Buffers: shared hit=2,913,028 read=1,409,638
  • I/O Timings: read=81,156.876
Planning time : 0.922 ms
Execution time : 312,063.492 ms