explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CJZe

Settings
# exclusive inclusive rows x rows loops node
1. 0.391 2,792.888 ↑ 1.0 1 1

Aggregate (cost=187,706.58..187,706.59 rows=1 width=8) (actual time=2,792.888..2,792.888 rows=1 loops=1)

  • Output: count(loan0_.loanid)
  • Buffers: shared hit=2164170 read=35388
  • I/O Timings: read=250.834
2. 0.049 2,792.497 ↓ 62.5 3,313 1

Nested Loop (cost=75,706.08..187,706.45 rows=53 width=4) (actual time=110.565..2,792.497 rows=3,313 loops=1)

  • Output: loan0_.loanid
  • Join Filter: (loanstatus6_.loanid = loan0_.loanid)
  • Buffers: shared hit=2164170 read=35388
  • I/O Timings: read=250.834
3. 2.133 2,782.509 ↓ 59.2 3,313 1

Merge Join (cost=75,705.79..187,685.86 rows=56 width=8) (actual time=110.548..2,782.509 rows=3,313 loops=1)

  • Output: loanstatus6_.loanid, la.loanid
  • Merge Cond: (la.loanid = loanstatus6_.loanid)
  • Buffers: shared hit=2154211 read=35388
  • I/O Timings: read=250.834
4. 1,213.113 2,675.814 ↑ 2.6 10,316 1

GroupAggregate (cost=0.43..111,644.74 rows=26,772 width=300) (actual time=0.097..2,675.814 rows=10,316 loops=1)

  • Output: la.loanid, NULL::numeric, NULL::numeric, NULL::numeric, NULL::numeric, NULL::numeric, NULL::numeric, NULL::numeric, NULL::timestamp without time zone, NULL::unknown, NULL::unknown
  • Group Key: la.loanid
  • Filter: ((((((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)) >= '3000'::numeric)
  • Rows Removed by Filter: 43102
  • Buffers: shared hit=2012437 read=35322
  • I/O Timings: read=235.926
5. 1,462.701 1,462.701 ↑ 1.0 2,123,792 1

Index Scan using loanallocation_loanid_idx on public.loanallocation la (cost=0.43..73,732.60 rows=2,128,162 width=23) (actual time=0.084..1,462.701 rows=2,123,792 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: 36126
  • Buffers: shared hit=2012437 read=35322
  • I/O Timings: read=235.926
6. 1.756 104.562 ↓ 29.9 3,319 1

Sort (cost=75,705.36..75,705.63 rows=111 width=4) (actual time=103.592..104.562 rows=3,319 loops=1)

  • Output: loanstatus6_.loanid
  • Sort Key: loanstatus6_.loanid
  • Sort Method: quicksort Memory: 252kB
  • Buffers: shared hit=141774 read=66
  • I/O Timings: read=14.908
7. 25.430 102.806 ↓ 29.9 3,319 1

Index Scan using loanstatushistory_loanstatus_idx on public.loanstatushistory loanstatus6_ (cost=0.42..75,701.58 rows=111 width=4) (actual time=0.058..102.806 rows=3,319 loops=1)

  • Output: loanstatus6_.loanid
  • Index Cond: (loanstatus6_.loanstatus = 2)
  • Filter: (loanstatus6_.isactive AND (loanstatus6_.statusdate = (SubPlan 1)))
  • Rows Removed by Filter: 18957
  • Buffers: shared hit=141774 read=66
  • I/O Timings: read=14.908
8.          

SubPlan (for Index Scan)

9. 19.344 77.376 ↑ 1.0 1 19,344

Aggregate (cost=3.24..3.25 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=19,344)

  • Output: max(lsh.statusdate)
  • Buffers: shared hit=139155
10. 58.032 58.032 ↑ 1.0 4 19,344

Index Scan using loanstatushistory_loanid_idx on public.loanstatushistory lsh (cost=0.42..3.23 rows=4 width=8) (actual time=0.002..0.003 rows=4 loops=19,344)

  • Output: lsh.loanstatushistoryid, lsh.created, lsh.updated, lsh.isactive, lsh.loanstatus, lsh.statusdate, lsh.loanid
  • Index Cond: (lsh.loanid = loanstatus6_.loanid)
  • Filter: lsh.isactive
  • Rows Removed by Filter: 0
  • Buffers: shared hit=139155
11. 9.939 9.939 ↑ 1.0 1 3,313

Index Scan using loan_pkey on public.loan loan0_ (cost=0.29..0.36 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=3,313)

  • Output: loan0_.loanid, loan0_.applicationcreditdecisionid
  • Index Cond: (loan0_.loanid = la.loanid)
  • Buffers: shared hit=9959