explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DlM

Settings
# exclusive inclusive rows x rows loops node
1. 60.385 41,450.184 ↓ 2.1 12,322 1

HashAggregate (cost=877,787.72..877,847.35 rows=5,963 width=4) (actual time=41,426.700..41,450.184 rows=12,322 loops=1)

  • Output: application.applicationid
  • Group Key: application.applicationid
  • Buffers: shared hit=6447967 read=17332, temp read=3616 written=3614
  • I/O Timings: read=960.841
2. 36.205 41,389.799 ↓ 2.1 12,560 1

Append (cost=47,443.86..877,772.81 rows=5,963 width=4) (actual time=25,592.269..41,389.799 rows=12,560 loops=1)

  • Buffers: shared hit=6447967 read=17332, temp read=3616 written=3614
  • I/O Timings: read=960.841
3. 3,217.973 36,012.369 ↓ 1,039.0 1,039 1

Nested Loop Left Join (cost=47,443.86..750,460.06 rows=1 width=4) (actual time=25,592.265..36,012.369 rows=1,039 loops=1)

  • Output: application.applicationid
  • Filter: ((loadedapp.loadedapplicationid IS NULL) OR ((NOT loadedapp.appterminalstatus) AND (loadedapp.applicationstatusdate <> ash.created) AND (loadedapp.loadeddatetime = (SubPlan 8))))
  • Rows Removed by Filter: 1238738
  • Buffers: shared hit=6013207 read=9232, temp read=3616 written=3614
  • I/O Timings: read=806.423
4. 1,471.629 27,401.086 ↓ 189,715.0 189,715 1

Nested Loop (cost=47,443.43..750,146.15 rows=1 width=12) (actual time=5,318.855..27,401.086 rows=189,715 loops=1)

  • Output: application.applicationid, ash.created
  • Join Filter: (application.applicationid = acd.applicationid)
  • Buffers: shared hit=4207497 read=3390, temp read=3616 written=3614
  • I/O Timings: read=589.252
5. 14,050.509 24,222.040 ↓ 189,713.0 189,713 1

Hash Join (cost=47,443.00..750,145.65 rows=1 width=16) (actual time=5,318.826..24,222.040 rows=189,713 loops=1)

  • Output: application.applicationid, ash.applicationid, ash.created
  • Hash Cond: ((application.applicationid = ash.applicationid) AND ((SubPlan 6) = ash.applicationstatushistoryid))
  • Buffers: shared hit=3407005 read=3390, temp read=3616 written=3614
  • I/O Timings: read=589.252
6. 642.133 643.597 ↓ 2.0 234,344 1

Seq Scan on public.application (cost=80.53..7,606.17 rows=117,186 width=4) (actual time=1.613..643.597 rows=234,344 loops=1)

  • Output: application.applicationid
  • Filter: (NOT (hashed SubPlan 9))
  • Rows Removed by Filter: 27
  • Buffers: shared hit=4771
7.          

SubPlan (for Seq Scan)

8. 0.212 1.464 ↓ 15.0 30 1

Nested Loop (cost=0.85..80.52 rows=2 width=4) (actual time=0.155..1.464 rows=30 loops=1)

  • Output: a_1.applicationid
  • Buffers: shared hit=175
9. 0.135 0.720 ↓ 2.5 28 1

Nested Loop (cost=0.43..74.84 rows=11 width=4) (actual time=0.090..0.720 rows=28 loops=1)

  • Output: p2_1.personid
  • Buffers: shared hit=61
10. 0.112 0.112 ↓ 1.4 11 1

Seq Scan on public.prohibition p_1 (cost=0.00..7.20 rows=8 width=4) (actual time=0.036..0.112 rows=11 loops=1)

  • Output: p_1.id, p_1.type, p_1.is_active, p_1.start_date, p_1.end_date, p_1.client_id, p_1.loan_id, p_1.front_user_id, p_1.comment_id, p_1.created, p_1.updated, p_1.cancel_front_user_id, p_1.cancel_date, p_1.cancel_comment_id
  • Filter: (p_1.is_active AND (p_1.type = 9) AND ((p_1.end_date IS NULL) OR (p_1.end_date > ('now'::cstring)::date)))
  • Rows Removed by Filter: 199
  • Buffers: shared hit=3
