explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 191.408 4,470.642 ↑ 271.0 1 1

GroupAggregate (cost=170,911.40..170,921.56 rows=271 width=621) (actual time=4,470.642..4,470.642 rows=1 loops=1)

  • Output: (sum(mh.borc_tutar) / '100'::numeric), (sum(mh.alacak_tutar) / '100'::numeric), 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=143688 read=319129
  • I/O Timings: read=2405.232
2.          

CTE parent_child2

3. 0.006 101.266 ↑ 1.0 1 1

Limit (cost=162,666.51..162,666.51 rows=1 width=80) (actual time=101.265..101.266 rows=1 loops=1)

  • Output: parent_child.id, parent_child.parent_hesap_adi, parent_child.en_alt_seviye_mi, parent_child.parent_hesap_kodu, parent_child.kurum_hesap_plani_id, parent_child.child_id
  • Buffers: shared hit=4 read=4726
  • I/O Timings: read=31.714
4. 18.616 101.260 ↑ 99,418.0 1 1

Sort (cost=162,666.51..162,915.05 rows=99,418 width=80) (actual time=101.260..101.260 rows=1 loops=1)

  • Output: parent_child.id, parent_child.parent_hesap_adi, parent_child.en_alt_seviye_mi, parent_child.parent_hesap_kodu, parent_child.kurum_hesap_plani_id, parent_child.child_id
  • Sort Key: parent_child.parent_hesap_kodu, parent_child.parent_hesap_adi, parent_child.en_alt_seviye_mi
  • Sort Method: top-N heapsort Memory: 25kB
  • Buffers: shared hit=4 read=4726
  • I/O Timings: read=31.714
5. 63.444 82.644 ↑ 1.0 98,651 1

Bitmap Heap Scan on maliye_analitikrapor.parent_child (cost=3,167.05..162,169.42 rows=99,418 width=80) (actual time=19.704..82.644 rows=98,651 loops=1)

  • Output: parent_child.id, parent_child.parent_hesap_adi, parent_child.en_alt_seviye_mi, parent_child.parent_hesap_kodu, parent_child.kurum_hesap_plani_id, parent_child.child_id
  • Recheck Cond: (parent_child.kurum_hesap_plani_id = 254)
  • Heap Blocks: exact=4122
  • Buffers: shared hit=4 read=4726
  • I/O Timings: read=31.714
6. 19.200 19.200 ↑ 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=19.200..19.200 rows=98,651 loops=1)

  • Index Cond: (parent_child.kurum_hesap_plani_id = 254)
  • Buffers: shared hit=3 read=605
  • I/O Timings: read=5.677
7. 395.332 4,279.234 ↓ 3,328.0 901,882 1

Sort (cost=8,244.89..8,245.57 rows=271 width=573) (actual time=4,231.358..4,279.234 rows=901,882 loops=1)

  • Output: mh.kurum_hesap_kodu_id, pc.parent_hesap_kodu, pc.parent_hesap_adi, pc.en_alt_seviye_mi, mh.borc_tutar, mh.alacak_tutar
  • Sort Key: mh.kurum_hesap_kodu_id, pc.parent_hesap_kodu, pc.parent_hesap_adi, pc.en_alt_seviye_mi
  • Sort Method: quicksort Memory: 151404kB
  • Buffers: shared hit=143688 read=319129
  • I/O Timings: read=2405.232
8. 310.210 3,883.902 ↓ 3,328.0 901,882 1

Nested Loop (cost=0.57..8,233.94 rows=271 width=573) (actual time=101.348..3,883.902 rows=901,882 loops=1)

  • Output: mh.kurum_hesap_kodu_id, pc.parent_hesap_kodu, pc.parent_hesap_adi, pc.en_alt_seviye_mi, mh.borc_tutar, mh.alacak_tutar
  • Buffers: shared hit=143669 read=319129
  • I/O Timings: read=2405.232
9. 101.272 101.272 ↑ 1.0 1 1

CTE Scan on parent_child2 pc (cost=0.00..0.02 rows=1 width=557) (actual time=101.267..101.272 rows=1 loops=1)

  • Output: pc.id, pc.parent_hesap_adi, pc.en_alt_seviye_mi, pc.parent_hesap_kodu, pc.kurum_hesap_plani_id, pc.child_id
  • Buffers: shared hit=4 read=4726
  • I/O Timings: read=31.714
10. 3,472.420 3,472.420 ↓ 3,328.0 901,882 1

Index Scan using muhasebe_hareketleri_kurum_hesap_kodu_id_idx on maliye_analitikrapor.muhasebe_hareketleri mh (cost=0.57..8,231.21 rows=271 width=24) (actual time=0.075..3,472.420 rows=901,882 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
  • Index Cond: (mh.kurum_hesap_kodu_id = pc.child_id)
  • Filter: (mh.kurum_hesap_plani_id = 254)
  • Buffers: shared hit=143665 read=314403
  • I/O Timings: read=2373.518