explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 18b4

Settings
# exclusive inclusive rows x rows loops node
1. 0.087 2,567.787 ↑ 1.0 1 1

Aggregate (cost=124,943.57..124,943.58 rows=1 width=8) (actual time=2,567.787..2,567.787 rows=1 loops=1)

  • Output: count(loan0_.loanid)
  • Buffers: shared hit=2,102,930 read=2
  • I/O Timings: read=0.032
2. 0.878 2,567.700 ↓ 900.0 900 1

Nested Loop (cost=15,176.98..124,943.57 rows=1 width=4) (actual time=1,674.504..2,567.700 rows=900 loops=1)

  • Output: loan0_.loanid
  • Buffers: shared hit=2,102,930 read=2
  • I/O Timings: read=0.032
3. 1.360 2,554.786 ↓ 71.6 1,003 1

Merge Join (cost=15,176.56..124,754.03 rows=14 width=12) (actual time=1,674.467..2,554.786 rows=1,003 loops=1)

  • Output: loan0_.loanid, loanhistor5_.loanid, la.loanid
  • Merge Cond: (loan0_.loanid = loanhistor5_.loanid)
  • Buffers: shared hit=2,090,744 read=2
  • I/O Timings: read=0.032
4. 6.479 2,535.106 ↑ 2.5 10,558 1

Merge Join (cost=0.72..113,114.19 rows=26,114 width=8) (actual time=0.032..2,535.106 rows=10,558 loops=1)

  • Output: loan0_.loanid, la.loanid
  • Merge Cond: (la.loanid = loan0_.loanid)
  • Buffers: shared hit=2,065,681
5. 1,199.376 2,492.752 ↑ 2.5 10,558 1

GroupAggregate (cost=0.43..110,371.69 rows=26,114 width=300) (actual time=0.026..2,492.752 rows=10,558 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)) >= '1'::numeric)
  • Rows Removed by Filter: 42,860
  • Buffers: shared hit=2,028,247
6. 1,293.376 1,293.376 ↑ 1.0 2,103,772 1

Index Scan using loanallocation_loanid_idx on public.loanallocation la (cost=0.43..72,902.67 rows=2,103,781 width=23) (actual time=0.011..1,293.376 rows=2,103,772 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: 36,122
  • Buffers: shared hit=2,028,247
7. 35.875 35.875 ↑ 1.0 54,713 1

Index Scan using loan_pkey on public.loan loan0_ (cost=0.29..2,015.62 rows=55,729 width=8) (actual time=0.003..35.875 rows=54,713 loops=1)

  • Output: loan0_.loanid, loan0_.applicationcreditdecisionid
  • Buffers: shared hit=37,434
8. 2.147 18.320 ↓ 158.5 4,754 1

Sort (cost=15,171.51..15,171.58 rows=30 width=4) (actual time=17.337..18.320 rows=4,754 loops=1)

  • Output: loanhistor5_.loanid
  • Sort Key: loanhistor5_.loanid
  • Sort Method: quicksort Memory: 415kB
  • Buffers: shared hit=25,063 read=2
  • I/O Timings: read=0.032
9. 4.459 16.173 ↓ 158.5 4,754 1

Index Scan using loanhistory_planenddate_idx on public.loanhistory loanhistor5_ (cost=0.29..15,170.77 rows=30 width=4) (actual time=0.021..16.173 rows=4,754 loops=1)

  • Output: loanhistor5_.loanid
  • Index Cond: ((loanhistor5_.planenddate >= '2020-03-01'::date) AND (loanhistor5_.planenddate <= '2020-03-21'::date))
  • Filter: (loanhistor5_.isactive AND (loanhistor5_.historydate = (SubPlan 1)))
  • Rows Removed by Filter: 1,103
  • Buffers: shared hit=25,063 read=2
  • I/O Timings: read=0.032
10.          

SubPlan (for Index Scan)

11. 0.000 11.714 ↑ 1.0 1 5,857

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

  • Output: max(lh.historydate)
  • Buffers: shared hit=20,872 read=1
  • I/O Timings: read=0.018
12. 11.714 11.714 ↓ 2.0 2 5,857

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,857)

  • 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=20,872 read=1
  • I/O Timings: read=0.018
13. 8.904 12.036 ↑ 1.0 1 1,003

Index Scan using loanstatushistory_loanid_idx on public.loanstatushistory loanstatus6_ (cost=0.42..13.53 rows=1 width=4) (actual time=0.006..0.012 rows=1 loops=1,003)

  • 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 = ANY ('{1,2,3,4,5,6}'::integer[])) AND (loanstatus6_.statusdate = (SubPlan 2)))
  • Rows Removed by Filter: 2
  • Buffers: shared hit=12,186
14.          

SubPlan (for Index Scan)

15. 0.000 3.132 ↑ 1.0 1 1,044

Aggregate (cost=3.23..3.24 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1,044)

  • Output: max(lsh.statusdate)
  • Buffers: shared hit=6,341
16. 3.132 3.132 ↑ 1.0 3 1,044

Index Scan using loanstatushistory_loanid_idx on public.loanstatushistory lsh (cost=0.42..3.22 rows=3 width=8) (actual time=0.001..0.003 rows=3 loops=1,044)

  • 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=6,341