11. 0.473 0.473 ↓ 3.0 3 11

Index Scan using person_clientid_idx on public.person p2_1 (cost=0.43..8.45 rows=1 width=8) (actual time=0.027..0.043 rows=3 loops=11)

  • Output: p2_1.personid, p2_1.created, p2_1.updated, p2_1.inn, p2_1.snils, p2_1.bankrupt, p2_1.birthdate, p2_1.childrennumber, p2_1.childrennumberlt21, p2_1.citizenship, p2_1.court, p2_1.dependecesnumber, p2_1.educationtype, p2_1.gender, p2_1.maritalstatus, p2_1.name, p2_1.patronymic, p2_1.surname, p2_1.addresslivingid, p2_1.addressregistrationid, p2_1.clientid, p2_1.personemailid
  • Index Cond: (p2_1.clientid = p_1.client_id)
  • Buffers: shared hit=58
12. 0.532 0.532 ↑ 3.0 1 28

Index Scan using application_personid_idx on public.application a_1 (cost=0.42..0.49 rows=3 width=8) (actual time=0.013..0.019 rows=1 loops=28)

  • Output: a_1.applicationid, a_1.created, a_1.updated, a_1.applicationdate, a_1.applicationnumber, a_1.consentdate, a_1.consentexpiredate, a_1.consentflag, a_1.consentpropose, a_1.consentproposenote, a_1.currency, a_1.finallimit, a_1.finalmaturity, a_1.finalrate, a_1.initiallimit, a_1.initialmaturity, a_1.initialrate, a_1.ip, a_1.methodcredit, a_1.payouttype, a_1.pendingtime, a_1.purpose, a_1.paymentinstrumentid, a_1.leadinfoid, a_1.personid, a_1.productid, a_1.promocodeid, a_1.fingerprint, a_1.clienttype, a_1.leadclickid, a_1.loadedapplicationid, a_1.insurance_allowed, a_1.client_agrees_insurance, a_1.restrict_cession
  • Index Cond: (a_1.personid = p2_1.personid)
  • Buffers: shared hit=114
13. 2,789.597 5,287.364 ↑ 1.0 1,239,379 1

Hash (cost=21,509.79..21,509.79 rows=1,239,379 width=20) (actual time=5,287.363..5,287.364 rows=1,239,379 loops=1)

  • Output: ash.applicationid, ash.applicationstatushistoryid, ash.created
  • Buckets: 1048576 Batches: 2 Memory Usage: 42019kB
  • Buffers: shared hit=9116, temp written=3334
14. 2,497.767 2,497.767 ↑ 1.0 1,239,379 1

Seq Scan on public.applicationstatushistory ash (cost=0.00..21,509.79 rows=1,239,379 width=20) (actual time=0.027..2,497.767 rows=1,239,379 loops=1)

  • Output: ash.applicationid, ash.applicationstatushistoryid, ash.created
  • Buffers: shared hit=9116
15.          

SubPlan (for Hash Join)

16. 0.000 4,240.570 ↑ 1.0 1 424,057

Index Scan using applicationstatushistory_created_idx on public.applicationstatushistory ash2 (cost=3.68..11.71 rows=1 width=8) (actual time=0.008..0.010 rows=1 loops=424,057)

  • Output: ash2.applicationstatushistoryid
  • Index Cond: (ash2.created = $7)
  • Filter: ((ash2.applicationid = application.applicationid) AND (ash2.applicationstatus = ANY ('{6,11,17,16}'::integer[])))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=1695261 read=3390
  • I/O Timings: read=589.252
17.          

Initplan (for Index Scan)

18. 2,968.399 9,329.254 ↑ 1.0 1 424,057

