explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Dl6n

Settings
# exclusive inclusive rows x rows loops node
1. 3.567 5,730.796 ↓ 39.0 4,051 1

Sort (cost=334,363.81..334,364.07 rows=104 width=494) (actual time=5,730.143..5,730.796 rows=4,051 loops=1)

  • Output: a.applicationid, a.applicationnumber, a.consentdate, (min(ac.created)), a.initiallimit, a.initialmaturity, (max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decidedat ELSE NULL::timestamp without time zone END)), (CASE WHEN ((max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decision ELSE NULL::integer END)) = 0) THEN 'Одобрено'::text WHEN ((max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decision ELSE NULL::integer END)) = 1) THEN 'Отказано'::text WHEN ((max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decision ELSE NULL::integer END)) = 2) THEN 'В обработке'::text ELSE ((max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decision ELSE NULL::integer END)))::text END), (max(CASE WHEN (ac.status = 0) THEN ac."limit" ELSE NULL::numeric END)), (max(CASE WHEN (ac.status = 0) THEN ac.maturity ELSE NULL::integer END)), (max(CASE WHEN (ac.source = 1) THEN 1 ELSE 0 END)), (max(CASE WHEN (ac.source = 0) THEN ac.decidedat ELSE NULL::timestamp without time zone END)), p.processdate, aps.applicationstatus, (CASE WHEN ((ll.last_loan_dt IS NOT NULL) AND (sa.personid IS NOT NULL)) THEN 'new_old_repeat'::text WHEN (ll.last_loan_dt IS NOT NULL) THEN 'new_repeat'::text WHEN (sa.personid IS NOT NULL) THEN 'old_repeat'::text ELSE 'new'::text END), pr.productkey, pm.promocode, li.url, l.loannumber, aps_1.loanstatus, lo.finalrate, lo.finalamount, ((ps.min_date - (l.startdatetime)::date)), ((l.startdatetime)::date), ps.min_date, ps.max_date, pl.repayments
  • Sort Key: a.applicationid
  • Sort Method: quicksort Memory: 1,782kB
  • Buffers: shared hit=3,260,737
2. 43.194 5,727.229 ↓ 39.0 4,051 1

Hash Right Join (cost=327,204.78..334,360.33 rows=104 width=494) (actual time=5,579.014..5,727.229 rows=4,051 loops=1)

  • Output: a.applicationid, a.applicationnumber, a.consentdate, (min(ac.created)), a.initiallimit, a.initialmaturity, (max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decidedat ELSE NULL::timestamp without time zone END)), CASE WHEN ((max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decision ELSE NULL::integer END)) = 0) THEN 'Одобрено'::text WHEN ((max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decision ELSE NULL::integer END)) = 1) THEN 'Отказано'::text WHEN ((max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decision ELSE NULL::integer END)) = 2) THEN 'В обработке'::text ELSE ((max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decision ELSE NULL::integer END)))::text END, (max(CASE WHEN (ac.status = 0) THEN ac."limit" ELSE NULL::numeric END)), (max(CASE WHEN (ac.status = 0) THEN ac.maturity ELSE NULL::integer END)), (max(CASE WHEN (ac.source = 1) THEN 1 ELSE 0 END)), (max(CASE WHEN (ac.source = 0) THEN ac.decidedat ELSE NULL::timestamp without time zone END)), p.processdate, aps.applicationstatus, CASE WHEN ((ll.last_loan_dt IS NOT NULL) AND (sa.personid IS NOT NULL)) THEN 'new_old_repeat'::text WHEN (ll.last_loan_dt IS NOT NULL) THEN 'new_repeat'::text WHEN (sa.personid IS NOT NULL) THEN 'old_repeat'::text ELSE 'new'::text END, pr.productkey, pm.promocode, li.url, l.loannumber, aps_1.loanstatus, lo.finalrate, lo.finalamount, (ps.min_date - (l.startdatetime)::date), (l.startdatetime)::date, ps.min_date, ps.max_date, pl.repayments
  • Hash Cond: ((sa.personid)::text = (cl.externalid)::text)
  • Buffers: shared hit=3,260,737
3. 585.661 1,119.617 ↑ 1.1 246,758 1

HashAggregate (cost=61,000.30..63,725.35 rows=272,505 width=4) (actual time=1,014.522..1,119.617 rows=246,758 loops=1)

  • Output: sa.personid
  • Group Key: sa.personid
  • Buffers: shared hit=148,752
4. 533.956 533.956 ↓ 2.2 1,051,384 1

Index Scan using application_isresive_idx on scoring.application sa (cost=0.43..59,787.99 rows=484,925 width=4) (actual time=0.021..533.956 rows=1,051,384 loops=1)

  • Output: sa.applicationid, sa.userid, sa.personid, sa.subtask, sa.iscancelled, sa.isresive, sa.isrepay, sa.isoverdue, sa.iscollector, sa.isuncollected, sa.isnotcomplied, sa.isfraud, sa.isaborttransfertobki, sa.iscollectoraccept, sa.tmscreate, sa.tmslastupdate, sa.tmsfeedback, sa.tmsfbcancelled, sa.tmsfbresive, sa.tmsfbrepay, sa.tmsfboverdue, sa.tmsfbcollector, sa.tmsfbuncollected, sa.tmsfbnotcomplied, sa.tmsfbfraud, sa.tmsfblastpayment, sa.tmsfblastprolongate, sa.tmsofsale, sa.tmsfbcollectoraccept, sa.tmsbkinextreport, sa.tmsofaborttransfertobki, sa.tmsmissedpayout, sa.status, sa.scoring, sa.testmode, sa.description, sa.rehabilitation, sa.is_sended, sa.pd, sa.result, sa.term, sa.term_initial, sa.amount, sa.restructuring_count, sa.rate, sa.psk, sa.agreement_id, sa.is_sended_bki, sa.is_antifraud, sa.ext_id
  • Index Cond: (sa.isresive = true)
  • Filter: (sa.isresive AND ((sa.subtask)::text = 'APPLICATION'::text))
  • Buffers: shared hit=148,752
5. 3.031 4,564.418 ↓ 39.0 4,051 1

Hash (cost=266,203.18..266,203.18 rows=104 width=449) (actual time=4,564.418..4,564.418 rows=4,051 loops=1)

  • Output: a.applicationid, a.applicationnumber, a.consentdate, a.initiallimit, a.initialmaturity, cl.externalid, pr.productkey, aps.applicationstatus, (min(ac.created)), (max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decidedat ELSE NULL::timestamp without time zone END)), (max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decision ELSE NULL::integer END)), (max(CASE WHEN (ac.status = 0) THEN ac."limit" ELSE NULL::numeric END)), (max(CASE WHEN (ac.status = 0) THEN ac.maturity ELSE NULL::integer END)), (max(CASE WHEN (ac.source = 1) THEN 1 ELSE 0 END)), (max(CASE WHEN (ac.source = 0) THEN ac.decidedat ELSE NULL::timestamp without time zone END)), l.loannumber, l.startdatetime, aps_1.loanstatus, lo.finalrate, lo.finalamount, p.processdate, pm.promocode, li.url, ps.min_date, ps.max_date, ll.last_loan_dt, pl.repayments
  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,062kB
  • Buffers: shared hit=3,111,985
6. 1.215 4,561.387 ↓ 39.0 4,051 1

