explain.depesz.com

PostgreSQL's explain analyze made readable

Result: whm6

Settings
# exclusive inclusive rows x rows loops node
1. 1,221.172 109,130.389 ↓ 1.8 11,563 1

Merge Left Join (cost=19,193.77..12,407,436.59 rows=6,377 width=4) (actual time=175.153..109,130.389 rows=11,563 loops=1)

  • Output: application.applicationid
  • Merge Cond: (application.applicationid = loadedapp.applicationid)
  • Join Filter: (loadedapp.loadeddatetime = (SubPlan 1))
  • Rows Removed by Join Filter: 1959861
  • Filter: ((NOT loadedapp.loanclosed) OR (loadedapp.loadedapplicationid IS NULL))
  • Rows Removed by Filter: 43558
  • Buffers: shared hit=238524898 read=3108
  • I/O Timings: read=40.487
2. 36.695 325.793 ↓ 3.3 55,121 1

Merge Join (cost=19,193.34..26,701.53 rows=16,798 width=4) (actual time=174.062..325.793 rows=55,121 loops=1)

  • Output: application.applicationid
  • Merge Cond: (acd.applicationid = application.applicationid)
  • Buffers: shared hit=81450 read=96
  • I/O Timings: read=3.894
3. 30.627 193.634 ↓ 1.6 55,133 1

Sort (cost=19,161.30..19,245.29 rows=33,596 width=4) (actual time=173.788..193.634 rows=55,133 loops=1)

  • Output: acd.applicationid
  • Sort Key: acd.applicationid
  • Sort Method: quicksort Memory: 4121kB
  • Buffers: shared hit=8515 read=96
  • I/O Timings: read=3.894
4. 9.919 163.007 ↓ 1.6 55,133 1

Hash Anti Join (cost=3,379.55..16,635.56 rows=33,596 width=4) (actual time=28.552..163.007 rows=55,133 loops=1)

  • Output: acd.applicationid
  • Hash Cond: (l.loanid = lsh.loanid)
  • Buffers: shared hit=8511 read=96
  • I/O Timings: read=3.894
5. 47.712 147.665 ↓ 1.6 56,464 1

Hash Join (cost=2,283.42..15,060.82 rows=34,418 width=8) (actual time=23.115..147.665 rows=56,464 loops=1)

  • Output: acd.applicationid, l.loanid
  • Hash Cond: (acd.applicationcreditdecisionid = l.applicationcreditdecisionid)
  • Buffers: shared hit=7560 read=91
  • I/O Timings: read=2.007
6. 76.929 76.929 ↓ 1.0 239,262 1

Seq Scan on public.applicationcreditdecision acd (cost=0.00..11,537.29 rows=238,915 width=8) (actual time=0.006..76.929 rows=239,262 loops=1)

  • Output: acd.applicationcreditdecisionid, acd.created, acd.updated, acd.decidedat, acd.decision, acd.expireddate, acd."limit", acd.maturity, acd.payouttype, acd.percent, acd.psk, acd.pskrate, acd.source, acd.status, acd.applicationid, acd.declinereasonid, acd.frontuserid, acd.insurance_allowed, acd.client_agrees_insurance, acd.pdn_value, acd.pdn_category
  • Filter: (acd.status = 0)
  • Rows Removed by Filter: 152693
  • Buffers: shared hit=6625 read=13
  • I/O Timings: read=1.481
7. 7.510 23.024 ↓ 1.0 56,464 1

Hash (cost=1,577.63..1,577.63 rows=56,463 width=8) (actual time=23.024..23.024 rows=56,464 loops=1)

  • Output: l.applicationcreditdecisionid, l.loanid
  • Buckets: 65536 Batches: 1 Memory Usage: 2718kB
  • Buffers: shared hit=935 read=78
  • I/O Timings: read=0.526
8. 15.514 15.514 ↓ 1.0 56,464 1

Seq Scan on public.loan l (cost=0.00..1,577.63 rows=56,463 width=8) (actual time=0.012..15.514 rows=56,464 loops=1)

  • Output: l.applicationcreditdecisionid, l.loanid
  • Buffers: shared hit=935 read=78
  • I/O Timings: read=0.526
9. 0.180 5.423 ↑ 1.0 1,331 1

Hash (cost=1,079.28..1,079.28 rows=1,348 width=4) (actual time=5.423..5.423 rows=1,331 loops=1)

  • Output: lsh.loanid
  • Buckets: 2048 Batches: 1 Memory Usage: 63kB
  • Buffers: shared hit=951 read=5
  • I/O Timings: read=1.887
10. 5.243 5.243 ↑ 1.0 1,331 1

Index Scan using loanstatushistory_loanstatus_idx on public.loanstatushistory lsh (cost=0.42..1,079.28 rows=1,348 width=4) (actual time=0.454..5.243 rows=1,331 loops=1)

  • Output: lsh.loanid
  • Index Cond: (lsh.loanstatus = 9)
  • Filter: lsh.isactive
  • Buffers: shared hit=951 read=5
  • I/O Timings: read=1.887
11. 95.234 95.464 ↓ 2.0 239,804 1

