explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XC2D

Settings
# exclusive inclusive rows x rows loops node
1. 0.074 2,913.923 ↑ 1.0 1 1

Aggregate (cost=138,067.88..138,067.89 rows=1 width=8) (actual time=2,913.923..2,913.923 rows=1 loops=1)

  • Output: count(loan0_.loanid)
  • Buffers: shared hit=2088802
2.          

CTE alb

3. 1,389.551 2,812.642 ↓ 2.0 54,738 1

GroupAggregate (cost=0.43..122,352.48 rows=26,772 width=300) (actual time=0.015..2,812.642 rows=54,738 loops=1)

  • Output: la.loanid, COALESCE(sum(la.principalcurrent), '0'::numeric), COALESCE(sum(la.principaloverdue), '0'::numeric), COALESCE(sum(la.interestcurrent), '0'::numeric), COALESCE(sum(la.interestoverdue), '0'::numeric), COALESCE(sum(la.penaltiesfixedtotal), '0'::numeric), COALESCE(sum(la.penaltiesvartotal), '0'::numeric), COALESCE(sum(la.additionalitemsum), '0'::numeric), max(la.billingdate), NULL::unknown, NULL::unknown
  • Group Key: la.loanid
  • Buffers: shared hit=2052591
4. 1,423.091 1,423.091 ↓ 1.0 2,128,770 1

Index Scan using loanallocation_loanid_idx on public.loanallocation la (cost=0.43..73,732.60 rows=2,128,162 width=34) (actual time=0.009..1,423.091 rows=2,128,770 loops=1)

  • Output: la.loanallocationid, la.created, la.updated, la.additionalitemsum, la.billingdate, la.interestcurrent, la.interestoverdue, la.penaltiesfixedtotal, la.penaltiesvartotal, la.principalcurrent, la.principaloverdue, la.isactive, la.loanallocationtype, la.additionlitemid, la.billingdetailid, la.clientbalanceid, la.loanid, la.paymentcorrectionid, la.commissionsum, la.isxlimit
  • Filter: la.isactive
  • Rows Removed by Filter: 36130
  • Buffers: shared hit=2052591
5. 0.000 2,913.849 ↓ 822.0 822 1

Nested Loop (cost=1,049.57..15,447.68 rows=1 width=4) (actual time=2,884.039..2,913.849 rows=822 loops=1)

  • Output: loan0_.loanid
  • Buffers: shared hit=2088802
6. 0.492 2,903.224 ↓ 205.5 822 1

Nested Loop (cost=1,049.15..15,393.43 rows=4 width=12) (actual time=2,884.018..2,903.224 rows=822 loops=1)

  • Output: loan0_.loanid, loanhistor5_.loanid, loanbalanc7_.loanid
  • Join Filter: (loanhistor5_.loanid = loan0_.loanid)
  • Buffers: shared hit=2079014
7. 1.374 2,901.088 ↓ 164.4 822 1

Hash Join (cost=1,048.86..15,391.12 rows=5 width=8) (actual time=2,884.008..2,901.088 rows=822 loops=1)

  • Output: loanhistor5_.loanid, loanbalanc7_.loanid
  • Hash Cond: (loanhistor5_.loanid = loanbalanc7_.loanid)
  • Buffers: shared hit=2076545
8. 4.803 15.943 ↓ 160.1 4,482 1

Index Scan using loanhistory_planenddate_idx on public.loanhistory loanhistor5_ (cost=0.29..14,311.21 rows=28 width=4) (actual time=0.027..15.943 rows=4,482 loops=1)

  • Output: loanhistor5_.loanhistoryid, loanhistor5_.created, loanhistor5_.updated, loanhistor5_.finalamount, loanhistor5_.finalmaturity, loanhistor5_.finalrate, loanhistor5_.historydate, loanhistor5_.isactive, loanhistor5_.planenddate, loanhistor5_.psk, loanhistor5_.pskrate, loanhistor5_.loanid
  • Index Cond: ((loanhistor5_.planenddate >= '2020-03-01'::date) AND (loanhistor5_.planenddate <= '2020-03-20'::date))
  • Filter: (loanhistor5_.isactive AND (loanhistor5_.historydate = (SubPlan 2)))
  • Rows Removed by Filter: 1088
  • Buffers: shared hit=23954