Nested Loop Left Join (cost=248,900.40..266,203.18 rows=104 width=449) (actual time=4,496.145..4,561.387 rows=4,051 loops=1)

  • Output: a.applicationid, a.applicationnumber, a.consentdate, a.initiallimit, a.initialmaturity, cl.externalid, pr.productkey, aps.applicationstatus, (min(ac.created)), (max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decidedat ELSE NULL::timestamp without time zone END)), (max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decision ELSE NULL::integer END)), (max(CASE WHEN (ac.status = 0) THEN ac."limit" ELSE NULL::numeric END)), (max(CASE WHEN (ac.status = 0) THEN ac.maturity ELSE NULL::integer END)), (max(CASE WHEN (ac.source = 1) THEN 1 ELSE 0 END)), (max(CASE WHEN (ac.source = 0) THEN ac.decidedat ELSE NULL::timestamp without time zone END)), l.loannumber, l.startdatetime, aps_1.loanstatus, lo.finalrate, lo.finalamount, p.processdate, pm.promocode, li.url, ps.min_date, ps.max_date, ll.last_loan_dt, pl.repayments
  • Buffers: shared hit=3,111,985
7. 1.176 4,552.070 ↓ 39.0 4,051 1

Hash Left Join (cost=248,899.97..265,940.44 rows=104 width=288) (actual time=4,496.109..4,552.070 rows=4,051 loops=1)

  • Output: a.applicationid, a.applicationnumber, a.consentdate, a.initiallimit, a.initialmaturity, a.leadclickid, cl.externalid, pr.productkey, aps.applicationstatus, (min(ac.created)), (max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decidedat ELSE NULL::timestamp without time zone END)), (max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decision ELSE NULL::integer END)), (max(CASE WHEN (ac.status = 0) THEN ac."limit" ELSE NULL::numeric END)), (max(CASE WHEN (ac.status = 0) THEN ac.maturity ELSE NULL::integer END)), (max(CASE WHEN (ac.source = 1) THEN 1 ELSE 0 END)), (max(CASE WHEN (ac.source = 0) THEN ac.decidedat ELSE NULL::timestamp without time zone END)), l.loannumber, l.startdatetime, aps_1.loanstatus, lo.finalrate, lo.finalamount, p.processdate, pm.promocode, ps.min_date, ps.max_date, ll.last_loan_dt, pl.repayments
  • Hash Cond: (l.loanid = pl.loanid)
  • Buffers: shared hit=3,101,562
8. 6.375 3,939.650 ↓ 39.0 4,051 1

Hash Left Join (cost=218,140.47..234,940.96 rows=104 width=260) (actual time=3,884.701..3,939.650 rows=4,051 loops=1)

  • Output: a.applicationid, a.applicationnumber, a.consentdate, a.initiallimit, a.initialmaturity, a.leadclickid, cl.externalid, pr.productkey, aps.applicationstatus, (min(ac.created)), (max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decidedat ELSE NULL::timestamp without time zone END)), (max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decision ELSE NULL::integer END)), (max(CASE WHEN (ac.status = 0) THEN ac."limit" ELSE NULL::numeric END)), (max(CASE WHEN (ac.status = 0) THEN ac.maturity ELSE NULL::integer END)), (max(CASE WHEN (ac.source = 1) THEN 1 ELSE 0 END)), (max(CASE WHEN (ac.source = 0) THEN ac.decidedat ELSE NULL::timestamp without time zone END)), l.loannumber, l.startdatetime, l.loanid, aps_1.loanstatus, lo.finalrate, lo.finalamount, p.processdate, pm.promocode, ps.min_date, ps.max_date, ll.last_loan_dt
  • Hash Cond: (c.clientid = ll.clientid)
  • Join Filter: (ll.last_loan_dt < a.applicationdate)
  • Rows Removed by Join Filter: 420
  • Buffers: shared hit=2,996,163
9. 1.567 3,330.419 ↓ 39.0 4,051 1

Hash Left Join (cost=174,645.66..191,228.24 rows=104 width=264) (actual time=3,281.780..3,330.419 rows=4,051 loops=1)

  • Output: a.applicationid, a.applicationnumber, a.consentdate, a.initiallimit, a.initialmaturity, a.leadclickid, a.applicationdate, c.clientid, cl.externalid, pr.productkey, aps.applicationstatus, (min(ac.created)), (max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decidedat ELSE NULL::timestamp without time zone END)), (max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decision ELSE NULL::integer END)), (max(CASE WHEN (ac.status = 0) THEN ac."limit" ELSE NULL::numeric END)), (max(CASE WHEN (ac.status = 0) THEN ac.maturity ELSE NULL::integer END)), (max(CASE WHEN (ac.source = 1) THEN 1 ELSE 0 END)), (max(CASE WHEN (ac.source = 0) THEN ac.decidedat ELSE NULL::timestamp without time zone END)), l.loannumber, l.startdatetime, l.loanid, aps_1.loanstatus, lo.finalrate, lo.finalamount, p.processdate, pm.promocode, ps.min_date, ps.max_date
  • Hash Cond: (l.loanid = ps.loanid)
  • Buffers: shared hit=1,841,299
10. 0.856 3,163.890 ↓ 39.0 4,051 1

Hash Left Join (cost=165,933.79..181,683.60 rows=104 width=256) (actual time=3,116.516..3,163.890 rows=4,051 loops=1)

  • Output: a.applicationid, a.applicationnumber, a.consentdate, a.initiallimit, a.initialmaturity, a.leadclickid, a.applicationdate, c.clientid, cl.externalid, pr.productkey, aps.applicationstatus, (min(ac.created)), (max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decidedat ELSE NULL::timestamp without time zone END)), (max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decision ELSE NULL::integer END)), (max(CASE WHEN (ac.status = 0) THEN ac."limit" ELSE NULL::numeric END)), (max(CASE WHEN (ac.status = 0) THEN ac.maturity ELSE NULL::integer END)), (max(CASE WHEN (ac.source = 1) THEN 1 ELSE 0 END)), (max(CASE WHEN (ac.source = 0) THEN ac.decidedat ELSE NULL::timestamp without time zone END)), l.loannumber, l.startdatetime, l.loanid, aps_1.loanstatus, lo.finalrate, lo.finalamount, p.processdate, pm.promocode
  • Hash Cond: (a.promocodeid = pm.promocodeid)
  • Buffers: shared hit=1,839,375
11. 3.237 3,163.014 ↓ 39.0 4,051 1

Nested Loop Left Join (cost=165,932.34..181,681.72 rows=104 width=252) (actual time=3,116.479..3,163.014 rows=4,051 loops=1)

  • Output: a.applicationid, a.applicationnumber, a.consentdate, a.initiallimit, a.initialmaturity, a.promocodeid, a.leadclickid, a.applicationdate, c.clientid, cl.externalid, pr.productkey, aps.applicationstatus, (min(ac.created)), (max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decidedat ELSE NULL::timestamp without time zone END)), (max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decision ELSE NULL::integer END)), (max(CASE WHEN (ac.status = 0) THEN ac."limit" ELSE NULL::numeric END)), (max(CASE WHEN (ac.status = 0) THEN ac.maturity ELSE NULL::integer END)), (max(CASE WHEN (ac.source = 1) THEN 1 ELSE 0 END)), (max(CASE WHEN (ac.source = 0) THEN ac.decidedat ELSE NULL::timestamp without time zone END)), l.loannumber, l.startdatetime, l.loanid, aps_1.loanstatus, lo.finalrate, lo.finalamount, p.processdate
  • Buffers: shared hit=1,839,374
