explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vawt

Settings
# exclusive inclusive rows x rows loops node
1. 0.128 1,634.857 ↑ 1.0 1 1

Aggregate (cost=137,423.99..137,424.00 rows=1 width=8) (actual time=1,634.857..1,634.857 rows=1 loops=1)

  • Output: count(loan0_.loanid)
  • Buffers: shared hit=192771
2. 0.289 1,634.729 ↓ 98.0 1,372 1

Hash Anti Join (cost=61,449.41..137,423.96 rows=14 width=4) (actual time=1,522.692..1,634.729 rows=1,372 loops=1)

  • Output: loan0_.loanid
  • Hash Cond: (loan0_.loanid = prohibitio6_.loan_id)
  • Buffers: shared hit=192771
3. 1.969 1,634.402 ↓ 98.1 1,373 1

Nested Loop Semi Join (cost=61,443.67..137,418.04 rows=14 width=4) (actual time=1,522.625..1,634.402 rows=1,373 loops=1)

  • Output: loan0_.loanid
  • Buffers: shared hit=192768
4. 1.418 1,624.930 ↓ 44.7 2,501 1

Nested Loop (cost=61,443.38..137,257.09 rows=56 width=12) (actual time=1,521.286..1,624.930 rows=2,501 loops=1)

  • Output: loan0_.loanid, loanstatus5_.loanid, loanbalanc9_.loanid
  • Join Filter: (loanstatus5_.loanid = loan0_.loanid)
  • Buffers: shared hit=180375
5. 1.590 1,618.510 ↓ 44.7 2,501 1

Hash Join (cost=61,443.09..137,145.23 rows=56 width=8) (actual time=1,521.260..1,618.510 rows=2,501 loops=1)

  • Output: loanstatus5_.loanid, loanbalanc9_.loanid
  • Hash Cond: (loanstatus5_.loanid = loanbalanc9_.loanid)
  • Buffers: shared hit=172853
6. 18.469 95.841 ↓ 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.039..95.841 rows=3,285 loops=1)

  • Output: loanstatus5_.loanstatushistoryid, loanstatus5_.created, loanstatus5_.updated, loanstatus5_.isactive, loanstatus5_.loanstatus, loanstatus5_.statusdate, loanstatus5_.loanid
  • Index Cond: (loanstatus5_.loanstatus = 2)
  • Filter: (loanstatus5_.isactive AND (loanstatus5_.statusdate = (SubPlan 1)))
  • Rows Removed by Filter: 18991
  • Buffers: shared hit=141870
7.          

SubPlan (for Index Scan)

8. 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
9. 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
10. 1.772 1,521.079 ↑ 2.5 10,606 1

Hash (cost=61,108.02..61,108.02 rows=26,772 width=4) (actual time=1,521.079..1,521.079 rows=10,606 loops=1)

  • Output: loanbalanc9_.loanid
  • Buckets: 32768 Batches: 1 Memory Usage: 629kB
  • Buffers: shared hit=30983
11. 1.203 1,519.307 ↑ 2.5 10,606 1

Subquery Scan on loanbalanc9_ (cost=60,171.00..61,108.02 rows=26,772 width=4) (actual time=1,469.276..1,519.307 rows=10,606 loops=1)

  • Output: loanbalanc9_.loanid
  • Buffers: shared hit=30983
12. 430.793 1,518.104 ↑ 2.5 10,606 1

Finalize HashAggregate (cost=60,171.00..60,840.30 rows=26,772 width=300) (actual time=1,469.275..1,518.104 rows=10,606 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: 44256
  • Buffers: shared hit=30983
13. 90.575 1,087.311 ↓ 2.2 236,372 1

Gather (cost=45,379.46..56,690.64 rows=107,088 width=164) (actual time=895.740..1,087.311 rows=236,372 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=30983
14. 813.556 996.736 ↓ 1.8 47,274 5 / 5

Partial HashAggregate (cost=44,379.46..44,981.83 rows=26,772 width=164) (actual time=889.755..996.736 rows=47,274 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=30983
  • Worker 0: actual time=890.281..997.172 rows=47749 loops=1
  • Buffers: shared hit=6167
  • Worker 1: actual time=890.492..988.853 rows=44626 loops=1
  • Buffers: shared hit=5141
  • Worker 2: actual time=890.570..1005.370 rows=49831 loops=1
  • Buffers: shared hit=7242
  • Worker 3: actual time=882.137..975.332 rows=42930 loops=1
  • Buffers: shared hit=3797
15. 183.180 183.180 ↑ 1.2 425,839 5 / 5

Parallel Seq Scan on public.loanallocation la (cost=0.00..36,397.57 rows=532,126 width=20) (actual time=0.014..183.180 rows=425,839 loops=5)

  • Output: la.loanid, la.principalcurrent, la.interestcurrent, la.interestoverdue, la.penaltiesfixedtotal, la.penaltiesvartotal
  • Filter: la.isactive
  • Rows Removed by Filter: 7229
  • Buffers: shared hit=30983
  • Worker 0: actual time=0.014..173.116 rows=424339 loops=1
  • Buffers: shared hit=6167
  • Worker 1: actual time=0.016..197.732 rows=352733 loops=1
  • Buffers: shared hit=5141
  • Worker 2: actual time=0.017..191.495 rows=498403 loops=1
  • Buffers: shared hit=7242
  • Worker 3: actual time=0.016..201.951 rows=261393 loops=1
  • Buffers: shared hit=3797
16. 5.002 5.002 ↑ 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.002..0.002 rows=1 loops=2,501)

  • Output: loan0_.loanid, loan0_.applicationcreditdecisionid
  • Index Cond: (loan0_.loanid = loanbalanc9_.loanid)
  • Buffers: shared hit=7522
17. 4.655 7.503 ↑ 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.003..0.003 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
18.          

SubPlan (for Index Scan)

19. 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
20. 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
21. 0.002 0.038 ↑ 1.2 10 1

Hash (cost=5.59..5.59 rows=12 width=4) (actual time=0.038..0.038 rows=10 loops=1)

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

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