explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NWsH

Settings
# exclusive inclusive rows x rows loops node
1. 1.488 4,099.646 ↑ 1.0 1 1

Aggregate (cost=179,975.83..179,975.84 rows=1 width=8) (actual time=4,099.643..4,099.646 rows=1 loops=1)

  • Output: count(loan0_.loanid)
  • Buffers: shared hit=125167
2. 3.107 4,098.158 ↓ 61.1 856 1

Hash Anti Join (cost=61,446.66..179,975.80 rows=14 width=4) (actual time=3,503.836..4,098.158 rows=856 loops=1)

  • Output: loan0_.loanid
  • Hash Cond: (loan0_.loanid = prohibitio6_.loan_id)
  • Buffers: shared hit=125167
3. 11.407 4,094.952 ↓ 61.2 857 1

Nested Loop Semi Join (cost=61,440.94..179,969.90 rows=14 width=4) (actual time=3,503.688..4,094.952 rows=857 loops=1)

  • Output: loan0_.loanid
  • Buffers: shared hit=125164
4. 14.956 4,053.333 ↓ 40.0 2,158 1

Nested Loop (cost=61,440.65..179,498.97 rows=54 width=12) (actual time=3,503.631..4,053.333 rows=2,158 loops=1)

  • Output: loan0_.loanid, loanstatus5_.loanid, loanbalanc9_.loanid
  • Join Filter: (loanstatus5_.loanid = loan0_.loanid)
  • Buffers: shared hit=115443
5. 11.252 4,021.113 ↓ 40.0 2,158 1

Hash Join (cost=61,440.36..179,155.53 rows=54 width=8) (actual time=3,503.592..4,021.113 rows=2,158 loops=1)

  • Output: loanstatus5_.loanid, loanbalanc9_.loanid
  • Hash Cond: (loanstatus5_.loanid = loanbalanc9_.loanid)
  • Buffers: shared hit=108954
6. 88.279 510.841 ↓ 29.7 3,242 1

Bitmap Heap Scan on public.loanstatushistory loanstatus5_ (cost=415.02..118,129.23 rows=109 width=4) (actual time=4.490..510.841 rows=3,242 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=1510
  • Buffers: shared hit=78229
7. 2.318 2.318 ↑ 1.0 22,035 1

Bitmap Index Scan on loanstatushistory_loanstatus_idx (cost=0.00..415.00 rows=22,210 width=0) (actual time=2.317..2.318 rows=22,035 loops=1)

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

SubPlan (for Bitmap Heap Scan)

9. 133.714 420.244 ↑ 1.0 1 19,102

Result (cost=5.20..5.21 rows=1 width=8) (actual time=0.020..0.022 rows=1 loops=19,102)

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

Initplan (for Result)

11. 133.714 286.530 ↑ 1.0 1 19,102

Limit (cost=0.42..5.20 rows=1 width=8) (actual time=0.010..0.015 rows=1 loops=19,102)

  • Output: lsh.statusdate
  • Buffers: shared hit=76656
12. 152.816 152.816 ↑ 3.0 1 19,102

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

  • 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=76656
13. 23.324 3,499.020 ↑ 2.6 10,100 1

Hash (cost=60,693.71..60,693.71 rows=26,530 width=4) (actual time=3,499.019..3,499.020 rows=10,100 loops=1)

  • Output: loanbalanc9_.loanid
  • Buckets: 32768 Batches: 1 Memory Usage: 612kB
  • Buffers: shared hit=30725
14. 33.929 3,475.696 ↑ 2.6 10,100 1

Subquery Scan on loanbalanc9_ (cost=59,765.16..60,693.71 rows=26,530 width=4) (actual time=3,339.099..3,475.696 rows=10,100 loops=1)

  • Output: loanbalanc9_.loanid
  • Buffers: shared hit=30725
15. 609.969 3,441.767 ↑ 2.6 10,100 1

Finalize HashAggregate (cost=59,765.16..60,428.41 rows=26,530 width=300) (actual time=3,339.095..3,441.767 rows=10,100 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: 43813
  • Buffers: shared hit=30725
16. 282.559 2,831.798 ↑ 1.3 82,530 1

Gather (cost=45,107.33..56,316.26 rows=106,120 width=164) (actual time=2,461.280..2,831.798 rows=82,530 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=30725
17. 1,528.481 2,549.239 ↑ 1.6 16,506 5 / 5

Partial HashAggregate (cost=44,107.33..44,704.26 rows=26,530 width=164) (actual time=2,454.907..2,549.239 rows=16,506 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=30725
  • Worker 0: actual time=2453.298..2542.546 rows=16706 loops=1
  • Buffers: shared hit=6190
  • Worker 1: actual time=2452.759..2555.029 rows=17054 loops=1
  • Buffers: shared hit=6237
  • Worker 2: actual time=2455.106..2539.900 rows=14825 loops=1
  • Buffers: shared hit=5778
  • Worker 3: actual time=2452.684..2555.321 rows=17009 loops=1
  • Buffers: shared hit=6204
18. 1,020.758 1,020.758 ↑ 1.3 425,295 5 / 5

Parallel Seq Scan on public.loanallocation la (cost=0.00..36,131.46 rows=531,725 width=20) (actual time=0.031..1,020.758 rows=425,295 loops=5)

  • Output: la.loanid, la.principalcurrent, la.interestcurrent, la.interestoverdue, la.penaltiesfixedtotal, la.penaltiesvartotal
  • Filter: la.isactive
  • Rows Removed by Filter: 7225
  • Buffers: shared hit=30725
  • Worker 0: actual time=0.032..1018.376 rows=428745 loops=1
  • Buffers: shared hit=6190
  • Worker 1: actual time=0.034..1037.468 rows=431705 loops=1
  • Buffers: shared hit=6237
  • Worker 2: actual time=0.033..1038.450 rows=399509 loops=1
  • Buffers: shared hit=5778
  • Worker 3: actual time=0.047..1029.979 rows=429233 loops=1
  • Buffers: shared hit=6204
19. 17.264 17.264 ↑ 1.0 1 2,158

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

  • 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=6489
20. 17.087 30.212 ↓ 0.0 0 2,158

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

  • 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=9721
21.          

SubPlan (for Index Scan)

22. 7.000 13.125 ↑ 1.0 1 875

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

  • Output: max(loanhistor8_.historydate)
  • Buffers: shared hit=2696
23. 6.125 6.125 ↑ 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.005..0.007 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.028 0.099 ↑ 1.2 10 1

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

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

Seq Scan on public.prohibition prohibitio6_ (cost=0.00..5.58 rows=12 width=4) (actual time=0.024..0.071 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