12. 1.480 3,159.777 ↓ 39.0 4,051 1

Hash Left Join (cost=165,932.05..181,644.77 rows=104 width=244) (actual time=3,116.443..3,159.777 rows=4,051 loops=1)

  • Output: a.applicationid, a.applicationnumber, a.consentdate, a.initiallimit, a.initialmaturity, a.promocodeid, a.leadclickid, a.applicationdate, c.clientid, cl.externalid, pr.productkey, aps.applicationstatus, (min(ac.created)), (max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decidedat ELSE NULL::timestamp without time zone END)), (max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decision ELSE NULL::integer END)), (max(CASE WHEN (ac.status = 0) THEN ac."limit" ELSE NULL::numeric END)), (max(CASE WHEN (ac.status = 0) THEN ac.maturity ELSE NULL::integer END)), (max(CASE WHEN (ac.source = 1) THEN 1 ELSE 0 END)), (max(CASE WHEN (ac.source = 0) THEN ac.decidedat ELSE NULL::timestamp without time zone END)), l.loannumber, l.startdatetime, l.loanid, aps_1.loanstatus, lo.finalrate, lo.finalamount
  • Hash Cond: (l.loanid = lo.loanid)
  • Buffers: shared hit=1,836,087
13. 0.341 2,851.177 ↓ 39.0 4,051 1

Nested Loop (cost=153,212.12..168,092.08 rows=104 width=180) (actual time=2,809.207..2,851.177 rows=4,051 loops=1)

  • Output: a.applicationid, a.applicationnumber, a.consentdate, a.initiallimit, a.initialmaturity, a.promocodeid, a.leadclickid, a.applicationdate, c.clientid, cl.externalid, pr.productkey, aps.applicationstatus, (min(ac.created)), (max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decidedat ELSE NULL::timestamp without time zone END)), (max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decision ELSE NULL::integer END)), (max(CASE WHEN (ac.status = 0) THEN ac."limit" ELSE NULL::numeric END)), (max(CASE WHEN (ac.status = 0) THEN ac.maturity ELSE NULL::integer END)), (max(CASE WHEN (ac.source = 1) THEN 1 ELSE 0 END)), (max(CASE WHEN (ac.source = 0) THEN ac.decidedat ELSE NULL::timestamp without time zone END)), l.loannumber, l.startdatetime, l.loanid, aps_1.loanstatus
  • Buffers: shared hit=1,834,890
14. 3.370 2,846.785 ↓ 39.0 4,051 1

Nested Loop (cost=153,211.98..168,073.53 rows=104 width=154) (actual time=2,809.199..2,846.785 rows=4,051 loops=1)

  • Output: a.applicationid, a.applicationnumber, a.consentdate, a.initiallimit, a.initialmaturity, a.productid, a.promocodeid, a.leadclickid, a.applicationdate, c.clientid, cl.externalid, aps.applicationstatus, (min(ac.created)), (max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decidedat ELSE NULL::timestamp without time zone END)), (max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decision ELSE NULL::integer END)), (max(CASE WHEN (ac.status = 0) THEN ac."limit" ELSE NULL::numeric END)), (max(CASE WHEN (ac.status = 0) THEN ac.maturity ELSE NULL::integer END)), (max(CASE WHEN (ac.source = 1) THEN 1 ELSE 0 END)), (max(CASE WHEN (ac.source = 0) THEN ac.decidedat ELSE NULL::timestamp without time zone END)), l.loannumber, l.startdatetime, l.loanid, aps_1.loanstatus
  • Buffers: shared hit=1,826,788
15. 2.589 2,831.262 ↓ 39.0 4,051 1

Nested Loop (cost=153,211.55..168,024.35 rows=104 width=147) (actual time=2,809.185..2,831.262 rows=4,051 loops=1)

  • Output: a.applicationid, a.applicationnumber, a.consentdate, a.initiallimit, a.initialmaturity, a.productid, a.promocodeid, a.leadclickid, a.applicationdate, c.clientid, aps.applicationstatus, (min(ac.created)), (max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decidedat ELSE NULL::timestamp without time zone END)), (max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decision ELSE NULL::integer END)), (max(CASE WHEN (ac.status = 0) THEN ac."limit" ELSE NULL::numeric END)), (max(CASE WHEN (ac.status = 0) THEN ac.maturity ELSE NULL::integer END)), (max(CASE WHEN (ac.source = 1) THEN 1 ELSE 0 END)), (max(CASE WHEN (ac.source = 0) THEN ac.decidedat ELSE NULL::timestamp without time zone END)), l.loannumber, l.startdatetime, l.loanid, aps_1.loanstatus
  • Buffers: shared hit=1,810,560
16. 7.104 2,816.520 ↓ 39.0 4,051 1

Merge Left Join (cost=153,211.12..167,782.46 rows=104 width=147) (actual time=2,809.159..2,816.520 rows=4,051 loops=1)

  • Output: a.applicationid, a.applicationnumber, a.consentdate, a.initiallimit, a.initialmaturity, a.personid, a.productid, a.promocodeid, a.leadclickid, a.applicationdate, aps.applicationstatus, (min(ac.created)), (max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decidedat ELSE NULL::timestamp without time zone END)), (max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decision ELSE NULL::integer END)), (max(CASE WHEN (ac.status = 0) THEN ac."limit" ELSE NULL::numeric END)), (max(CASE WHEN (ac.status = 0) THEN ac.maturity ELSE NULL::integer END)), (max(CASE WHEN (ac.source = 1) THEN 1 ELSE 0 END)), (max(CASE WHEN (ac.source = 0) THEN ac.decidedat ELSE NULL::timestamp without time zone END)), l.loannumber, l.startdatetime, l.loanid, aps_1.loanstatus
  • Merge Cond: (l.loanid = aps_1.loanid)
  • Buffers: shared hit=1,794,324
17. 2.942 2,538.633 ↓ 39.0 4,051 1

Sort (cost=153,210.70..153,210.96 rows=104 width=143) (actual time=2,537.366..2,538.633 rows=4,051 loops=1)

  • Output: a.applicationid, a.applicationnumber, a.consentdate, a.initiallimit, a.initialmaturity, a.personid, a.productid, a.promocodeid, a.leadclickid, a.applicationdate, aps.applicationstatus, (min(ac.created)), (max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decidedat ELSE NULL::timestamp without time zone END)), (max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decision ELSE NULL::integer END)), (max(CASE WHEN (ac.status = 0) THEN ac."limit" ELSE NULL::numeric END)), (max(CASE WHEN (ac.status = 0) THEN ac.maturity ELSE NULL::integer END)), (max(CASE WHEN (ac.source = 1) THEN 1 ELSE 0 END)), (max(CASE WHEN (ac.source = 0) THEN ac.decidedat ELSE NULL::timestamp without time zone END)), l.loannumber, l.startdatetime, l.loanid
  • Sort Key: l.loanid
  • Sort Method: quicksort Memory: 845kB
  • Buffers: shared hit=1,558,187
18. 1.049 2,535.691 ↓ 39.0 4,051 1

