explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GWro

Settings
# exclusive inclusive rows x rows loops node
1. 201.952 4,925.111 ↓ 87.0 87 1

Nested Loop Left Join (cost=48,697.74..288,621.31 rows=1 width=4) (actual time=3,446.107..4,925.111 rows=87 loops=1)

  • Output: application.applicationid
  • Filter: ((loadedapp.loadedapplicationid IS NULL) OR ((NOT loadedapp.appterminalstatus) AND (loadedapp.applicationstatusdate <> ash.created) AND (loadedapp.loadeddatetime = (SubPlan 4))))
  • Rows Removed by Filter: 1288023
  • Buffers: shared hit=5925557 read=51073 dirtied=40 written=324, temp read=3700 written=3698
  • I/O Timings: read=584.638 write=3.534
2. 128.706 3,169.706 ↓ 194,172.0 194,172 1

Nested Loop (cost=48,697.31..282,955.89 rows=1 width=12) (actual time=483.883..3,169.706 rows=194,172 loops=1)

  • Output: application.applicationid, ash.created
  • Join Filter: (application.applicationid = acd.applicationid)
  • Buffers: shared hit=4091029 read=8068 written=38, temp read=3700 written=3698
  • I/O Timings: read=65.953 write=0.373
3. 1,212.383 2,652.658 ↓ 194,171.0 194,171 1

Hash Join (cost=48,696.89..282,955.41 rows=1 width=16) (actual time=483.789..2,652.658 rows=194,171 loops=1)

  • Output: application.applicationid, ash.applicationid, ash.created
  • Hash Cond: ((application.applicationid = ash.applicationid) AND ((SubPlan 3) = ash.applicationstatushistoryid))
  • Buffers: shared hit=3274002 read=1984 written=3, temp read=3700 written=3698
  • I/O Timings: read=16.667 write=0.029
4. 92.612 97.182 ↓ 2.0 239,808 1

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

  • Output: application.applicationid
  • Filter: (NOT (hashed SubPlan 5))
  • Rows Removed by Filter: 18
  • Heap Fetches: 88570
  • Buffers: shared hit=70951 read=1984 written=3
  • I/O Timings: read=16.667 write=0.029
5.          

SubPlan (for Index Only Scan)

6. 0.007 4.570 ↓ 10.5 21 1

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

  • Output: a.applicationid
  • Buffers: shared hit=115 read=35
  • I/O Timings: read=3.849
7. 0.008 3.538 ↓ 2.5 25 1

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

  • Output: p2.personid
  • Buffers: shared hit=37 read=17
  • I/O Timings: read=3.137
8. 0.070 0.070 ↓ 1.4 10 1

Seq Scan on public.prohibition p (cost=0.00..7.36 rows=7 width=4) (actual time=0.026..0.070 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
9. 3.460 3.460 ↓ 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.306..0.346 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=34 read=17
  • I/O Timings: read=3.137
10. 1.025 1.025 ↑ 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.037..0.041 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=78 read=18
  • I/O Timings: read=0.712
11. 291.249 475.135 ↓ 1.0 1,268,495 1

Hash (cost=22,205.58..22,205.58 rows=1,268,458 width=20) (actual time=475.135..475.135 rows=1,268,495 loops=1)

  • Output: ash.applicationid, ash.applicationstatushistoryid, ash.created
  • Buckets: 1048576 Batches: 2 Memory Usage: 42817kB
  • Buffers: shared hit=9521, temp written=3412
12. 183.886 183.886 ↓ 1.0 1,268,495 1

Seq Scan on public.applicationstatushistory ash (cost=0.00..22,205.58 rows=1,268,458 width=20) (actual time=0.013..183.886 rows=1,268,495 loops=1)

  • Output: ash.applicationid, ash.applicationstatushistoryid, ash.created
  • Buffers: shared hit=9521
13.          

SubPlan (for Hash Join)

14. 0.000 867.958 ↑ 1.0 1 433,979

Index Scan using applicationstatushistory_applicationid_created_idx on public.applicationstatushistory ash2 (cost=1.41..3.64 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=433,979)

  • Output: ash2.applicationstatushistoryid
  • Index Cond: ((ash2.applicationid = application.applicationid) AND (ash2.created = $2))
  • Filter: (ash2.applicationstatus = ANY ('{6,11,17,16}'::integer[]))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=1739258
15.          

Initplan (for Index Scan)

16. 0.000 867.958 ↑ 1.0 1 433,979

Result (cost=0.98..0.99 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=433,979)

  • Output: $1
  • Buffers: shared hit=1454269
17.          

Initplan (for Result)

18. 433.979 867.958 ↑ 1.0 1 433,979

Limit (cost=0.43..0.98 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=433,979)

  • Output: ashmax.created
  • Buffers: shared hit=1454269
19. 433.979 433.979 ↑ 6.0 1 433,979

Index Only Scan using applicationstatushistory_applicationid_created_idx on public.applicationstatushistory ashmax (cost=0.43..3.72 rows=6 width=8) (actual time=0.001..0.001 rows=1 loops=433,979)

  • Output: ashmax.created
  • Index Cond: ((ashmax.applicationid = application.applicationid) AND (ashmax.created IS NOT NULL))
  • Heap Fetches: 150694
  • Buffers: shared hit=1454269
20. 388.342 388.342 ↑ 1.0 1 194,171

Index Scan using applicationcreditdecision_applicationid_idx on public.applicationcreditdecision acd (cost=0.42..0.47 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=194,171)

  • 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
  • Index Cond: (acd.applicationid = ash.applicationid)
  • Filter: (acd.status = 0)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=817027 read=6084 written=35
  • I/O Timings: read=49.286 write=0.344
21. 1,553.376 1,553.376 ↑ 10.1 7 194,172

Index Scan using loadedapplication_applicationid_index on public.loadedapplication loadedapp (cost=0.43..2.12 rows=71 width=25) (actual time=0.003..0.008 rows=7 loops=194,172)

  • Output: loadedapp.loadedapplicationid, loadedapp.applicationstatusdate, loadedapp.loanclosed, loadedapp.loanissued, loadedapp.appterminalstatus, loadedapp.loadeddatetime, loadedapp.externalid, loadedapp.exportsystemtype, loadedapp.applicationid
  • Index Cond: (application.applicationid = loadedapp.applicationid)
  • Buffers: shared hit=1834473 read=43005 dirtied=40 written=286
  • I/O Timings: read=518.685 write=3.161
22.          

SubPlan (for Nested Loop Left Join)

23. 0.055 0.077 ↑ 1.0 1 11

Aggregate (cost=79.74..79.75 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=11)

  • Output: max(subla.loadeddatetime)
  • Buffers: shared hit=55
24. 0.022 0.022 ↑ 35.5 2 11

Index Scan using loadedapplication_applicationid_index on public.loadedapplication subla (cost=0.43..79.56 rows=71 width=8) (actual time=0.001..0.002 rows=2 loops=11)

  • 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=55