explain.depesz.com

PostgreSQL's explain analyze made readable

Result: i6PC : Optimization for: Optimization for: plan #ixd7; plan #1yeS

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 7,602.340 110,120.283 ↑ 28.7 17,465 1

Group (cost=19,772,774.18..19,779,044.24 rows=501,605 width=68) (actual time=83,686.620..110,120.283 rows=17,465 loops=1)

  • Output: mh.kurum_hesap_kodu_id, pc.parent_hesap_kodu, pc.parent_hesap_adi, pc.en_alt_seviye_mi
  • Group Key: mh.kurum_hesap_kodu_id, pc.parent_hesap_kodu, pc.parent_hesap_adi, pc.en_alt_seviye_mi
  • Buffers: shared hit=5908 read=3222135
  • I/O Timings: read=18138.888
2. 62,742.104 102,517.943 ↓ 106.9 53,609,740 1

Sort (cost=19,772,774.18..19,774,028.19 rows=501,605 width=68) (actual time=83,686.616..102,517.943 rows=53,609,740 loops=1)

  • Output: mh.kurum_hesap_kodu_id, pc.parent_hesap_kodu, pc.parent_hesap_adi, pc.en_alt_seviye_mi
  • Sort Key: mh.kurum_hesap_kodu_id, pc.parent_hesap_kodu, pc.parent_hesap_adi, pc.en_alt_seviye_mi
  • Sort Method: quicksort Memory: 8687270kB
  • Buffers: shared hit=5908 read=3222135
  • I/O Timings: read=18138.888
3. 9,355.231 39,775.839 ↓ 106.9 53,609,740 1

Hash Join (cost=468,497.01..19,725,281.74 rows=501,605 width=68) (actual time=3,572.062..39,775.839 rows=53,609,740 loops=1)

  • Output: mh.kurum_hesap_kodu_id, pc.parent_hesap_kodu, pc.parent_hesap_adi, pc.en_alt_seviye_mi
  • Hash Cond: (mh.kurum_hesap_kodu_id = pc.child_id)
  • Buffers: shared hit=5889 read=3222135
  • I/O Timings: read=18138.888
4. 27,883.512 30,346.678 ↑ 1.0 15,858,551 1

Bitmap Heap Scan on maliye_analitikrapor.muhasebe_hareketleri mh (cost=305,084.86..18,920,382.35 rows=15,911,780 width=8) (actual time=3,497.512..30,346.678 rows=15,858,551 loops=1)

  • Output: mh.muhasebe_kaydi_detay_id, mh.muhasebe_fisi_id, mh.mali_yil, mh.fis_tarihi, mh.rapor_tarihi, mh.hesap_plani_yevmiye_no, mh.muhasebe_birimi_yevmiye_no, mh.kurum_hesap_plani_id, mh.kurum_hesap_plani_ad, mh.hesap_kodu, mh.hesap_adi, mh.borc_tutar, mh.alacak_tutar, mh.fis_turu, mh.islem_tipi, mh.aciklama, mh.islem_yapan_kisi, mh.islem_yapilan_kisi, mh.islem_alindi_no, mh.islem_aciklama, mh.harcama_birimi_ad, mh.harcama_birimi_vergi_kimlik_no, mh.muhasebe_birimi_ad, mh.muhasebe_birimi_vergi_kimlik_no, mh.kamu_idaresi_ad, mh.kamu_idaresi_iliskili_kayit_no, mh.tertip, mh.kkod_1, mh.kkod_2, mh.kkod_3, mh.kkod_4, mh.kurum_hesap_kodu_id, mh.muhasebe_birimi_iliskili_kayit_no, mh.harcama_birimi_iliskili_kayit_no, mh.kamu_idaresi_birimi_iliskili_kayit_no, mh.tertip_numarasi, mh.butce_turu, mh.harcama_birimi_turu
  • Recheck Cond: (mh.kurum_hesap_plani_id = 254)
  • Heap Blocks: exact=3167581
  • Buffers: shared hit=1159 read=3222135
  • I/O Timings: read=18138.888
5. 2,463.166 2,463.166 ↑ 1.0 15,858,842 1

Bitmap Index Scan on mh_kurumhesapplaniid_idx (cost=0.00..301,106.92 rows=15,911,780 width=0) (actual time=2,463.166..2,463.166 rows=15,858,842 loops=1)

  • Index Cond: (mh.kurum_hesap_plani_id = 254)
  • Buffers: shared hit=1 read=55712
  • I/O Timings: read=424.536
6. 19.505 73.930 ↑ 1.0 98,651 1

Hash (cost=162,169.42..162,169.42 rows=99,418 width=68) (actual time=73.930..73.930 rows=98,651 loops=1)

  • Output: pc.parent_hesap_kodu, pc.parent_hesap_adi, pc.en_alt_seviye_mi, pc.child_id
  • Buckets: 131072 Batches: 1 Memory Usage: 11070kB
  • Buffers: shared hit=4730
7. 45.787 54.425 ↑ 1.0 98,651 1

Bitmap Heap Scan on maliye_analitikrapor.parent_child pc (cost=3,167.05..162,169.42 rows=99,418 width=68) (actual time=9.103..54.425 rows=98,651 loops=1)

  • Output: pc.parent_hesap_kodu, pc.parent_hesap_adi, pc.en_alt_seviye_mi, pc.child_id
  • Recheck Cond: (pc.kurum_hesap_plani_id = 254)
  • Heap Blocks: exact=4122
  • Buffers: shared hit=4730
8. 8.638 8.638 ↑ 1.0 98,651 1

Bitmap Index Scan on parentchild_kurum_hesap_plani_id_idx (cost=0.00..3,142.20 rows=99,418 width=0) (actual time=8.638..8.638 rows=98,651 loops=1)

  • Index Cond: (pc.kurum_hesap_plani_id = 254)
  • Buffers: shared hit=608