Nested Loop Left Join (cost=10,141.42..153,207.22 rows=104 width=143) (actual time=2,493.876..2,535.691 rows=4,051 loops=1)

  • Output: a.applicationid, a.applicationnumber, a.consentdate, a.initiallimit, a.initialmaturity, a.personid, a.productid, a.promocodeid, a.leadclickid, a.applicationdate, aps.applicationstatus, (min(ac.created)), (max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decidedat ELSE NULL::timestamp without time zone END)), (max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decision ELSE NULL::integer END)), (max(CASE WHEN (ac.status = 0) THEN ac."limit" ELSE NULL::numeric END)), (max(CASE WHEN (ac.status = 0) THEN ac.maturity ELSE NULL::integer END)), (max(CASE WHEN (ac.source = 1) THEN 1 ELSE 0 END)), (max(CASE WHEN (ac.source = 0) THEN ac.decidedat ELSE NULL::timestamp without time zone END)), l.loannumber, l.startdatetime, l.loanid
  • Buffers: shared hit=1,558,187
19. 33.663 2,530.591 ↓ 39.0 4,051 1

Merge Left Join (cost=10,141.12..153,173.14 rows=104 width=126) (actual time=2,493.857..2,530.591 rows=4,051 loops=1)

  • Output: a.applicationid, a.applicationnumber, a.consentdate, a.initiallimit, a.initialmaturity, a.personid, a.productid, a.promocodeid, a.leadclickid, a.applicationdate, aps.applicationstatus, (min(ac.created)), (max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decidedat ELSE NULL::timestamp without time zone END)), (max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decision ELSE NULL::integer END)), (max(CASE WHEN (ac.status = 0) THEN ac."limit" ELSE NULL::numeric END)), (max(CASE WHEN (ac.status = 0) THEN ac.maturity ELSE NULL::integer END)), (max(CASE WHEN (ac.source = 1) THEN 1 ELSE 0 END)), (max(CASE WHEN (ac.source = 0) THEN ac.decidedat ELSE NULL::timestamp without time zone END)), (max(CASE WHEN (ac.status = 0) THEN ac.applicationcreditdecisionid ELSE NULL::integer END))
  • Merge Cond: (a.applicationid = ac.applicationid)
  • Buffers: shared hit=1,548,982
20. 21.622 1,580.826 ↓ 39.0 4,051 1

Merge Join (cost=10,140.70..104,232.32 rows=104 width=54) (actual time=1,560.033..1,580.826 rows=4,051 loops=1)

  • Output: a.applicationid, a.applicationnumber, a.consentdate, a.initiallimit, a.initialmaturity, a.personid, a.productid, a.promocodeid, a.leadclickid, a.applicationdate, aps.applicationstatus
  • Merge Cond: (aps.applicationid = a.applicationid)
  • Buffers: shared hit=1,158,334
21. 141.654 1,522.662 ↓ 37.3 324,822 1

Subquery Scan on aps (cost=0.43..94,071.15 rows=8,697 width=8) (actual time=0.091..1,522.662 rows=324,822 loops=1)

  • Output: aps.applicationid, aps.created, aps.applicationstatus, aps.max_created
  • Filter: (aps.created = aps.max_created)
  • Rows Removed by Filter: 1,390,017
  • Buffers: shared hit=1,151,894
22. 704.968 1,381.008 ↑ 1.0 1,714,839 1

WindowAgg (cost=0.43..72,328.91 rows=1,739,379 width=24) (actual time=0.087..1,381.008 rows=1,714,839 loops=1)

  • Output: a_1.applicationid, a_1.created, a_1.applicationstatus, max(a_1.created) OVER (?)
  • Buffers: shared hit=1,151,894
23. 676.040 676.040 ↑ 1.0 1,714,848 1

Index Scan using applicationstatushistory_applicationid_idx on axirep.applicationstatushistory a_1 (cost=0.43..46,238.23 rows=1,739,379 width=16) (actual time=0.078..676.040 rows=1,714,848 loops=1)

  • Output: a_1.applicationstatushistoryid, a_1.created, a_1.updated, a_1.applicationstatus, a_1.applicationid
  • Buffers: shared hit=1,151,894
24. 2.534 36.542 ↓ 1.0 4,051 1

Sort (cost=10,129.45..10,139.33 rows=3,951 width=50) (actual time=35.432..36.542 rows=4,051 loops=1)

  • Output: a.applicationid, a.applicationnumber, a.consentdate, a.initiallimit, a.initialmaturity, a.personid, a.productid, a.promocodeid, a.leadclickid, a.applicationdate
  • Sort Key: a.applicationid
  • Sort Method: quicksort Memory: 666kB
  • Buffers: shared hit=6,440
25. 6.797 34.008 ↓ 1.0 4,051 1

Gather (cost=1,000.00..9,893.42 rows=3,951 width=50) (actual time=9.727..34.008 rows=4,051 loops=1)

  • Output: a.applicationid, a.applicationnumber, a.consentdate, a.initiallimit, a.initialmaturity, a.personid, a.productid, a.promocodeid, a.leadclickid, a.applicationdate
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=6,440
26. 27.211 27.211 ↑ 1.2 1,350 3 / 3

Parallel Seq Scan on axirep.application a (cost=0.00..8,498.32 rows=1,646 width=50) (actual time=4.979..27.211 rows=1,350 loops=3)

  • Output: a.applicationid, a.applicationnumber, a.consentdate, a.initiallimit, a.initialmaturity, a.personid, a.productid, a.promocodeid, a.leadclickid, a.applicationdate
  • Filter: ((a.consentdate >= '2020-06-16'::date) AND (a.consentdate < '2020-06-21'::date))
  • Rows Removed by Filter: 108,427
  • Buffers: shared hit=6,440
  • Worker 0: actual time=3.062..25.638 rows=1,408 loops=1
  • Buffers: shared hit=1,765
  • Worker 1: actual time=3.048..24.628 rows=1,332 loops=1
  • Buffers: shared hit=1,782
27. 67.106 916.102 ↓ 1.4 324,184 1

Materialize (cost=0.42..48,370.40 rows=227,779 width=76) (actual time=0.043..916.102 rows=324,184 loops=1)

  • Output: ac.applicationid, (min(ac.created)), (max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decidedat ELSE NULL::timestamp without time zone END)), (max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decision ELSE NULL::integer END)), (max(CASE WHEN (ac.status = 0) THEN ac."limit" ELSE NULL::numeric END)), (max(CASE WHEN (ac.status = 0) THEN ac.maturity ELSE NULL::integer END)), (max(CASE WHEN (ac.source = 1) THEN 1 ELSE 0 END)), (max(CASE WHEN (ac.source = 0) THEN ac.decidedat ELSE NULL::timestamp without time zone END)), (max(CASE WHEN (ac.status = 0) THEN ac.applicationcreditdecisionid ELSE NULL::integer END))
  • Buffers: shared hit=390,648
28. 646.968 848.996 ↓ 1.4 324,184 1