Result (cost=3.25..3.26 rows=1 width=8) (actual time=0.021..0.022 rows=1 loops=424,057)

  • Output: $6
  • Buffers: shared hit=1697854
19.          

Initplan (for Result)

20. 2,968.399 6,360.855 ↑ 1.0 1 424,057

Limit (cost=0.43..3.25 rows=1 width=8) (actual time=0.011..0.015 rows=1 loops=424,057)

  • Output: ashmax.created
  • Buffers: shared hit=1697854
21. 3,392.456 3,392.456 ↑ 6.0 1 424,057

Index Only Scan using applicationstatushistory_applicationid_created_idx on public.applicationstatushistory ashmax (cost=0.43..17.35 rows=6 width=8) (actual time=0.007..0.008 rows=1 loops=424,057)

  • Output: ashmax.created
  • Index Cond: ((ashmax.applicationid = application.applicationid) AND (ashmax.created IS NOT NULL))
  • Heap Fetches: 424057
  • Buffers: shared hit=1697854
22. 1,707.417 1,707.417 ↑ 1.0 1 189,713

Index Scan using applicationcreditdecision_applicationid_idx on public.applicationcreditdecision acd (cost=0.42..0.48 rows=1 width=4) (actual time=0.007..0.009 rows=1 loops=189,713)

  • 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=800492
23. 5,312.020 5,312.020 ↑ 9.9 7 189,715

Index Scan using loadedapplication_applicationid_index on public.loadedapplication loadedapp (cost=0.43..2.80 rows=69 width=25) (actual time=0.007..0.028 rows=7 loops=189,715)

  • 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=1805681 read=5827
  • I/O Timings: read=136.394
24.          

SubPlan (for Nested Loop Left Join)

25. 0.099 81.290 ↑ 1.0 1 11

Result (cost=4.48..4.49 rows=1 width=8) (actual time=7.388..7.390 rows=1 loops=11)

  • Output: $9
  • Buffers: shared hit=29 read=15
  • I/O Timings: read=80.777
26.          

Initplan (for Result)

27. 0.110 81.191 ↑ 1.0 1 11

Limit (cost=0.43..4.48 rows=1 width=8) (actual time=7.376..7.381 rows=1 loops=11)

  • Output: subla_1.loadeddatetime
  • Buffers: shared hit=29 read=15
  • I/O Timings: read=80.777
28. 81.081 81.081 ↑ 69.0 1 11

Index Only Scan using loadedapplication_applicationid_loadeddatetime_idx on public.loadedapplication subla_1 (cost=0.43..280.15 rows=69 width=8) (actual time=7.369..7.371 rows=1 loops=11)

  • Output: subla_1.loadeddatetime
  • Index Cond: ((subla_1.applicationid = application.applicationid) AND (subla_1.loadeddatetime IS NOT NULL))
  • Heap Fetches: 11
  • Buffers: shared hit=29 read=15
  • I/O Timings: read=80.777
29. 1,972.990 5,341.225 ↓ 1.9 11,521 1

Nested Loop Left Join (cost=13,001.85..127,253.12 rows=5,962 width=4) (actual time=1,327.164..5,341.225 rows=11,521 loops=1)

  • Output: application_1.applicationid
  • Filter: ((NOT loadedapp_1.loanclosed) OR (loadedapp_1.loadedapplicationid IS NULL))
  • Rows Removed by Filter: 42374
  • Buffers: shared hit=434760 read=8100
  • I/O Timings: read=154.418
30. 195.880 2,937.075 ↓ 3.3 53,895 1

Hash Anti Join (cost=12,996.93..25,369.16 rows=16,441 width=4) (actual time=1,326.278..2,937.075 rows=53,895 loops=1)

  • Output: application_1.applicationid
  • Hash Cond: (l.loanid = lsh.loanid)
  • Buffers: shared hit=12388 read=3
  • I/O Timings: read=5.561
31. 235.571 2,723.135 ↓ 3.3 55,200 1

