explain.depesz.com

PostgreSQL's explain analyze made readable

Result: h9uJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.113 2,980.785 ↑ 1.0 1 1

Aggregate (cost=126,967.18..126,967.19 rows=1 width=8) (actual time=2,980.785..2,980.785 rows=1 loops=1)

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

CTE alb

3. 1,488.595 2,885.258 ↓ 2.0 54,738 1

GroupAggregate (cost=0.43..122,352.48 rows=26,772 width=300) (actual time=0.018..2,885.258 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,396.663 1,396.663 ↓ 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.011..1,396.663 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.496 2,980.672 ↓ 822.0 822 1

Nested Loop (cost=307.24..4,346.98 rows=1 width=4) (actual time=1,976.217..2,980.672 rows=822 loops=1)

  • Output: loan0_.loanid
  • Join Filter: ((loan0_.loanid = loanstatus6_.loanid) AND ((SubPlan 3) = loanstatus6_.statusdate))
  • Buffers: shared hit=2078834
6. 1.013 2,974.422 ↓ 822.0 822 1

Nested Loop (cost=306.82..4,342.91 rows=1 width=12) (actual time=1,976.190..2,974.422 rows=822 loops=1)

  • Output: loan0_.loanid, loanhistor5_.loanid, loanbalanc7_.loanid
  • Join Filter: ((loanhistor5_.loanid = loan0_.loanid) AND (loanhistor5_.historydate = (SubPlan 2)))
  • Rows Removed by Join Filter: 903
  • Buffers: shared hit=2069046
7. 10.068 2,963.059 ↓ 1.8 1,725 1

Hash Join (cost=306.53..1,420.66 rows=978 width=16) (actual time=1,976.143..2,963.059 rows=1,725 loops=1)

  • Output: loanhistor5_.loanid, loanhistor5_.historydate, loanbalanc7_.loanid
  • Hash Cond: (loanbalanc7_.loanid = loanhistor5_.loanid)
  • Buffers: shared hit=2056597
8. 2,948.857 2,948.857 ↓ 1.3 11,493 1

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

  • Output: loanbalanc7_.loanid, loanbalanc7_.principalcurrent, loanbalanc7_.principaloverdue, loanbalanc7_.interestcurrent, loanbalanc7_.interestoverdue, loanbalanc7_.penaltiesfixedtotal, loanbalanc7_.penaltiesvartotal, loanbalanc7_.additionalitemsum, loanbalanc7_.billingdate, loanbalanc7_.created, loanbalanc7_.updated
  • Filter: ((((((loanbalanc7_.principalcurrent + loanbalanc7_.principaloverdue) + loanbalanc7_.interestcurrent) + loanbalanc7_.interestoverdue) + loanbalanc7_.penaltiesfixedtotal) + loanbalanc7_.penaltiesvartotal) >= '3000'::numeric)
  • Rows Removed by Filter: 43245
  • Buffers: shared hit=2052591
9. 0.702 4.134 ↑ 1.0 5,570 1

Hash (cost=236.86..236.86 rows=5,574 width=12) (actual time=4.134..4.134 rows=5,570 loops=1)

  • Output: loanhistor5_.loanid, loanhistor5_.historydate
  • Buckets: 8192 Batches: 1 Memory Usage: 326kB
  • Buffers: shared hit=4006
10. 3.432 3.432 ↑ 1.0 5,570 1

Index Scan using loanhistory_planenddate_idx on public.loanhistory loanhistor5_ (cost=0.29..236.86 rows=5,574 width=12) (actual time=0.014..3.432 rows=5,570 loops=1)

  • Output: loanhistor5_.loanid, loanhistor5_.historydate
  • Index Cond: ((loanhistor5_.planenddate >= '2020-03-01'::date) AND (loanhistor5_.planenddate <= '2020-03-20'::date))
  • Filter: loanhistor5_.isactive
  • Buffers: shared hit=4006
11. 3.450 3.450 ↑ 1.0 1 1,725

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=1,725)

  • Output: loan0_.loanid, loan0_.applicationcreditdecisionid
  • Index Cond: (loan0_.loanid = loanbalanc7_.loanid)
  • Buffers: shared hit=5179
12.          

SubPlan (for Nested Loop)

13. 1.725 6.900 ↑ 1.0 1 1,725

Aggregate (cost=2.51..2.52 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=1,725)

  • Output: max(lh.historydate)
  • Buffers: shared hit=7270
14. 5.175 5.175 ↓ 2.0 2 1,725

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

  • 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 = loan0_.loanid)
  • Filter: lh.isactive
  • Buffers: shared hit=7270
15. 2.466 2.466 ↑ 1.0 1 822

Index Scan using loanstatushistory_loanid_idx on public.loanstatushistory loanstatus6_ (cost=0.42..0.81 rows=1 width=12) (actual time=0.002..0.003 rows=1 loops=822)

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

SubPlan (for Nested Loop)

17. 0.822 3.288 ↑ 1.0 1 822

Aggregate (cost=3.24..3.25 rows=1 width=8) (actual time=0.003..0.004 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 = loan0_.loanid)
  • Filter: lsh.isactive
  • Buffers: shared hit=4894