GroupAggregate (cost=0.42..45,523.16 rows=227,779 width=76) (actual time=0.040..848.996 rows=324,184 loops=1)

  • Output: ac.applicationid, min(ac.created), max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decidedat ELSE NULL::timestamp without time zone END), max(CASE WHEN ((ac.status = ANY ('{0,1}'::integer[])) AND (ac.source = ANY ('{1,2}'::integer[]))) THEN ac.decision ELSE NULL::integer END), max(CASE WHEN (ac.status = 0) THEN ac."limit" ELSE NULL::numeric END), max(CASE WHEN (ac.status = 0) THEN ac.maturity ELSE NULL::integer END), max(CASE WHEN (ac.source = 1) THEN 1 ELSE 0 END), max(CASE WHEN (ac.source = 0) THEN ac.decidedat ELSE NULL::timestamp without time zone END), max(CASE WHEN (ac.status = 0) THEN ac.applicationcreditdecisionid ELSE NULL::integer END)
  • Group Key: ac.applicationid
  • Buffers: shared hit=390,648
29. 202.028 202.028 ↑ 1.0 534,064 1

Index Scan using applicationcreditdecision_applicationid_idx on axirep.applicationcreditdecision ac (cost=0.42..18,823.42 rows=542,710 width=45) (actual time=0.014..202.028 rows=534,064 loops=1)

  • Output: ac.applicationcreditdecisionid, ac.created, ac.updated, ac.decidedat, ac.decision, ac.expireddate, ac."limit", ac.maturity, ac.payouttype, ac.percent, ac.psk, ac.pskrate, ac.source, ac.status, ac.applicationid, ac.declinereasonid, ac.frontuserid, ac.insurance_allowed, ac.client_agrees_insurance, ac.pdn_value, ac.pdn_category
  • Buffers: shared hit=390,648
30. 4.051 4.051 ↓ 0.0 0 4,051

Index Scan using loan_applicationcreditdecisionid_idx on axirep.loan l (cost=0.29..0.32 rows=1 width=25) (actual time=0.001..0.001 rows=0 loops=4,051)

  • Output: l.loanid, l.created, l.updated, l.currency, l.loannumber, l.maturitymeasure, l.startdatetime, l.applicationcreditdecisionid, l.factenddate, l.tradeuuid
  • Index Cond: (l.applicationcreditdecisionid = (max(CASE WHEN (ac.status = 0) THEN ac.applicationcreditdecisionid ELSE NULL::integer END)))
  • Buffers: shared hit=9,205
31. 10.729 270.783 ↓ 56.8 74,113 1

Materialize (cost=0.42..14,567.95 rows=1,305 width=8) (actual time=0.037..270.783 rows=74,113 loops=1)

  • Output: aps_1.loanid, aps_1.statusdate, aps_1.loanstatus, aps_1.max_created
  • Buffers: shared hit=236,137
32. 26.371 260.054 ↓ 56.8 74,113 1

Subquery Scan on aps_1 (cost=0.42..14,564.69 rows=1,305 width=8) (actual time=0.032..260.054 rows=74,113 loops=1)

  • Output: aps_1.loanid, aps_1.statusdate, aps_1.loanstatus, aps_1.max_created
  • Filter: (aps_1.statusdate = aps_1.max_created)
  • Rows Removed by Filter: 186,452
  • Buffers: shared hit=236,137
33. 112.839 233.683 ↑ 1.0 260,565 1

WindowAgg (cost=0.42..11,302.08 rows=261,009 width=24) (actual time=0.025..233.683 rows=260,565 loops=1)

  • Output: a_2.loanid, a_2.statusdate, a_2.loanstatus, max(a_2.statusdate) OVER (?)
  • Buffers: shared hit=236,137
34. 120.844 120.844 ↑ 1.0 260,567 1

Index Scan using loanstatushistory_loanid_idx on axirep.loanstatushistory a_2 (cost=0.42..7,386.94 rows=261,009 width=16) (actual time=0.016..120.844 rows=260,567 loops=1)

  • Output: a_2.loanstatushistoryid, a_2.created, a_2.updated, a_2.isactive, a_2.loanstatus, a_2.statusdate, a_2.loanid
  • Buffers: shared hit=236,137
35. 12.153 12.153 ↑ 1.0 1 4,051

Index Scan using person_pkey on axirep.person c (cost=0.43..2.32 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=4,051)

  • Output: c.personid, c.created, c.updated, c.inn, c.snils, c.bankrupt, c.birthdate, c.childrennumber, c.childrennumberlt21, c.citizenship, c.court, c.dependecesnumber, c.educationtype, c.gender, c.maritalstatus, c.name, c.patronymic, c.surname, c.addresslivingid, c.addressregistrationid, c.clientid, c.personemailid
  • Index Cond: (c.personid = a.personid)
  • Buffers: shared hit=16,236
36. 12.153 12.153 ↑ 1.0 1 4,051

Index Scan using client_pkey on axirep.client cl (cost=0.43..0.46 rows=1 width=11) (actual time=0.003..0.003 rows=1 loops=4,051)

  • Output: cl.clientid, cl.created, cl.updated, cl.agreetermdate, cl.agreetermexpiredate, cl.agreetermflag, cl.agreetermpropose, cl.discountid, cl.externalid, cl.mobilephone, cl.password, cl.registrationclientid, cl.basicstandardid, cl.oldclientadddata
  • Index Cond: (cl.clientid = c.clientid)
  • Buffers: shared hit=16,228
37. 4.051 4.051 ↑ 1.0 1 4,051

Index Scan using product_pkey on axirep.product pr (cost=0.15..0.17 rows=1 width=34) (actual time=0.001..0.001 rows=1 loops=4,051)

  • Output: pr.productid, pr.productagemax, pr.productagemin, pr.productcurrentexperiencemin, pr.productmaxamount, pr.productmaxmaturity, pr.productmaxterm, pr.productmeasure, pr.productminamount, pr.productminincome, pr.productminmaturity, pr.productminterm, pr.producttermmeasure, pr.producttotalexperiencemin, pr.rate, pr.isactive, pr.productkey, pr.productname, pr.islimitfsp2018, pr.islimitfsp2019, pr.islimitpercent, pr.limitfsp
  • Index Cond: (pr.productid = a.productid)
  • Buffers: shared hit=8,102
38. 16.456 307.120 ↓ 1.2 74,247 1

Hash (cost=11,920.34..11,920.34 rows=63,967 width=68) (actual time=307.120..307.120 rows=74,247 loops=1)

  • Output: lo.finalrate, lo.finalamount, lo.loanid
  • Buckets: 131,072 (originally 65536) Batches: 1 (originally 1) Memory Usage: 4,512kB
  • Buffers: shared hit=1,197
39. 7.793 290.664 ↓ 1.2 74,247 1

Subquery Scan on lo (cost=10,641.00..11,920.34 rows=63,967 width=68) (actual time=255.963..290.664 rows=74,247 loops=1)

  • Output: lo.finalrate, lo.finalamount, lo.loanid
  • Buffers: shared hit=1,197
40. 65.992 282.871 ↓ 1.2 74,247 1

HashAggregate (cost=10,641.00..11,280.67 rows=63,967 width=86) (actual time=255.961..282.871 rows=74,247 loops=1)

  • Output: lo_1.loanid, (first_value(lo_1.finalrate) OVER (?)), (first_value(lo_1.finalamount) OVER (?)), lo_1.historydate, lo_1.finalrate, lo_1.finalamount
  • Group Key: lo_1.loanid, first_value(lo_1.finalrate) OVER (?), first_value(lo_1.finalamount) OVER (?)
  • Buffers: shared hit=1,197