Hash Join (cost=11,256.49..23,402.92 rows=16,815 width=8) (actual time=1,308.192..2,723.135 rows=55,200 loops=1)

  • Output: application_1.applicationid, l.loanid
  • Hash Cond: (acd_1.applicationid = application_1.applicationid)
  • Buffers: shared hit=11573
32. 599.317 1,415.422 ↓ 1.6 55,222 1

Hash Join (cost=2,185.49..14,037.66 rows=33,630 width=8) (actual time=235.163..1,415.422 rows=55,222 loops=1)

  • Output: acd_1.applicationid, l.loanid
  • Hash Cond: (acd_1.applicationcreditdecisionid = l.applicationcreditdecisionid)
  • Buffers: shared hit=6802
33. 581.504 581.504 ↓ 1.0 233,811 1

Seq Scan on public.applicationcreditdecision acd_1 (cost=0.00..10,642.01 rows=233,028 width=8) (actual time=0.021..581.504 rows=233,811 loops=1)

  • Output: acd_1.applicationcreditdecisionid, acd_1.created, acd_1.updated, acd_1.decidedat, acd_1.decision, acd_1.expireddate, acd_1."limit", acd_1.maturity, acd_1.payouttype, acd_1.percent, acd_1.psk, acd_1.pskrate, acd_1.source, acd_1.status, acd_1.applicationid, acd_1.declinereasonid, acd_1.frontuserid, acd_1.insurance_allowed, acd_1.client_agrees_insurance, acd_1.pdn_value, acd_1.pdn_category
  • Filter: (acd_1.status = 0)
  • Rows Removed by Filter: 148830
  • Buffers: shared hit=5859
34. 118.837 234.601 ↑ 1.0 55,222 1

Hash (cost=1,495.22..1,495.22 rows=55,222 width=8) (actual time=234.600..234.601 rows=55,222 loops=1)

  • Output: l.applicationcreditdecisionid, l.loanid
  • Buckets: 65536 Batches: 1 Memory Usage: 2670kB
  • Buffers: shared hit=943
35. 115.764 115.764 ↑ 1.0 55,222 1

Seq Scan on public.loan l (cost=0.00..1,495.22 rows=55,222 width=8) (actual time=0.026..115.764 rows=55,222 loops=1)

  • Output: l.applicationcreditdecisionid, l.loanid
  • Buffers: shared hit=943
36. 529.415 1,072.142 ↓ 2.0 234,344 1

Hash (cost=7,606.17..7,606.17 rows=117,186 width=4) (actual time=1,072.140..1,072.142 rows=234,344 loops=1)

  • Output: application_1.applicationid
  • Buckets: 262144 (originally 131072) Batches: 1 (originally 1) Memory Usage: 10287kB
  • Buffers: shared hit=4771
37. 541.779 542.727 ↓ 2.0 234,344 1

Seq Scan on public.application application_1 (cost=80.53..7,606.17 rows=117,186 width=4) (actual time=1.088..542.727 rows=234,344 loops=1)

  • Output: application_1.applicationid
  • Filter: (NOT (hashed SubPlan 3))
  • Rows Removed by Filter: 27
  • Buffers: shared hit=4771
38.          

SubPlan (for Seq Scan)

39. 0.216 0.948 ↓ 15.0 30 1

Nested Loop (cost=0.85..80.52 rows=2 width=4) (actual time=0.103..0.948 rows=30 loops=1)

  • Output: a.applicationid
  • Buffers: shared hit=175
40. 0.139 0.452 ↓ 2.5 28 1

Nested Loop (cost=0.43..74.84 rows=11 width=4) (actual time=0.064..0.452 rows=28 loops=1)

  • Output: p2.personid
  • Buffers: shared hit=61
41. 0.104 0.104 ↓ 1.4 11 1

