explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qYSs

Settings
# exclusive inclusive rows x rows loops node
1. 1.655 4,084.083 ↑ 1.0 1 1

Aggregate (cost=180,539.45..180,539.46 rows=1 width=8) (actual time=4,084.080..4,084.083 rows=1 loops=1)

  • Output: count(loan0_.loanid)
  • Buffers: shared hit=126657
2. 3.675 4,082.428 ↓ 61.1 856 1

Hash Anti Join (cost=61,671.09..180,539.41 rows=14 width=4) (actual time=3,426.664..4,082.428 rows=856 loops=1)

  • Output: loan0_.loanid
  • Hash Cond: (loan0_.loanid = prohibitio6_.loan_id)
  • Buffers: shared hit=126657
3. 13.644 4,078.657 ↓ 61.2 857 1

Nested Loop Semi Join (cost=61,665.37..180,533.51 rows=14 width=4) (actual time=3,426.527..4,078.657 rows=857 loops=1)

  • Output: loan0_.loanid
  • Buffers: shared hit=126654
4. 18.974 4,024.621 ↓ 41.6 2,244 1

Nested Loop (cost=61,665.08..180,062.58 rows=54 width=12) (actual time=3,426.476..4,024.621 rows=2,244 loops=1)

  • Output: loan0_.loanid, loanstatus5_.loanid, loanbalanc9_.loanid
  • Join Filter: (loanstatus5_.loanid = loan0_.loanid)
  • Buffers: shared hit=116604
5. 14.757 3,983.207 ↓ 41.6 2,244 1

Hash Join (cost=61,664.79..179,719.14 rows=54 width=8) (actual time=3,426.441..3,983.207 rows=2,244 loops=1)

  • Output: loanstatus5_.loanid, loanbalanc9_.loanid
  • Hash Cond: (loanstatus5_.loanid = loanbalanc9_.loanid)
  • Buffers: shared hit=109857
6. 100.347 545.520 ↓ 31.4 3,427 1

Bitmap Heap Scan on public.loanstatushistory loanstatus5_ (cost=415.50..118,468.90 rows=109 width=4) (actual time=3.455..545.520 rows=3,427 loops=1)

  • Output: loanstatus5_.loanstatushistoryid, loanstatus5_.created, loanstatus5_.updated, loanstatus5_.isactive, loanstatus5_.loanstatus, loanstatus5_.statusdate, loanstatus5_.loanid
  • Recheck Cond: (loanstatus5_.loanstatus = 2)
  • Filter: (loanstatus5_.isactive AND (loanstatus5_.statusdate = (SubPlan 2)))
  • Rows Removed by Filter: 18793
  • Heap Blocks: exact=1515
  • Buffers: shared hit=78976
7. 1.572 1.572 ↑ 1.0 22,220 1

Bitmap Index Scan on loanstatushistory_loanstatus_idx (cost=0.00..415.48 rows=22,274 width=0) (actual time=1.571..1.572 rows=22,220 loops=1)

  • Index Cond: (loanstatus5_.loanstatus = 2)
  • Buffers: shared hit=64
8.          

SubPlan (for Bitmap Heap Scan)

9. 135.009 443.601 ↑ 1.0 1 19,287

Result (cost=5.20..5.21 rows=1 width=8) (actual time=0.021..0.023 rows=1 loops=19,287)

  • Output: $1
  • Buffers: shared hit=77397
10.          

Initplan (for Result)

11. 154.296 308.592 ↑ 1.0 1 19,287

Limit (cost=0.42..5.20 rows=1 width=8) (actual time=0.011..0.016 rows=1 loops=19,287)

  • Output: lsh.statusdate
  • Buffers: shared hit=77397
12. 154.296 154.296 ↑ 3.0 1 19,287

Index Scan using loanstatushistory_loanid_statusdate_idx on public.loanstatushistory lsh (cost=0.42..14.75 rows=3 width=8) (actual time=0.007..0.008 rows=1 loops=19,287)

  • Output: lsh.statusdate
  • Index Cond: ((lsh.loanid = loanstatus5_.loanid) AND (lsh.statusdate IS NOT NULL))
  • Filter: lsh.isactive
  • Rows Removed by Filter: 0
  • Buffers: shared hit=77397
13. 21.195 3,422.930 ↑ 2.7 10,006 1

Hash (cost=60,917.66..60,917.66 rows=26,530 width=4) (actual time=3,422.929..3,422.930 rows=10,006 loops=1)

  • Output: loanbalanc9_.loanid
  • Buckets: 32768 Batches: 1 Memory Usage: 608kB
  • Buffers: shared hit=30881
14. 31.200 3,401.735 ↑ 2.7 10,006 1

Subquery Scan on loanbalanc9_ (cost=59,989.11..60,917.66 rows=26,530 width=4) (actual time=3,280.844..3,401.735 rows=10,006 loops=1)

  • Output: loanbalanc9_.loanid
  • Buffers: shared hit=30881