41. 54.538 216.879 ↓ 1.4 88,910 1

WindowAgg (cost=8,721.99..10,161.25 rows=63,967 width=86) (actual time=126.249..216.879 rows=88,910 loops=1)

  • Output: lo_1.loanid, first_value(lo_1.finalrate) OVER (?), first_value(lo_1.finalamount) OVER (?), lo_1.historydate, lo_1.finalrate, lo_1.finalamount
  • Buffers: shared hit=1,197
42. 71.154 162.341 ↓ 1.4 88,910 1

Sort (cost=8,721.99..8,881.91 rows=63,967 width=22) (actual time=126.234..162.341 rows=88,910 loops=1)

  • Output: lo_1.loanid, lo_1.historydate, lo_1.finalrate, lo_1.finalamount
  • Sort Key: lo_1.loanid, lo_1.historydate
  • Sort Method: quicksort Memory: 10,019kB
  • Buffers: shared hit=1,197
43. 81.079 91.187 ↓ 1.4 88,910 1

HashAggregate (cost=2,976.14..3,615.81 rows=63,967 width=22) (actual time=57.916..91.187 rows=88,910 loops=1)

  • Output: lo_1.loanid, lo_1.historydate, lo_1.finalrate, lo_1.finalamount
  • Group Key: lo_1.loanid, lo_1.historydate, lo_1.finalrate, lo_1.finalamount
  • Buffers: shared hit=1,197
44. 10.108 10.108 ↑ 1.0 88,957 1

Seq Scan on axirep.loanhistory lo_1 (cost=0.00..2,086.57 rows=88,957 width=22) (actual time=0.016..10.108 rows=88,957 loops=1)

  • Output: lo_1.loanhistoryid, lo_1.created, lo_1.updated, lo_1.finalamount, lo_1.finalmaturity, lo_1.finalrate, lo_1.historydate, lo_1.isactive, lo_1.planenddate, lo_1.psk, lo_1.pskrate, lo_1.loanid
  • Buffers: shared hit=1,197
45. 0.000 0.000 ↓ 0.0 0 4,051

Index Scan using paymentout_loanid_idx on axirep.paymentout p (cost=0.29..0.35 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=4,051)

  • Output: p.paymentoutid, p.created, p.updated, p.amount, p.cardholdername, p.cardnumber, p.cardtype, p.comment, p.currency, p.paymentstatus, p.processdate, p.transactionid, p.clientid, p.externalsystemid, p.loanid, p.error, p.paytype, p.bankacquire
  • Index Cond: (p.loanid = l.loanid)
  • Filter: (p.paymentstatus = 2)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=3,287
46. 0.004 0.020 ↑ 1.0 20 1

Hash (cost=1.20..1.20 rows=20 width=12) (actual time=0.020..0.020 rows=20 loops=1)

  • Output: pm.promocode, pm.promocodeid
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
47. 0.016 0.016 ↑ 1.0 20 1

Seq Scan on axirep.promocode pm (cost=0.00..1.20 rows=20 width=12) (actual time=0.012..0.016 rows=20 loops=1)

  • Output: pm.promocode, pm.promocodeid
  • Buffers: shared hit=1
48. 12.493 164.962 ↓ 1.1 72,434 1

Hash (cost=7,912.28..7,912.28 rows=63,967 width=12) (actual time=164.962..164.962 rows=72,434 loops=1)

  • Output: ps.min_date, ps.max_date, ps.loanid
  • Buckets: 131,072 (originally 65536) Batches: 1 (originally 1) Memory Usage: 4,137kB
  • Buffers: shared hit=1,924
49. 6.493 152.469 ↓ 1.1 72,434 1

Subquery Scan on ps (cost=6,632.94..7,912.28 rows=63,967 width=12) (actual time=115.262..152.469 rows=72,434 loops=1)

  • Output: ps.min_date, ps.max_date, ps.loanid
  • Buffers: shared hit=1,924
50. 69.246 145.976 ↓ 1.1 72,434 1

HashAggregate (cost=6,632.94..7,272.61 rows=63,967 width=12) (actual time=115.261..145.976 rows=72,434 loops=1)

  • Output: lo_2.loanid, min(p_1.scheduledate), max(p_1.scheduledate)
  • Group Key: lo_2.loanid
  • Buffers: shared hit=1,924
51. 39.493 76.730 ↓ 1.0 87,144 1

Hash Join (cost=2,687.78..5,979.37 rows=87,143 width=8) (actual time=28.168..76.730 rows=87,144 loops=1)

  • Output: lo_2.loanid, p_1.scheduledate
  • Hash Cond: (lo_2.loanhistoryid = p_1.loanhistoryid)
  • Buffers: shared hit=1,924
52. 9.677 9.677 ↑ 1.0 88,957 1

Seq Scan on axirep.loanhistory lo_2 (cost=0.00..2,086.57 rows=88,957 width=8) (actual time=0.009..9.677 rows=88,957 loops=1)

  • Output: lo_2.loanhistoryid, lo_2.created, lo_2.updated, lo_2.finalamount, lo_2.finalmaturity, lo_2.finalrate, lo_2.historydate, lo_2.isactive, lo_2.planenddate, lo_2.psk, lo_2.pskrate, lo_2.loanid
  • Buffers: shared hit=1,197
53. 13.620 27.560 ↓ 1.0 87,144 1

Hash (cost=1,598.49..1,598.49 rows=87,143 width=8) (actual time=27.560..27.560 rows=87,144 loops=1)

  • Output: p_1.scheduledate, p_1.loanhistoryid
  • Buckets: 131,072 Batches: 1 Memory Usage: 4,429kB
  • Buffers: shared hit=727
54. 13.940 13.940 ↓ 1.0 87,144 1

Seq Scan on axirep.paymentschedule p_1 (cost=0.00..1,598.49 rows=87,143 width=8) (actual time=0.014..13.940 rows=87,144 loops=1)

  • Output: p_1.scheduledate, p_1.loanhistoryid
  • Filter: p_1.isactive
  • Rows Removed by Filter: 5
  • Buffers: shared hit=727
55. 2.951 602.856 ↓ 1.3 21,945 1

Hash (cost=43,285.55..43,285.55 rows=16,741 width=12) (actual time=602.856..602.856 rows=21,945 loops=1)

  • Output: ll.last_loan_dt, ll.clientid
  • Buckets: 32,768 Batches: 1 Memory Usage: 1,199kB
  • Buffers: shared hit=1,154,864
56. 1.896 599.905 ↓ 1.3 21,945 1

Subquery Scan on ll (cost=42,950.73..43,285.55 rows=16,741 width=12) (actual time=594.867..599.905 rows=21,945 loops=1)

  • Output: ll.last_loan_dt, ll.clientid
  • Buffers: shared hit=1,154,864
57. 12.193 598.009 ↓ 1.3 21,945 1

Finalize HashAggregate (cost=42,950.73..43,118.14 rows=16,741 width=12) (actual time=594.866..598.009 rows=21,945 loops=1)

  • Output: po.clientid, min(ah.created)
  • Group Key: po.clientid
  • Buffers: shared hit=1,154,864
58. 22.279 585.816 ↓ 2.8 38,991 1

Gather (cost=41,416.23..42,880.98 rows=13,950 width=12) (actual time=563.942..585.816 rows=38,991 loops=1)

  • Output: po.clientid, (PARTIAL min(ah.created))
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=1,154,864
59. 21.648 563.537 ↓ 1.9 12,997 3 / 3

