explain.depesz.com

PostgreSQL's explain analyze made readable

Result: W9po : Optimization for: plan #Vg6Jk

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 18,461.666 35,070.848 ↑ 7,240,060.0 93,288 1

Hash Right Join (cost=1,108,534.33..9,686,069,183.07 rows=675,410,714,157 width=696) (actual time=4,118.630..35,070.848 rows=93,288 loops=1)

  • Hash Cond: ((dw.fias_house_code)::uuid = d.fias_house_code)
  • Buffers: shared hit=244395 read=1577366, temp read=158253 written=158253
  • I/O Timings: read=4392.830
2. 12,522.006 12,522.006 ↑ 1.0 16,530,760 1

Seq Scan on hm_houses_dwelling dw (cost=0.00..1,755,370.34 rows=16,534,770 width=38) (actual time=0.019..12,522.006 rows=16,530,760 loops=1)

  • Filter: entity_is_actual
  • Rows Removed by Filter: 282448
  • Buffers: shared hit=9853 read=1577366
  • I/O Timings: read=4392.830
3. 582.296 4,087.176 ↑ 87.6 93,288 1

Hash (cost=288,384.56..288,384.56 rows=8,169,581 width=696) (actual time=4,087.176..4,087.176 rows=93,288 loops=1)

  • Buckets: 65536 Batches: 256 Memory Usage: 684kB
  • Buffers: shared hit=234542, temp read=36139 written=41285
4. 2,178.192 3,504.880 ↑ 87.6 93,288 1

Hash Right Join (cost=4,248.60..288,384.56 rows=8,169,581 width=696) (actual time=339.736..3,504.880 rows=93,288 loops=1)

  • Hash Cond: ((ap.fias_house_code)::uuid = d.fias_house_code)
  • Buffers: shared hit=234542, temp read=36139 written=36139
5. 1,007.672 1,007.672 ↓ 1.0 1,513,889 1

Seq Scan on hm_houses_apartment ap (cost=0.00..164,687.93 rows=1,510,089 width=38) (actual time=0.027..1,007.672 rows=1,513,889 loops=1)

  • Filter: entity_is_actual
  • Rows Removed by Filter: 68204
  • Buffers: shared hit=148916
6. 98.296 319.016 ↓ 86.2 93,288 1

Hash (cost=4,235.07..4,235.07 rows=1,082 width=695) (actual time=319.015..319.016 rows=93,288 loops=1)

  • Buckets: 131072 (originally 2048) Batches: 2 (originally 1) Memory Usage: 31745kB
  • Buffers: shared hit=85626, temp written=2638
7. 16.329 220.720 ↓ 86.2 93,288 1

Append (cost=39.65..4,235.07 rows=1,082 width=695) (actual time=46.879..220.720 rows=93,288 loops=1)

  • Buffers: shared hit=85626
8. 178.672 204.391 ↓ 86.2 93,288 1

Bitmap Heap Scan on org_pf_336_offence_pd_tech_org_house_detail_2019_09 d (cost=39.65..4,229.66 rows=1,082 width=695) (actual time=46.878..204.391 rows=93,288 loops=1)

  • Recheck Cond: ((ppa_organization_guid = 'd0f5d77e-0fe6-459a-afab-265adb3a65b7'::uuid) AND (fias_region_code = '15784a67-8cea-425b-834a-6afe0e3ed61c'::uuid))
  • Filter: (report_month = '2019-09-01'::date)
  • Heap Blocks: exact=85060
  • Buffers: shared hit=85626
9. 25.719 25.719 ↓ 86.2 93,288 1

Bitmap Index Scan on idx_org_pf_336_self_pd_tech_org_house_detail_2019_09 (cost=0.00..39.38 rows=1,082 width=0) (actual time=25.719..25.719 rows=93,288 loops=1)

  • Index Cond: ((ppa_organization_guid = 'd0f5d77e-0fe6-459a-afab-265adb3a65b7'::uuid) AND (fias_region_code = '15784a67-8cea-425b-834a-6afe0e3ed61c'::uuid))
  • Buffers: shared hit=566
Planning time : 0.578 ms
Execution time : 35,079.499 ms