explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gdDB

Settings
# exclusive inclusive rows x rows loops node
1. 0.184 2,782.111 ↑ 1.0 1 1

Aggregate (cost=187,982.27..187,982.28 rows=1 width=8) (actual time=2,782.111..2,782.111 rows=1 loops=1)

  • Output: count(loan0_.loanid)
  • Buffers: shared hit=2202868 read=2136
  • I/O Timings: read=20.355
2. 0.329 2,781.927 ↓ 98.0 1,372 1

Merge Anti Join (cost=75,712.17..187,982.24 rows=14 width=4) (actual time=101.287..2,781.927 rows=1,372 loops=1)

  • Output: loan0_.loanid
  • Merge Cond: (loan0_.loanid = prohibitio6_.loan_id)
  • Buffers: shared hit=2202868 read=2136
  • I/O Timings: read=20.355
3. 1.916 2,781.537 ↓ 98.1 1,373 1

Nested Loop Semi Join (cost=75,706.37..187,976.33 rows=14 width=4) (actual time=101.231..2,781.537 rows=1,373 loops=1)

  • Output: loan0_.loanid
  • Buffers: shared hit=2202865 read=2136
  • I/O Timings: read=20.355
4. 1.148 2,767.116 ↓ 44.7 2,501 1

Nested Loop (cost=75,706.08..187,815.37 rows=56 width=12) (actual time=101.209..2,767.116 rows=2,501 loops=1)

  • Output: loan0_.loanid, loanstatus5_.loanid, la.loanid
  • Join Filter: (loanstatus5_.loanid = loan0_.loanid)
  • Buffers: shared hit=2190472 read=2136
  • I/O Timings: read=20.355
5. 6.307 2,755.964 ↓ 44.7 2,501 1

Merge Join (cost=75,705.79..187,703.51 rows=56 width=8) (actual time=101.194..2,755.964 rows=2,501 loops=1)

  • Output: loanstatus5_.loanid, la.loanid
  • Merge Cond: (la.loanid = loanstatus5_.loanid)
  • Buffers: shared hit=2182950 read=2136
  • I/O Timings: read=20.355
6. 1,111.221 2,650.180 ↑ 3.0 8,865 1

GroupAggregate (cost=0.43..111,662.39 rows=26,772 width=300) (actual time=0.075..2,650.180 rows=8,865 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.interestcurrent), '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: 43916
  • Buffers: shared hit=2041146 read=2070
  • I/O Timings: read=19.726
7. 1,538.959 1,538.959 ↑ 1.0 2,119,232 1

Index Scan using loanallocation_loanid_idx on public.loanallocation la (cost=0.43..73,744.24 rows=2,128,506 width=20) (actual time=0.054..1,538.959 rows=2,119,232 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: 36133
  • Buffers: shared hit=2041146 read=2070
  • I/O Timings: read=19.726
8. 1.958 99.477 ↓ 29.6 3,285 1

Sort (cost=75,705.36..75,705.63 rows=111 width=4) (actual time=98.414..99.477 rows=3,285 loops=1)

  • Output: loanstatus5_.loanid
  • Sort Key: loanstatus5_.loanid
  • Sort Method: quicksort Memory: 250kB
  • Buffers: shared hit=141804 read=66
  • I/O Timings: read=0.629
9. 20.147 97.519 ↓ 29.6 3,285 1

Index Scan using loanstatushistory_loanstatus_idx on public.loanstatushistory loanstatus5_ (cost=0.42..75,701.58 rows=111 width=4) (actual time=0.106..97.519 rows=3,285 loops=1)

  • Output: loanstatus5_.loanid
  • Index Cond: (loanstatus5_.loanstatus = 2)
  • Filter: (loanstatus5_.isactive AND (loanstatus5_.statusdate = (SubPlan 1)))
  • Rows Removed by Filter: 18991
  • Buffers: shared hit=141804 read=66
  • I/O Timings: read=0.629
10.          

SubPlan (for Index Scan)

11. 0.000 77.372 ↑ 1.0 1 19,343

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

  • Output: max(lsh.statusdate)
  • Buffers: shared hit=139184
12. 77.372 77.372 ↑ 1.0 4 19,343

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

  • Output: lsh.loanstatushistoryid, lsh.created, lsh.updated, lsh.isactive, lsh.loanstatus, lsh.statusdate, lsh.loanid
  • Index Cond: (lsh.loanid = loanstatus5_.loanid)
  • Filter: lsh.isactive
  • Rows Removed by Filter: 0
  • Buffers: shared hit=139184
13. 10.004 10.004 ↑ 1.0 1 2,501

Index Scan using loan_pkey on public.loan loan0_ (cost=0.29..1.99 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=2,501)

  • Output: loan0_.loanid, loan0_.applicationcreditdecisionid
  • Index Cond: (loan0_.loanid = la.loanid)
  • Buffers: shared hit=7522
14. 9.657 12.505 ↑ 1.0 1 2,501

Index Scan using loanhistory_loanid_idx on public.loanhistory loanhistor7_ (cost=0.29..2.86 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=2,501)

  • Output: loanhistor7_.loanhistoryid, loanhistor7_.created, loanhistor7_.updated, loanhistor7_.finalamount, loanhistor7_.finalmaturity, loanhistor7_.finalrate, loanhistor7_.historydate, loanhistor7_.isactive, loanhistor7_.planenddate, loanhistor7_.psk, loanhistor7_.pskrate, loanhistor7_.loanid
  • Index Cond: (loanhistor7_.loanid = loan0_.loanid)
  • Filter: (loanhistor7_.isactive AND (loanhistor7_.planenddate >= '2019-01-01'::date) AND (loanhistor7_.planenddate <= '2019-11-30'::date) AND ((SubPlan 2) = loanhistor7_.historydate))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=12393
15.          

SubPlan (for Index Scan)

16. 1.424 2.848 ↑ 1.0 1 1,424

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

  • Output: max(loanhistor8_.historydate)
  • Buffers: shared hit=4415
17. 1.424 1.424 ↑ 1.0 1 1,424

Index Scan using loanhistory_loanid_idx on public.loanhistory loanhistor8_ (cost=0.29..2.51 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1,424)

  • Output: loanhistor8_.loanhistoryid, loanhistor8_.created, loanhistor8_.updated, loanhistor8_.finalamount, loanhistor8_.finalmaturity, loanhistor8_.finalrate, loanhistor8_.historydate, loanhistor8_.isactive, loanhistor8_.planenddate, loanhistor8_.psk, loanhistor8_.pskrate, loanhistor8_.loanid
  • Index Cond: (loanhistor8_.loanid = loan0_.loanid)
  • Filter: loanhistor8_.isactive
  • Buffers: shared hit=4415
18. 0.017 0.061 ↑ 2.0 6 1

Sort (cost=5.80..5.83 rows=12 width=4) (actual time=0.055..0.061 rows=6 loops=1)

  • Output: prohibitio6_.loan_id
  • Sort Key: prohibitio6_.loan_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=3
19. 0.044 0.044 ↑ 1.2 10 1

Seq Scan on public.prohibition prohibitio6_ (cost=0.00..5.59 rows=12 width=4) (actual time=0.019..0.044 rows=10 loops=1)

  • Output: prohibitio6_.loan_id
  • Filter: (prohibitio6_.is_active AND (prohibitio6_.type = 1))
  • Rows Removed by Filter: 206
  • Buffers: shared hit=3