Partial HashAggregate (cost=40,416.23..40,485.98 rows=6,975 width=12) (actual time=558.330..563.537 rows=12,997 loops=3)

  • Output: po.clientid, PARTIAL min(ah.created)
  • Group Key: po.clientid
  • Buffers: shared hit=1,154,864
  • Worker 0: actual time=556.548..559.452 rows=12,176 loops=1
  • Buffers: shared hit=343,554
  • Worker 1: actual time=555.323..563.558 rows=11,949 loops=1
  • Buffers: shared hit=336,691
60. 13.760 541.889 ↓ 3.5 24,116 3 / 3

Nested Loop (cost=3,813.68..40,381.36 rows=6,975 width=12) (actual time=42.300..541.889 rows=24,116 loops=3)

  • Output: po.clientid, ah.created
  • Buffers: shared hit=1,154,864
  • Worker 0: actual time=40.885..538.601 rows=21,491 loops=1
  • Buffers: shared hit=343,554
  • Worker 1: actual time=56.545..542.097 rows=21,008 loops=1
  • Buffers: shared hit=336,691
61. 16.639 479.897 ↓ 3.5 24,116 3 / 3

Nested Loop (cost=3,813.39..37,917.49 rows=6,984 width=12) (actual time=42.272..479.897 rows=24,116 loops=3)

  • Output: ah.created, l_1.loanid
  • Join Filter: (ah.applicationid = a_3.applicationid)
  • Buffers: shared hit=937,151
  • Worker 0: actual time=40.851..479.016 rows=21,491 loops=1
  • Buffers: shared hit=278,873
  • Worker 1: actual time=56.505..486.356 rows=21,008 loops=1
  • Buffers: shared hit=273,486
62. 7.838 390.910 ↓ 3.0 24,116 3 / 3

Hash Anti Join (cost=3,812.97..34,109.11 rows=8,128 width=20) (actual time=42.218..390.910 rows=24,116 loops=3)

  • Output: ah.created, ah.applicationid, ad.applicationid, l_1.loanid
  • Hash Cond: (l_1.loanid = ls1.loanid)
  • Buffers: shared hit=647,157
  • Worker 0: actual time=40.784..389.038 rows=21,491 loops=1
  • Buffers: shared hit=192,747
  • Worker 1: actual time=56.425..396.891 rows=21,008 loops=1
  • Buffers: shared hit=189,261
63. 0.000 376.870 ↓ 2.9 24,145 3 / 3

Nested Loop (cost=2,940.94..33,128.50 rows=8,234 width=20) (actual time=35.948..376.870 rows=24,145 loops=3)

  • Output: ah.created, ah.applicationid, ad.applicationid, l_1.loanid
  • Buffers: shared hit=643,681
  • Worker 0: actual time=33.508..374.720 rows=21,508 loops=1
  • Buffers: shared hit=191,588
  • Worker 1: actual time=47.168..380.484 rows=21,028 loops=1
  • Buffers: shared hit=188,102
64. 65.135 131.613 ↑ 1.2 24,748 3 / 3

Hash Join (cost=2,940.51..14,661.14 rows=30,935 width=8) (actual time=35.882..131.613 rows=24,748 loops=3)

  • Output: ad.applicationid, l_1.loanid
  • Hash Cond: (ad.applicationcreditdecisionid = l_1.applicationcreditdecisionid)
  • Buffers: shared hit=12,118
  • Worker 0: actual time=33.437..119.734 rows=22,048 loops=1
  • Buffers: shared hit=3,666
  • Worker 1: actual time=47.063..138.725 rows=21,554 loops=1
  • Buffers: shared hit=3,616
65. 31.271 31.271 ↑ 1.3 180,903 3 / 3

Parallel Seq Scan on axirep.applicationcreditdecision ad (cost=0.00..10,563.29 rows=226,129 width=8) (actual time=0.017..31.271 rows=180,903 loops=3)

  • Output: ad.applicationcreditdecisionid, ad.created, ad.updated, ad.decidedat, ad.decision, ad.expireddate, ad."limit", ad.maturity, ad.payouttype, ad.percent, ad.psk, ad.pskrate, ad.source, ad.status, ad.applicationid, ad.declinereasonid, ad.frontuserid, ad.insurance_allowed, ad.client_agrees_insurance, ad.pdn_value, ad.pdn_category
  • Buffers: shared hit=8,302
  • Worker 0: actual time=0.017..29.851 rows=156,219 loops=1
  • Buffers: shared hit=2,393
  • Worker 1: actual time=0.019..34.716 rows=152,797 loops=1
  • Buffers: shared hit=2,343
66. 16.010 35.207 ↑ 1.0 74,245 3 / 3

Hash (cost=2,012.45..2,012.45 rows=74,245 width=8) (actual time=35.207..35.207 rows=74,245 loops=3)

  • Output: l_1.applicationcreditdecisionid, l_1.loanid
  • Buckets: 131,072 Batches: 1 Memory Usage: 3,925kB
  • Buffers: shared hit=3,810
  • Worker 0: actual time=32.801..32.801 rows=74,245 loops=1
  • Buffers: shared hit=1,270
  • Worker 1: actual time=46.278..46.278 rows=74,245 loops=1
  • Buffers: shared hit=1,270
67. 19.197 19.197 ↑ 1.0 74,245 3 / 3

Seq Scan on axirep.loan l_1 (cost=0.00..2,012.45 rows=74,245 width=8) (actual time=0.026..19.197 rows=74,245 loops=3)

  • Output: l_1.applicationcreditdecisionid, l_1.loanid
  • Buffers: shared hit=3,810
  • Worker 0: actual time=0.032..19.260 rows=74,245 loops=1
  • Buffers: shared hit=1,270
  • Worker 1: actual time=0.034..25.155 rows=74,245 loops=1
  • Buffers: shared hit=1,270
68. 247.480 247.480 ↑ 1.0 1 74,244 / 3

Index Scan using applicationstatushistory_applicationid_idx on axirep.applicationstatushistory ah (cost=0.43..0.59 rows=1 width=12) (actual time=0.007..0.010 rows=1 loops=74,244)

  • Output: ah.applicationstatushistoryid, ah.created, ah.updated, ah.applicationstatus, ah.applicationid
  • Index Cond: (ah.applicationid = ad.applicationid)
  • Filter: (ah.applicationstatus = 11)
  • Rows Removed by Filter: 8
  • Buffers: shared hit=631,563
  • Worker 0: actual time=0.009..0.011 rows=1 loops=22,048
  • Buffers: shared hit=187,922
  • Worker 1: actual time=0.009..0.011 rows=1 loops=21,554
  • Buffers: shared hit=184,486
69. 0.410 6.202 ↓ 2.0 1,900 3 / 3

Hash (cost=860.11..860.11 rows=953 width=4) (actual time=6.202..6.202 rows=1,900 loops=3)

  • Output: ls1.loanid
  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 83kB
  • Buffers: shared hit=3,476
  • Worker 0: actual time=7.142..7.142 rows=1,900 loops=1
  • Buffers: shared hit=1,159
  • Worker 1: actual time=9.234..9.234 rows=1,900 loops=1
  • Buffers: shared hit=1,159
