explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ixd7

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 11,185.476 96,597.233 ↑ 1,541.1 17,465 1

GroupAggregate (cost=29,650,428.24..30,659,767.25 rows=26,915,707 width=621) (actual time=81,518.169..96,597.233 rows=17,465 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=246 read=3227794
  • I/O Timings: read=18413.855
2.          

CTE parent_child2

3. 68.477 87.360 ↑ 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.424..87.360 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=7 read=4723
  • I/O Timings: read=35.408
4. 18.883 18.883 ↑ 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=18.883..18.883 rows=98,651 loops=1)

  • Index Cond: (parent_child.kurum_hesap_plani_id = 254)
  • Buffers: shared hit=3 read=605
  • I/O Timings: read=6.246
5. 35,550.038 85,411.757 ↓ 2.0 53,609,740 1

Sort (cost=29,488,258.82..29,555,548.09 rows=26,915,707 width=573) (actual time=81,242.118..85,411.757 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, 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: 9498758kB
  • Buffers: shared hit=246 read=3227794
  • I/O Timings: read=18413.855
6. 5,515.171 49,861.719 ↓ 2.0 53,609,740 1

Merge Join (cost=20,833,951.13..21,238,183.82 rows=26,915,707 width=573) (actual time=36,927.840..49,861.719 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, mh.borc_tutar, mh.alacak_tutar
  • Merge Cond: (pc.child_id = mh.kurum_hesap_kodu_id)
  • Buffers: shared hit=230 read=3227794
  • I/O Timings: read=18413.855
7. 48.668 182.471 ↑ 1.0 98,619 1

Sort (cost=10,240.66..10,489.21 rows=99,418 width=557) (actual time=168.792..182.471 rows=98,619 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: 16227kB
  • Buffers: shared hit=7 read=4723
  • I/O Timings: read=35.408
8. 133.803 133.803 ↑ 1.0 98,651 1

CTE Scan on parent_child2 pc (cost=0.00..1,988.36 rows=99,418 width=557) (actual time=19.434..133.803 rows=98,651 loops=1)

  • Output: pc.parent_hesap_kodu, pc.parent_hesap_adi, pc.en_alt_seviye_mi, pc.child_id
  • Buffers: shared hit=7 read=4723
  • I/O Timings: read=35.408
9. 10,177.716 44,164.077 ↓ 3.4 53,609,736 1

Sort (cost=20,823,710.47..20,863,489.91 rows=15,911,775 width=24) (actual time=36,759.015..44,164.077 rows=53,609,736 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=223 read=3223071
  • I/O Timings: read=18378.447
10. 31,414.509 33,986.361 ↑ 1.0 15,858,551 1

Bitmap Heap Scan on maliye_analitikrapor.muhasebe_hareketleri mh (cost=305,084.83..18,920,376.45 rows=15,911,775 width=24) (actual time=3,598.352..33,986.361 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=223 read=3223071
  • I/O Timings: read=18378.447
11. 2,571.852 2,571.852 ↑ 1.0 15,858,842 1

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

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