Seq Scan on public.prohibition p (cost=0.00..7.20 rows=8 width=4) (actual time=0.035..0.104 rows=11 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: 199
  • Buffers: shared hit=3
42. 0.209 0.209 ↓ 3.0 3 11

Index Scan using person_clientid_idx on public.person p2 (cost=0.43..8.45 rows=1 width=8) (actual time=0.011..0.019 rows=3 loops=11)

  • 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=58
43. 0.280 0.280 ↑ 3.0 1 28

Index Scan using application_personid_idx on public.application a (cost=0.42..0.49 rows=3 width=8) (actual time=0.007..0.010 rows=1 loops=28)

  • 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=114
44. 2.999 18.060 ↓ 1.1 1,306 1

Hash (cost=1,725.08..1,725.08 rows=1,229 width=4) (actual time=18.059..18.060 rows=1,306 loops=1)

  • Output: lsh.loanid
  • Buckets: 2048 Batches: 1 Memory Usage: 62kB
  • Buffers: shared hit=815 read=3
  • I/O Timings: read=5.561
45. 9.240 15.061 ↓ 1.1 1,306 1

Bitmap Heap Scan on public.loanstatushistory lsh (cost=26.11..1,725.08 rows=1,229 width=4) (actual time=5.981..15.061 rows=1,306 loops=1)

  • Output: lsh.loanid
  • Recheck Cond: (lsh.loanstatus = 9)
  • Filter: lsh.isactive
  • Heap Blocks: exact=812
  • Buffers: shared hit=815 read=3
  • I/O Timings: read=5.561
46. 5.821 5.821 ↓ 1.0 1,306 1

Bitmap Index Scan on loanstatushistory_loanstatus_idx (cost=0.00..25.80 rows=1,251 width=0) (actual time=5.820..5.821 rows=1,306 loops=1)

  • Index Cond: (lsh.loanstatus = 9)
  • Buffers: shared hit=3 read=3
  • I/O Timings: read=5.561
47. 0.000 431.160 ↑ 1.0 1 53,895

Index Scan using loadedapplication_applicationid_loadeddatetime_idx on public.loadedapplication loadedapp_1 (cost=4.92..6.19 rows=1 width=17) (actual time=0.006..0.008 rows=1 loops=53,895)

  • Output: loadedapp_1.loadedapplicationid, loadedapp_1.applicationstatusdate, loadedapp_1.loanclosed, loadedapp_1.loanissued, loadedapp_1.appterminalstatus, loadedapp_1.loadeddatetime, loadedapp_1.externalid, loadedapp_1.exportsystemtype, loadedapp_1.applicationid
  • Index Cond: ((application_1.applicationid = loadedapp_1.applicationid) AND (loadedapp_1.loadeddatetime = (SubPlan 2)))
  • Buffers: shared hit=214900 read=44
  • I/O Timings: read=0.594
48.          

SubPlan (for Index Scan)

49. 377.265 1,455.165 ↑ 1.0 1 53,895

Result (cost=4.48..4.49 rows=1 width=8) (actual time=0.025..0.027 rows=1 loops=53,895)

  • Output: $1
  • Buffers: shared hit=207472 read=8053
  • I/O Timings: read=148.263
50.          

Initplan (for Result)

51. 377.265 1,077.900 ↑ 1.0 1 53,895

Limit (cost=0.43..4.48 rows=1 width=8) (actual time=0.015..0.020 rows=1 loops=53,895)

  • Output: subla.loadeddatetime
  • Buffers: shared hit=207472 read=8053
  • I/O Timings: read=148.263
52. 700.635 700.635 ↑ 69.0 1 53,895

Index Only Scan using loadedapplication_applicationid_loadeddatetime_idx on public.loadedapplication subla (cost=0.43..280.15 rows=69 width=8) (actual time=0.011..0.013 rows=1 loops=53,895)

  • Output: subla.loadeddatetime
  • Index Cond: ((subla.applicationid = application_1.applicationid) AND (subla.loadeddatetime IS NOT NULL))
  • Heap Fetches: 53635
  • Buffers: shared hit=207472 read=8053
  • I/O Timings: read=148.263