70. 5.792 5.792 ↓ 2.0 1,900 3 / 3

Index Scan using loanstatushistory_loanstatus_idx on axirep.loanstatushistory ls1 (cost=0.42..860.11 rows=953 width=4) (actual time=0.038..5.792 rows=1,900 loops=3)

  • Output: ls1.loanid
  • Index Cond: (ls1.loanstatus = ANY ('{3,9}'::integer[]))
  • Filter: ls1.isactive
  • Rows Removed by Filter: 7
  • Buffers: shared hit=3,476
  • Worker 0: actual time=0.047..6.858 rows=1,900 loops=1
  • Buffers: shared hit=1,159
  • Worker 1: actual time=0.042..8.579 rows=1,900 loops=1
  • Buffers: shared hit=1,159
71. 72.348 72.348 ↑ 1.0 1 72,348 / 3

Index Only Scan using application_pkey on axirep.application a_3 (cost=0.42..0.46 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=72,348)

  • Output: a_3.applicationid
  • Index Cond: (a_3.applicationid = ad.applicationid)
  • Heap Fetches: 29,849
  • Buffers: shared hit=289,994
  • Worker 0: actual time=0.004..0.004 rows=1 loops=21,491
  • Buffers: shared hit=86,126
  • Worker 1: actual time=0.004..0.004 rows=1 loops=21,008
  • Buffers: shared hit=84,225
72. 48.232 48.232 ↑ 1.0 1 72,348 / 3

Index Scan using paymentout_loanid_idx on axirep.paymentout po (cost=0.29..0.34 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=72,348)

  • Output: po.paymentoutid, po.created, po.updated, po.amount, po.cardholdername, po.cardnumber, po.cardtype, po.comment, po.currency, po.paymentstatus, po.processdate, po.transactionid, po.clientid, po.externalsystemid, po.loanid, po.error, po.paytype, po.bankacquire
  • Index Cond: (po.loanid = l_1.loanid)
  • Buffers: shared hit=217,713
  • Worker 0: actual time=0.002..0.002 rows=1 loops=21,491
  • Buffers: shared hit=64,681
  • Worker 1: actual time=0.002..0.002 rows=1 loops=21,008
  • Buffers: shared hit=63,205
73. 11.077 611.244 ↓ 3.4 63,221 1

Hash (cost=30,529.26..30,529.26 rows=18,419 width=36) (actual time=611.244..611.244 rows=63,221 loops=1)

  • Output: pl.repayments, pl.loanid
  • Buckets: 65,536 (originally 32768) Batches: 1 (originally 1) Memory Usage: 3,259kB
  • Buffers: shared hit=105,399
74. 6.282 600.167 ↓ 3.4 63,221 1

Subquery Scan on pl (cost=30,114.83..30,529.26 rows=18,419 width=36) (actual time=557.112..600.167 rows=63,221 loops=1)

  • Output: pl.repayments, pl.loanid
  • Buffers: shared hit=105,399
75. 114.181 593.885 ↓ 3.4 63,221 1

HashAggregate (cost=30,114.83..30,345.07 rows=18,419 width=36) (actual time=557.110..593.885 rows=63,221 loops=1)

  • Output: ll_1.loanid, sum(p_2.amount)
  • Group Key: ll_1.loanid
  • Buffers: shared hit=105,399
76. 59.264 479.704 ↓ 8.1 148,838 1

Merge Join (cost=24,285.13..30,022.74 rows=18,419 width=10) (actual time=271.699..479.704 rows=148,838 loops=1)

  • Output: ll_1.loanid, p_2.amount
  • Merge Cond: (ll_1.clientbalanceid = c_1.clientbalanceid)
  • Buffers: shared hit=105,399
77. 109.676 109.676 ↑ 22.1 148,839 1

Index Scan using loanallocation_clientbalanceid_idx on axirep.loanallocation ll_1 (cost=0.43..111,966.63 rows=3,292,527 width=8) (actual time=0.029..109.676 rows=148,839 loops=1)

  • Output: ll_1.loanallocationid, ll_1.created, ll_1.updated, ll_1.additionalitemsum, ll_1.billingdate, ll_1.interestcurrent, ll_1.interestoverdue, ll_1.penaltiesfixedtotal, ll_1.penaltiesvartotal, ll_1.principalcurrent, ll_1.principaloverdue, ll_1.isactive, ll_1.loanallocationtype, ll_1.additionlitemid, ll_1.billingdetailid, ll_1.clientbalanceid, ll_1.loanid, ll_1.paymentcorrectionid, ll_1.commissionsum, ll_1.isxlimit
  • Filter: ll_1.isactive
  • Rows Removed by Filter: 1,470
  • Buffers: shared hit=82,069
78. 115.129 310.764 ↓ 8.0 245,254 1

Sort (cost=24,284.65..24,360.99 rows=30,534 width=10) (actual time=271.659..310.764 rows=245,254 loops=1)

  • Output: p_2.amount, c_1.clientbalanceid
  • Sort Key: c_1.clientbalanceid
  • Sort Method: quicksort Memory: 17,641kB
  • Buffers: shared hit=23,330
79. 90.947 195.635 ↓ 8.0 245,254 1

Hash Join (cost=15,675.56..22,010.15 rows=30,534 width=10) (actual time=78.021..195.635 rows=245,254 loops=1)

  • Output: p_2.amount, c_1.clientbalanceid
  • Hash Cond: (c_1.paymentinid = p_2.paymentinid)
  • Buffers: shared hit=23,330
80. 27.248 27.248 ↑ 1.0 245,255 1

Seq Scan on axirep.clientbalance c_1 (cost=0.00..5,109.55 rows=245,255 width=8) (actual time=0.014..27.248 rows=245,255 loops=1)

  • Output: c_1.clientbalanceid, c_1.created, c_1.updated, c_1.balance, c_1.isactive, c_1.processdate, c_1.clientid, c_1.paymentinid, c_1.paymentcorrectionid, c_1.loanallocationid, c_1.commissionamount, c_1.comment, c_1.clientbalancetype
  • Buffers: shared hit=2,657
81. 22.034 77.440 ↑ 1.0 94,684 1

Hash (cost=14,488.95..14,488.95 rows=94,929 width=10) (actual time=77.440..77.440 rows=94,684 loops=1)

  • Output: p_2.amount, p_2.paymentinid
  • Buckets: 131,072 Batches: 1 Memory Usage: 5,121kB
  • Buffers: shared hit=20,673
82. 55.406 55.406 ↑ 1.0 94,684 1

Index Scan using paymentin_paymentstatus_idx on axirep.paymentin p_2 (cost=0.42..14,488.95 rows=94,929 width=10) (actual time=0.028..55.406 rows=94,684 loops=1)

  • Output: p_2.amount, p_2.paymentinid
  • Index Cond: (p_2.paymentstatus = 2)
  • Buffers: shared hit=20,673
83. 8.102 8.102 ↑ 1.0 1 4,051

Index Scan using leadclick_pkey on axirep.leadclick li (cost=0.43..2.52 rows=1 width=169) (actual time=0.002..0.002 rows=1 loops=4,051)

  • Output: li.leadclickid, li.fingerprint, li.headers, li.info, li.method, li.url, li.visittime, li.wmid, li.leadcompanyid
  • Index Cond: (li.leadclickid = a.leadclickid)
  • Buffers: shared hit=10,423