9.          

SubPlan (for Index Scan)

10. 0.000 11.140 ↑ 1.0 1 5,570

Aggregate (cost=2.51..2.52 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=5,570)

  • Output: max(lh.historydate)
  • Buffers: shared hit=19948
11. 11.140 11.140 ↓ 2.0 2 5,570

Index Scan using loanhistory_loanid_idx on public.loanhistory lh (cost=0.29..2.51 rows=1 width=8) (actual time=0.001..0.002 rows=2 loops=5,570)

  • Output: lh.loanhistoryid, lh.created, lh.updated, lh.finalamount, lh.finalmaturity, lh.finalrate, lh.historydate, lh.isactive, lh.planenddate, lh.psk, lh.pskrate, lh.loanid
  • Index Cond: (lh.loanid = loanhistor5_.loanid)
  • Filter: lh.isactive
  • Buffers: shared hit=19948
12. 3.810 2,883.771 ↓ 1.3 11,493 1

Hash (cost=937.02..937.02 rows=8,924 width=4) (actual time=2,883.771..2,883.771 rows=11,493 loops=1)

  • Output: loanbalanc7_.loanid
  • Buckets: 16384 Batches: 1 Memory Usage: 533kB
  • Buffers: shared hit=2052591
13. 2,879.961 2,879.961 ↓ 1.3 11,493 1

CTE Scan on alb loanbalanc7_ (cost=0.00..937.02 rows=8,924 width=4) (actual time=0.021..2,879.961 rows=11,493 loops=1)

  • Output: loanbalanc7_.loanid
  • Filter: ((((((loanbalanc7_.principalcurrent + loanbalanc7_.principaloverdue) + loanbalanc7_.interestcurrent) + loanbalanc7_.interestoverdue) + loanbalanc7_.penaltiesfixedtotal) + loanbalanc7_.penaltiesvartotal) >= '3000'::numeric)
  • Rows Removed by Filter: 43245
  • Buffers: shared hit=2052591
14. 1.644 1.644 ↑ 1.0 1 822

Index Scan using loan_pkey on public.loan loan0_ (cost=0.29..0.45 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=822)

  • Output: loan0_.loanid, loan0_.applicationcreditdecisionid
  • Index Cond: (loan0_.loanid = loanbalanc7_.loanid)
  • Buffers: shared hit=2469
15. 8.220 10.686 ↑ 1.0 1 822

Index Scan using loanstatushistory_loanid_idx on public.loanstatushistory loanstatus6_ (cost=0.42..13.55 rows=1 width=4) (actual time=0.006..0.013 rows=1 loops=822)

  • Output: loanstatus6_.loanstatushistoryid, loanstatus6_.created, loanstatus6_.updated, loanstatus6_.isactive, loanstatus6_.loanstatus, loanstatus6_.statusdate, loanstatus6_.loanid
  • Index Cond: (loanstatus6_.loanid = loan0_.loanid)
  • Filter: (loanstatus6_.isactive AND (loanstatus6_.loanstatus = 2) AND (loanstatus6_.statusdate = (SubPlan 3)))
  • Rows Removed by Filter: 2
  • Buffers: shared hit=9788
16.          

SubPlan (for Index Scan)

17. 0.000 2.466 ↑ 1.0 1 822

Aggregate (cost=3.24..3.25 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=822)

  • Output: max(lsh.statusdate)
  • Buffers: shared hit=4894
18. 2.466 2.466 ↑ 1.3 3 822

Index Scan using loanstatushistory_loanid_idx on public.loanstatushistory lsh (cost=0.42..3.23 rows=4 width=8) (actual time=0.001..0.003 rows=3 loops=822)

  • Output: lsh.loanstatushistoryid, lsh.created, lsh.updated, lsh.isactive, lsh.loanstatus, lsh.statusdate, lsh.loanid
  • Index Cond: (lsh.loanid = loanstatus6_.loanid)
  • Filter: lsh.isactive
  • Buffers: shared hit=4894