Index Only Scan using application_pkey on public.application (cost=31.44..6,904.51 rows=119,909 width=4) (actual time=0.269..95.464 rows=239,804 loops=1)

  • Output: application.applicationid
  • Filter: (NOT (hashed SubPlan 2))
  • Rows Removed by Filter: 18
  • Heap Fetches: 88566
  • Buffers: shared hit=72935
12.          

SubPlan (for Index Only Scan)

13. 0.014 0.230 ↓ 10.5 21 1

Nested Loop (cost=0.85..31.01 rows=2 width=4) (actual time=0.053..0.230 rows=21 loops=1)

  • Output: a.applicationid
  • Buffers: shared hit=150
14. 0.008 0.141 ↓ 2.5 25 1

Nested Loop (cost=0.43..25.95 rows=10 width=4) (actual time=0.043..0.141 rows=25 loops=1)

  • Output: p2.personid
  • Buffers: shared hit=54
15. 0.053 0.053 ↓ 1.4 10 1

Seq Scan on public.prohibition p (cost=0.00..7.36 rows=7 width=4) (actual time=0.028..0.053 rows=10 loops=1)

  • Output: p.id, p.type, p.is_active, p.start_date, p.end_date, p.client_id, p.loan_id, p.front_user_id, p.comment_id, p.created, p.updated, p.cancel_front_user_id, p.cancel_date, p.cancel_comment_id
  • Filter: (p.is_active AND (p.type = 9) AND ((p.end_date IS NULL) OR (p.end_date > ('now'::cstring)::date)))
  • Rows Removed by Filter: 208
  • Buffers: shared hit=3
16. 0.080 0.080 ↓ 2.0 2 10

Index Scan using person_clientid_created_idx on public.person p2 (cost=0.43..2.65 rows=1 width=8) (actual time=0.005..0.008 rows=2 loops=10)

  • Output: p2.personid, p2.created, p2.updated, p2.inn, p2.snils, p2.bankrupt, p2.birthdate, p2.childrennumber, p2.childrennumberlt21, p2.citizenship, p2.court, p2.dependecesnumber, p2.educationtype, p2.gender, p2.maritalstatus, p2.name, p2.patronymic, p2.surname, p2.addresslivingid, p2.addressregistrationid, p2.clientid, p2.personemailid
  • Index Cond: (p2.clientid = p.client_id)
  • Buffers: shared hit=51
17. 0.075 0.075 ↑ 3.0 1 25

Index Scan using application_personid_idx on public.application a (cost=0.42..0.48 rows=3 width=8) (actual time=0.003..0.003 rows=1 loops=25)

  • Output: a.applicationid, a.created, a.updated, a.applicationdate, a.applicationnumber, a.consentdate, a.consentexpiredate, a.consentflag, a.consentpropose, a.consentproposenote, a.currency, a.finallimit, a.finalmaturity, a.finalrate, a.initiallimit, a.initialmaturity, a.initialrate, a.ip, a.methodcredit, a.payouttype, a.pendingtime, a.purpose, a.paymentinstrumentid, a.leadinfoid, a.personid, a.productid, a.promocodeid, a.fingerprint, a.clienttype, a.leadclickid, a.loadedapplicationid, a.insurance_allowed, a.client_agrees_insurance, a.restrict_cession
  • Index Cond: (a.personid = p2.personid)
  • Buffers: shared hit=96
18. 790.279 790.279 ↑ 1.0 2,198,991 1

Index Scan using loadedapplication_applicationid_index on public.loadedapplication loadedapp (cost=0.43..86,033.89 rows=2,199,622 width=17) (actual time=0.011..790.279 rows=2,198,991 loops=1)

  • Output: loadedapp.loadedapplicationid, loadedapp.applicationstatusdate, loadedapp.loanclosed, loadedapp.loanissued, loadedapp.appterminalstatus, loadedapp.loadeddatetime, loadedapp.externalid, loadedapp.exportsystemtype, loadedapp.applicationid
  • Buffers: shared hit=2019306 read=1
  • I/O Timings: read=0.016
19.          

SubPlan (for Merge Left Join)

20. 24,179.580 106,793.145 ↑ 1.0 1 2,014,965

Aggregate (cost=79.74..79.75 rows=1 width=8) (actual time=0.053..0.053 rows=1 loops=2,014,965)

  • Output: max(subla.loadeddatetime)
  • Buffers: shared hit=236424142 read=3011
  • I/O Timings: read=36.577
21. 82,613.565 82,613.565 ↓ 1.6 115 2,014,965

Index Scan using loadedapplication_applicationid_index on public.loadedapplication subla (cost=0.43..79.56 rows=71 width=8) (actual time=0.003..0.041 rows=115 loops=2,014,965)

  • Output: subla.loadedapplicationid, subla.applicationstatusdate, subla.loanclosed, subla.loanissued, subla.appterminalstatus, subla.loadeddatetime, subla.externalid, subla.exportsystemtype, subla.applicationid
  • Index Cond: (subla.applicationid = application.applicationid)
  • Buffers: shared hit=236424142 read=3011
  • I/O Timings: read=36.577