15. 562.233 3,370.535 ↑ 2.7 10,006 1

Finalize HashAggregate (cost=59,989.11..60,652.36 rows=26,530 width=300) (actual time=3,280.841..3,370.535 rows=10,006 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: 43907
  • Buffers: shared hit=30881
16. 249.240 2,808.302 ↑ 1.2 87,951 1

Gather (cost=45,331.29..56,540.21 rows=106,120 width=164) (actual time=2,483.004..2,808.302 rows=87,951 loops=1)

  • Output: la.loanid, (PARTIAL sum(la.principalcurrent)), (PARTIAL sum(la.interestcurrent)), (PARTIAL sum(la.interestoverdue)), (PARTIAL sum(la.penaltiesfixedtotal)), (PARTIAL sum(la.penaltiesvartotal))
  • Workers Planned: 4
  • Workers Launched: 4
  • Buffers: shared hit=30881
17. 1,516.693 2,559.062 ↑ 1.5 17,590 5 / 5

Partial HashAggregate (cost=44,331.29..44,928.21 rows=26,530 width=164) (actual time=2,471.244..2,559.062 rows=17,590 loops=5)

  • Output: la.loanid, PARTIAL sum(la.principalcurrent), PARTIAL sum(la.interestcurrent), PARTIAL sum(la.interestoverdue), PARTIAL sum(la.penaltiesfixedtotal), PARTIAL sum(la.penaltiesvartotal)
  • Group Key: la.loanid
  • Buffers: shared hit=30881
  • Worker 0: actual time=2469.489..2556.057 rows=17149 loops=1
  • Buffers: shared hit=5937
  • Worker 1: actual time=2470.235..2556.714 rows=17437 loops=1
  • Buffers: shared hit=6137
  • Worker 2: actual time=2472.414..2564.613 rows=17568 loops=1
  • Buffers: shared hit=6208
  • Worker 3: actual time=2461.651..2547.837 rows=17328 loops=1
  • Buffers: shared hit=6031
18. 1,042.369 1,042.369 ↑ 1.3 427,462 5 / 5

Parallel Seq Scan on public.loanallocation la (cost=0.00..36,314.91 rows=534,425 width=20) (actual time=0.036..1,042.369 rows=427,462 loops=5)

  • Output: la.loanid, la.principalcurrent, la.interestcurrent, la.interestoverdue, la.penaltiesfixedtotal, la.penaltiesvartotal
  • Filter: la.isactive
  • Rows Removed by Filter: 7227
  • Buffers: shared hit=30881
  • Worker 0: actual time=0.045..1038.910 rows=410473 loops=1
  • Buffers: shared hit=5937
  • Worker 1: actual time=0.046..1055.147 rows=424711 loops=1
  • Buffers: shared hit=6137
  • Worker 2: actual time=0.032..1040.147 rows=429242 loops=1
  • Buffers: shared hit=6208
  • Worker 3: actual time=0.046..1024.714 rows=417970 loops=1
  • Buffers: shared hit=6031
19. 22.440 22.440 ↑ 1.0 1 2,244

Index Scan using loan_pkey on public.loan loan0_ (cost=0.29..6.35 rows=1 width=8) (actual time=0.008..0.010 rows=1 loops=2,244)

  • Output: loan0_.loanid, loan0_.created, loan0_.updated, loan0_.currency, loan0_.loannumber, loan0_.maturitymeasure, loan0_.startdatetime, loan0_.applicationcreditdecisionid, loan0_.factenddate, loan0_.tradeuuid
  • Index Cond: (loan0_.loanid = loanbalanc9_.loanid)
  • Buffers: shared hit=6747
20. 24.642 40.392 ↓ 0.0 0 2,244

Index Scan using loanhistory_loanid_idx on public.loanhistory loanhistor7_ (cost=0.29..8.71 rows=1 width=12) (actual time=0.018..0.018 rows=0 loops=2,244)

  • 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 3) = loanhistor7_.historydate))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=10050
21.          

SubPlan (for Index Scan)

22. 8.750 15.750 ↑ 1.0 1 875

Aggregate (cost=8.31..8.32 rows=1 width=8) (actual time=0.016..0.018 rows=1 loops=875)

  • Output: max(loanhistor8_.historydate)
  • Buffers: shared hit=2696
23. 7.000 7.000 ↑ 1.0 1 875

Index Scan using loanhistory_loanid_idx on public.loanhistory loanhistor8_ (cost=0.29..8.31 rows=1 width=8) (actual time=0.006..0.008 rows=1 loops=875)

  • 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=2696
24. 0.027 0.096 ↑ 1.2 10 1

Hash (cost=5.58..5.58 rows=12 width=4) (actual time=0.093..0.096 rows=10 loops=1)

  • Output: prohibitio6_.loan_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=3
25. 0.069 0.069 ↑ 1.2 10 1

Seq Scan on public.prohibition prohibitio6_ (cost=0.00..5.58 rows=12 width=4) (actual time=0.022..0.069 rows=10 loops=1)

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