explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1yeS : Optimization for: plan #ixd7

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1,675.009 47,972.172 ↑ 2,076.2 652 1

GroupAggregate (cost=21,223,891.38..21,274,653.78 rows=1,353,664 width=621) (actual time=45,946.549..47,972.172 rows=652 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=4849 read=3223172
  • I/O Timings: read=17676.651
2.          

CTE parent_child2

3. 0.252 92.791 ↑ 1.0 5,000 1

Limit (cost=168,774.61..168,787.11 rows=5,000 width=80) (actual time=92.113..92.791 rows=5,000 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=4727
4. 38.027 92.539 ↑ 19.9 5,000 1

Sort (cost=168,774.61..169,023.15 rows=99,418 width=80) (actual time=92.112..92.539 rows=5,000 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: 1111kB
  • Buffers: shared hit=4727
5. 41.603 54.512 ↑ 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=13.861..54.512 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=4727
6. 12.909 12.909 ↑ 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=12.909..12.909 rows=98,651 loops=1)

  • Index Cond: (parent_child.kurum_hesap_plani_id = 254)
  • Buffers: shared hit=605
7. 4,705.313 46,297.163 ↓ 6.1 8,208,448 1

Sort (cost=21,055,104.27..21,058,488.43 rows=1,353,664 width=573) (actual time=45,658.737..46,297.163 rows=8,208,448 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: 1351100kB
  • Buffers: shared hit=4849 read=3223172
  • I/O Timings: read=17676.651
8. 1,484.937 41,591.850 ↓ 6.1 8,208,448 1

Merge Join (cost=20,824,123.63..20,917,244.16 rows=1,353,664 width=573) (actual time=37,028.796..41,591.850 rows=8,208,448 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
  • Merge Cond: (pc.child_id = mh.kurum_hesap_kodu_id)
  • Buffers: shared hit=4849 read=3223172
  • I/O Timings: read=17676.651
9. 3.218 97.023 ↑ 1.0 5,000 1

Sort (cost=407.19..419.69 rows=5,000 width=557) (actual time=95.199..97.023 rows=5,000 loops=1)

  • Output: pc.parent_hesap_kodu, pc.parent_hesap_adi, pc.en_alt_seviye_mi, pc.child_id
  • Sort Key: pc.child_id
  • Sort Method: quicksort Memory: 842kB
  • Buffers: shared hit=4727
10. 93.805 93.805 ↑ 1.0 5,000 1

CTE Scan on parent_child2 pc (cost=0.00..100.00 rows=5,000 width=557) (actual time=92.116..93.805 rows=5,000 loops=1)

  • Output: pc.parent_hesap_kodu, pc.parent_hesap_adi, pc.en_alt_seviye_mi, pc.child_id
  • Buffers: shared hit=4727
11. 6,080.030 40,009.890 ↓ 1.3 20,312,594 1

Sort (cost=20,823,716.43..20,863,495.88 rows=15,911,779 width=24) (actual time=36,933.571..40,009.890 rows=20,312,594 loops=1)

  • Output: mh.borc_tutar, mh.alacak_tutar, mh.kurum_hesap_kodu_id
  • Sort Key: mh.kurum_hesap_kodu_id
  • Sort Method: quicksort Memory: 1632166kB
  • Buffers: shared hit=122 read=3223172
  • I/O Timings: read=17676.651
12. 31,427.843 33,929.860 ↑ 1.0 15,858,551 1

Bitmap Heap Scan on maliye_analitikrapor.muhasebe_hareketleri mh (cost=305,084.86..18,920,381.91 rows=15,911,779 width=24) (actual time=3,942.438..33,929.860 rows=15,858,551 loops=1)

  • Output: mh.borc_tutar, mh.alacak_tutar, mh.kurum_hesap_kodu_id
  • Recheck Cond: (mh.kurum_hesap_plani_id = 254)
  • Heap Blocks: exact=3167581
  • Buffers: shared hit=122 read=3223172
  • I/O Timings: read=17676.651
13. 2,502.017 2,502.017 ↑ 1.0 15,858,842 1

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

  • Index Cond: (mh.kurum_hesap_plani_id = 254)
  • Buffers: shared hit=1 read=55712
  • I/O Timings: read=374.441