explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3OaQ

Settings
# exclusive inclusive rows x rows loops node
1. 0.136 1,565.987 ↑ 1.0 1 1

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

  • Output: count(loan0_.loanid)
  • Buffers: shared hit=192711 read=60
  • I/O Timings: read=10.244
2. 0.255 1,565.851 ↓ 98.0 1,372 1

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

  • Output: loan0_.loanid
  • Hash Cond: (loan0_.loanid = prohibitio6_.loan_id)
  • Buffers: shared hit=192711 read=60
  • I/O Timings: read=10.244
3. 1.499 1,565.551 ↓ 98.1 1,373 1

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

  • Output: loan0_.loanid
  • Buffers: shared hit=192708 read=60
  • I/O Timings: read=10.244
4. 1.682 1,556.549 ↓ 44.7 2,501 1

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

  • Output: loan0_.loanid, loanstatus5_.loanid, loanbalanc9_.loanid
  • Join Filter: (loanstatus5_.loanid = loan0_.loanid)
  • Buffers: shared hit=180315 read=60
  • I/O Timings: read=10.244
5. 1.520 1,549.865 ↓ 44.7 2,501 1

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

  • Output: loanstatus5_.loanid, loanbalanc9_.loanid
  • Hash Cond: (loanstatus5_.loanid = loanbalanc9_.loanid)
  • Buffers: shared hit=172793 read=60
  • I/O Timings: read=10.244
6. 9.289 86.661 ↓ 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.047..86.661 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. 19.343 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. 58.029 58.029 ↑ 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.003 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. 6.730 1,461.684 ↑ 2.5 10,606 1

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

  • Output: loanbalanc9_.loanid
  • Buckets: 32768 Batches: 1 Memory Usage: 629kB
  • Buffers: shared hit=30923 read=60
  • I/O Timings: read=10.244
11. 1.321 1,454.954 ↑ 2.5 10,606 1

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

  • Output: loanbalanc9_.loanid
  • Buffers: shared hit=30923 read=60
  • I/O Timings: read=10.244
12. 408.222 1,453.633 ↑ 2.5 10,606 1

Finalize HashAggregate (cost=60,171.00..60,840.30 rows=26,772 width=300) (actual time=1,412.348..1,453.633 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=30923 read=60
  • I/O Timings: read=10.244
13. 135.810 1,045.411 ↓ 2.2 231,402 1

Gather (cost=45,379.46..56,690.64 rows=107,088 width=164) (actual time=850.799..1,045.411 rows=231,402 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=30923 read=60
  • I/O Timings: read=10.244
14. 712.077 909.601 ↓ 1.7 46,280 5 / 5

Partial HashAggregate (cost=44,379.46..44,981.83 rows=26,772 width=164) (actual time=806.477..909.601 rows=46,280 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=30923 read=60
  • I/O Timings: read=10.244
  • Worker 0: actual time=787.884..882.107 rows=43985 loops=1
  • Buffers: shared hit=5121 read=13
  • I/O Timings: read=0.116
  • Worker 1: actual time=797.638..891.085 rows=41739 loops=1
  • Buffers: shared hit=3568 read=12
  • I/O Timings: read=0.533
  • Worker 2: actual time=799.702..906.912 rows=47750 loops=1
  • Buffers: shared hit=6963 read=10
  • I/O Timings: read=7.837
  • Worker 3: actual time=797.902..909.498 rows=48794 loops=1
  • Buffers: shared hit=7433 read=16
  • I/O Timings: read=1.115
15. 197.524 197.524 ↑ 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.019..197.524 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=30923 read=60
  • I/O Timings: read=10.244
  • Worker 0: actual time=0.031..176.844 rows=353914 loops=1
  • Buffers: shared hit=5121 read=13
  • I/O Timings: read=0.116
  • Worker 1: actual time=0.018..261.628 rows=245941 loops=1
  • Buffers: shared hit=3568 read=12
  • I/O Timings: read=0.533
  • Worker 2: actual time=0.022..172.642 rows=478392 loops=1
  • Buffers: shared hit=6963 read=10
  • I/O Timings: read=7.837
  • Worker 3: actual time=0.017..176.571 rows=512281 loops=1
  • Buffers: shared hit=7433 read=16
  • I/O Timings: read=1.115
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.009 0.045 ↑ 1.2 10 1

Hash (cost=5.59..5.59 rows=12 width=4) (actual time=0.045..0.045 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.016..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