explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tiy8

Settings
# exclusive inclusive rows x rows loops node
1. 0.025 14,368.426 ↓ 6.0 6 1

Nested Loop Left Join (cost=1,694,448.14..1,694,456.35 rows=1 width=146) (actual time=14,367.045..14,368.426 rows=6 loops=1)

  • Output: u.name, (count(h.typeid)), (count(h_1.typeid)), (((((count(h_1.typeid)))::double precision / ((count(h.typeid)))::double precision)) * '100'::double precision), (count(h_2.typeid)), (count(h_3.typeid)), (count(h_4.typeid)), (count(h_5.typeid)), (((((count(h_2.typeid)))::double precision / ((count(h_1.typeid)))::double precision)) * '100'::double precision), (sum(pp.totalamountpromise)), (count(pp.id)), (((((count(pp.id)))::double precision / ((count(h_2.typeid)))::double precision)) * '100'::double precision), (sum(pp_1.totalamountpromise)), (((sum(pp_1.totalamountpromise)) / (sum(pp.totalamountpromise))) * '100'::double precision), ((collection_oper_individual_plan.plan_promise / 21) * (('now'::cstring)::date - '2020-03-01'::date)), ((sum(pp.totalamountpromise)) / (((collection_oper_individual_plan.plan_promise / 21) * (('now'::cstring)::date - '2020-03-01'::date)))::double precision)
  • Join Filter: (u.name = u_1.name)
  • Rows Removed by Join Filter: 66
  • Buffers: shared hit=1162200
2. 0.023 10,647.663 ↓ 6.0 6 1

Nested Loop (cost=1,541,209.93..1,541,213.02 rows=1 width=122) (actual time=10,647.634..10,647.663 rows=6 loops=1)

  • Output: u.name, (count(h.typeid)), (count(h_1.typeid)), ((((count(h_1.typeid)))::double precision / ((count(h.typeid)))::double precision)), (count(h_2.typeid)), (count(h_3.typeid)), (count(h_4.typeid)), (count(h_5.typeid)), ((((count(h_2.typeid)))::double precision / ((count(h_1.typeid)))::double precision)), (sum(pp.totalamountpromise)), (count(pp.id)), ((((count(pp.id)))::double precision / ((count(h_2.typeid)))::double precision)), collection_oper_individual_plan.plan_promise
  • Join Filter: (u.name = collection_oper_individual_plan.name)
  • Rows Removed by Join Filter: 114
  • Buffers: shared hit=892420
3. 0.014 10,647.600 ↑ 3.1 20 1

Sort (cost=1,541,209.93..1,541,210.09 rows=63 width=118) (actual time=10,647.596..10,647.600 rows=20 loops=1)

  • Output: u.name, (count(h.typeid)), (count(h_1.typeid)), ((((count(h_1.typeid)))::double precision / ((count(h.typeid)))::double precision)), (count(h_2.typeid)), (count(h_3.typeid)), (count(h_4.typeid)), (count(h_5.typeid)), ((((count(h_2.typeid)))::double precision / ((count(h_1.typeid)))::double precision)), (sum(pp.totalamountpromise)), (count(pp.id)), ((((count(pp.id)))::double precision / ((count(h_2.typeid)))::double precision))
  • Sort Key: (count(h.typeid)) DESC
  • Sort Method: quicksort Memory: 29kB
  • Buffers: shared hit=892419
4. 0.040 10,647.586 ↑ 3.1 20 1

Hash Right Join (cost=1,541,201.67..1,541,208.05 rows=63 width=118) (actual time=10,647.549..10,647.586 rows=20 loops=1)

  • Output: u.name, (count(h.typeid)), (count(h_1.typeid)), (((count(h_1.typeid)))::double precision / ((count(h.typeid)))::double precision), (count(h_2.typeid)), (count(h_3.typeid)), (count(h_4.typeid)), (count(h_5.typeid)), (((count(h_2.typeid)))::double precision / ((count(h_1.typeid)))::double precision), (sum(pp.totalamountpromise)), (count(pp.id)), (((count(pp.id)))::double precision / ((count(h_2.typeid)))::double precision)
  • Hash Cond: (u.id = h.aid)
  • Buffers: shared hit=892419
5. 0.021 0.021 ↑ 1.0 97 1

Seq Scan on delta.collecting_users u (cost=0.00..3.97 rows=97 width=32) (actual time=0.011..0.021 rows=97 loops=1)

  • Output: u.id, u.groupid, u.ruleid, u.isgrouprule, u.name, u.login, u.passwd, u.ipphonecode, u.created, u.deleted, u.isdenyaccess, u.isreadonly, u.isblocked, u.comments, u.linkedusers, u.city, u.workaddress, u.photo, u.phonemobile, u.phonework, u.phoneinternal, u.applayregions, u.cardviewstage, u.sid, u.ipphonepassword, u.emaillogin, u.emailpassword, u.f778, u.f788, u.f796, u.f797, u.f829, u.email
  • Buffers: shared hit=3
6. 0.011 10,647.525 ↑ 3.1 20 1

Hash (cost=1,541,200.88..1,541,200.88 rows=63 width=66) (actual time=10,647.525..10,647.525 rows=20 loops=1)

  • Output: (count(h.typeid)), h.aid, (count(h_1.typeid)), (count(h_2.typeid)), (count(h_3.typeid)), (count(h_4.typeid)), (count(h_5.typeid)), (sum(pp.totalamountpromise)), (count(pp.id))
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=892416
7. 0.013 10,647.514 ↑ 3.1 20 1

Merge Left Join (cost=1,541,177.21..1,541,200.88 rows=63 width=66) (actual time=10,647.156..10,647.514 rows=20 loops=1)

  • Output: (count(h.typeid)), h.aid, (count(h_1.typeid)), (count(h_2.typeid)), (count(h_3.typeid)), (count(h_4.typeid)), (count(h_5.typeid)), (sum(pp.totalamountpromise)), (count(pp.id))
  • Merge Cond: (h.aid = pp.aid)
  • Buffers: shared hit=892416
8. 0.014 10,636.269 ↑ 3.1 20 1

Merge Left Join (cost=1,539,074.37..1,539,093.11 rows=63 width=50) (actual time=10,636.139..10,636.269 rows=20 loops=1)

  • Output: (count(h.typeid)), h.aid, (count(h_1.typeid)), (count(h_2.typeid)), (count(h_3.typeid)), (count(h_4.typeid)), (count(h_5.typeid))
  • Merge Cond: (h.aid = h_5.aid)
  • Buffers: shared hit=891609
9. 0.013 8,255.902 ↑ 3.1 20 1

Merge Left Join (cost=1,282,007.39..1,282,022.82 rows=63 width=42) (actual time=8,255.791..8,255.902 rows=20 loops=1)

  • Output: (count(h.typeid)), h.aid, (count(h_1.typeid)), (count(h_2.typeid)), (count(h_3.typeid)), (count(h_4.typeid))
  • Merge Cond: (h.aid = h_4.aid)
  • Buffers: shared hit=743035
10. 0.011 6,969.031 ↑ 3.1 20 1

Merge Left Join (cost=1,026,351.18..1,026,363.31 rows=63 width=34) (actual time=6,968.941..6,969.031 rows=20 loops=1)

  • Output: (count(h.typeid)), h.aid, (count(h_1.typeid)), (count(h_2.typeid)), (count(h_3.typeid))
  • Merge Cond: (h.aid = h_3.aid)
  • Buffers: shared hit=594379
11. 0.012 4,903.137 ↑ 3.1 20 1

Merge Left Join (cost=770,653.45..770,662.27 rows=63 width=26) (actual time=4,903.078..4,903.137 rows=20 loops=1)

  • Output: (count(h.typeid)), h.aid, (count(h_1.typeid)), (count(h_2.typeid))
  • Merge Cond: (h.aid = h_2.aid)
  • Buffers: shared hit=445805
12. 0.017 3,657.719 ↑ 3.1 20 1

Merge Left Join (cost=514,713.54..514,719.05 rows=63 width=18) (actual time=3,657.678..3,657.719 rows=20 loops=1)

  • Output: (count(h.typeid)), h.aid, (count(h_1.typeid))
  • Merge Cond: (h.aid = h_1.aid)
  • Buffers: shared hit=297149
13. 0.016 1,409.053 ↑ 3.1 20 1

Finalize GroupAggregate (cost=257,192.81..257,194.39 rows=63 width=10) (actual time=1,409.038..1,409.053 rows=20 loops=1)

  • Output: h.aid, count(h.typeid)
  • Group Key: h.aid
  • Buffers: shared hit=148575
14. 0.000 1,409.037 ↑ 3.2 39 1

Sort (cost=257,192.81..257,193.13 rows=126 width=10) (actual time=1,409.033..1,409.037 rows=39 loops=1)

  • Output: h.aid, (PARTIAL count(h.typeid))
  • Sort Key: h.aid
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=148575
15. 9.408 1,409.112 ↑ 3.2 39 1

Gather (cost=257,016.26..257,188.42 rows=126 width=10) (actual time=1,401.404..1,409.112 rows=39 loops=1)

  • Output: h.aid, (PARTIAL count(h.typeid))
  • Workers Planned: 2
  • Workers Launched: 1
  • Buffers: shared hit=148575
16. 25.644 1,399.704 ↑ 3.1 20 2 / 2

Partial GroupAggregate (cost=256,016.26..256,175.82 rows=63 width=10) (actual time=1,392.876..1,399.704 rows=20 loops=2)

  • Output: h.aid, PARTIAL count(h.typeid)
  • Group Key: h.aid
  • Buffers: shared hit=148575
  • Worker 0: actual time=1384.933..1391.062 rows=20 loops=1
  • Buffers: shared hit=67371
17. 53.400 1,374.060 ↓ 10.2 215,828 2 / 2

Sort (cost=256,016.26..256,069.24 rows=21,190 width=7) (actual time=1,355.339..1,374.060 rows=215,828 loops=2)

  • Output: h.aid, h.typeid
  • Sort Key: h.aid
  • Sort Method: quicksort Memory: 17493kB
  • Buffers: shared hit=148575
  • Worker 0: actual time=1351.196..1370.939 rows=189562 loops=1
  • Buffers: shared hit=67371
18. 1,320.660 1,320.660 ↓ 10.2 215,828 2 / 2

Parallel Seq Scan on delta.collecting_history h (cost=0.00..254,493.65 rows=21,190 width=7) (actual time=1,190.617..1,320.660 rows=215,828 loops=2)

  • Output: h.aid, h.typeid
  • Filter: (((h.created)::date >= '2020-03-01'::date) AND ((h.created)::date < ('now'::cstring)::date))
  • Rows Removed by Filter: 4876030
  • Buffers: shared hit=148546
  • Worker 0: actual time=1186.503..1318.184 rows=189562 loops=1
  • Buffers: shared hit=67342
19. 0.006 2,248.649 ↑ 3.1 20 1

Materialize (cost=257,520.73..257,523.09 rows=63 width=10) (actual time=2,248.633..2,248.649 rows=20 loops=1)

  • Output: h_1.aid, (count(h_1.typeid))
  • Buffers: shared hit=148574
20. 0.014 2,248.643 ↑ 3.1 20 1

Finalize GroupAggregate (cost=257,520.73..257,522.30 rows=63 width=10) (actual time=2,248.629..2,248.643 rows=20 loops=1)

  • Output: h_1.aid, count(h_1.typeid)
  • Group Key: h_1.aid
  • Buffers: shared hit=148574
21. 0.000 2,248.629 ↑ 6.3 20 1

Sort (cost=257,520.73..257,521.04 rows=126 width=10) (actual time=2,248.624..2,248.629 rows=20 loops=1)

  • Output: h_1.aid, (PARTIAL count(h_1.typeid))
  • Sort Key: h_1.aid
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=148574
22. 0.851 2,248.664 ↑ 6.3 20 1

Gather (cost=257,347.43..257,516.33 rows=126 width=10) (actual time=2,242.405..2,248.664 rows=20 loops=1)

  • Output: h_1.aid, (PARTIAL count(h_1.typeid))
  • Workers Planned: 2
  • Workers Launched: 0
  • Buffers: shared hit=148574
23. 45.818 2,247.813 ↑ 3.1 20 1

Partial GroupAggregate (cost=256,347.43..256,503.73 rows=63 width=10) (actual time=2,241.619..2,247.813 rows=20 loops=1)

  • Output: h_1.aid, PARTIAL count(h_1.typeid)
  • Group Key: h_1.aid
  • Buffers: shared hit=148574
24. 97.083 2,201.995 ↓ 19.2 398,368 1

Sort (cost=256,347.43..256,399.32 rows=20,756 width=7) (actual time=2,173.502..2,201.995 rows=398,368 loops=1)

  • Output: h_1.aid, h_1.typeid
  • Sort Key: h_1.aid
  • Sort Method: quicksort Memory: 30962kB
  • Buffers: shared hit=148574
25. 119.562 2,104.912 ↓ 19.2 398,368 1

Hash Join (cost=78.48..254,859.10 rows=20,756 width=7) (actual time=1,762.560..2,104.912 rows=398,368 loops=1)

  • Output: h_1.aid, h_1.typeid
  • Hash Cond: (h_1.typeid = cr.id)
  • Buffers: shared hit=148574
26. 1,984.705 1,984.705 ↓ 20.4 431,655 1

Parallel Seq Scan on delta.collecting_history h_1 (cost=0.00..254,493.65 rows=21,190 width=7) (actual time=1,761.895..1,984.705 rows=431,655 loops=1)

  • Output: h_1.id, h_1.lid, h_1.cid, h_1.aid, h_1.typeid, h_1.text, h_1.created, h_1.notification, h_1.paymentdate, h_1.paymentvalue, h_1.ispayment, h_1.isnotify, h_1.isviewved, h_1.isdeclared, h_1.isskiptracing, h_1.declareddate, h_1.declaredvalue, h_1.scenario, h_1.dr, h_1.phoneid, h_1.addressid, h_1.dictid, h_1.hasfields, h_1.hasevents, h_1.moodindicatorid
  • Filter: (((h_1.created)::date >= '2020-03-01'::date) AND ((h_1.created)::date < ('now'::cstring)::date))
  • Rows Removed by Filter: 9752059
  • Buffers: shared hit=148546
27. 0.157 0.645 ↑ 1.0 1,175 1

Hash (cost=63.69..63.69 rows=1,183 width=5) (actual time=0.645..0.645 rows=1,175 loops=1)

  • Output: cr.id
  • Buckets: 2048 Batches: 1 Memory Usage: 59kB
  • Buffers: shared hit=28
28. 0.245 0.488 ↑ 1.0 1,175 1

Hash Join (cost=14.25..63.69 rows=1,183 width=5) (actual time=0.124..0.488 rows=1,175 loops=1)

  • Output: cr.id
  • Hash Cond: (cr.typeid = ct.id)
  • Buffers: shared hit=28
29. 0.137 0.137 ↑ 1.0 1,208 1

Seq Scan on delta.collecting_contactresult cr (cost=0.00..33.08 rows=1,208 width=7) (actual time=0.009..0.137 rows=1,208 loops=1)

  • Output: cr.id, cr.typeid, cr.caption, cr.dataselect, cr.isnextcontact, cr.ispositive, cr.isdefault, cr.ishidden, cr.code, cr.fs, cr.dlfs, cr.showpromiss, cr.showdeclared, cr.showdelayreason, cr.showskip, cr.isscenarioauto, cr.ispaymentrequired, cr.isdeclaredrequired, cr.iscontwithrequired, cr.scriptcount, cr.scriptstate, cr.nextcontactmin, cr.nextcontactmax, cr.nextpromissmin, cr.nextpromissmax, cr.nextpaymentmin, cr.nextpaymentmax, cr.promisspaymentdays, cr.fields, cr.isconnect, cr.ismotivation, cr.showmoodindicator, cr.isphoneweight, cr.ispromiseschedule, cr.iscommentrequired, cr.fieldsrequired, cr.fieldsview
  • Buffers: shared hit=21
30. 0.029 0.106 ↑ 1.0 287 1

Hash (cost=10.66..10.66 rows=287 width=2) (actual time=0.106..0.106 rows=287 loops=1)

  • Output: ct.id
  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
  • Buffers: shared hit=7
31. 0.077 0.077 ↑ 1.0 287 1

Seq Scan on delta.collecting_contacttype ct (cost=0.00..10.66 rows=287 width=2) (actual time=0.011..0.077 rows=287 loops=1)

  • Output: ct.id
  • Filter: (ct.caption !~~ 'Негативный контакт'::text)
  • Rows Removed by Filter: 6
  • Buffers: shared hit=7
32. 0.006 1,245.406 ↑ 4.8 13 1

Materialize (cost=255,939.91..255,942.27 rows=63 width=10) (actual time=1,245.395..1,245.406 rows=13 loops=1)

  • Output: h_2.aid, (count(h_2.typeid))
  • Buffers: shared hit=148656
33. 0.011 1,245.400 ↑ 4.8 13 1

Finalize GroupAggregate (cost=255,939.91..255,941.48 rows=63 width=10) (actual time=1,245.390..1,245.400 rows=13 loops=1)

  • Output: h_2.aid, count(h_2.typeid)
  • Group Key: h_2.aid
  • Buffers: shared hit=148656
34. 0.000 1,245.389 ↑ 4.8 26 1

Sort (cost=255,939.91..255,940.22 rows=126 width=10) (actual time=1,245.386..1,245.389 rows=26 loops=1)

  • Output: h_2.aid, (PARTIAL count(h_2.typeid))
  • Sort Key: h_2.aid
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=148656
35. 5.897 1,245.426 ↑ 4.8 26 1

Gather (cost=255,893.54..255,935.51 rows=126 width=10) (actual time=1,244.836..1,245.426 rows=26 loops=1)

  • Output: h_2.aid, (PARTIAL count(h_2.typeid))
  • Workers Planned: 2
  • Workers Launched: 1
  • Buffers: shared hit=148656
36. 0.273 1,239.529 ↑ 4.8 13 2 / 2

Partial GroupAggregate (cost=254,893.54..254,922.91 rows=63 width=10) (actual time=1,239.155..1,239.529 rows=13 loops=2)

  • Output: h_2.aid, PARTIAL count(h_2.typeid)
  • Group Key: h_2.aid
  • Buffers: shared hit=148656
  • Worker 0: actual time=1234.326..1234.655 rows=13 loops=1
  • Buffers: shared hit=63798
37. 0.817 1,239.256 ↑ 1.7 2,194 2 / 2

Sort (cost=254,893.54..254,903.12 rows=3,833 width=7) (actual time=1,239.056..1,239.256 rows=2,194 loops=2)

  • Output: h_2.aid, h_2.typeid
  • Sort Key: h_2.aid
  • Sort Method: quicksort Memory: 207kB
  • Buffers: shared hit=148656
  • Worker 0: actual time=1234.232..1234.391 rows=2041 loops=1
  • Buffers: shared hit=63798
38. 27.315 1,238.439 ↑ 1.7 2,194 2 / 2

Hash Join (cost=53.86..254,665.39 rows=3,833 width=7) (actual time=1,087.346..1,238.439 rows=2,194 loops=2)

  • Output: h_2.aid, h_2.typeid
  • Hash Cond: (h_2.typeid = cr_1.id)
  • Buffers: shared hit=148627
  • Worker 0: actual time=1083.359..1233.526 rows=2041 loops=1
  • Buffers: shared hit=63769
39. 1,209.987 1,209.987 ↓ 10.2 215,828 2 / 2

Parallel Seq Scan on delta.collecting_history h_2 (cost=0.00..254,493.65 rows=21,190 width=7) (actual time=1,085.646..1,209.987 rows=215,828 loops=2)

  • Output: h_2.id, h_2.lid, h_2.cid, h_2.aid, h_2.typeid, h_2.text, h_2.created, h_2.notification, h_2.paymentdate, h_2.paymentvalue, h_2.ispayment, h_2.isnotify, h_2.isviewved, h_2.isdeclared, h_2.isskiptracing, h_2.declareddate, h_2.declaredvalue, h_2.scenario, h_2.dr, h_2.phoneid, h_2.addressid, h_2.dictid, h_2.hasfields, h_2.hasevents, h_2.moodindicatorid
  • Filter: (((h_2.created)::date >= '2020-03-01'::date) AND ((h_2.created)::date < ('now'::cstring)::date))
  • Rows Removed by Filter: 4876030
  • Buffers: shared hit=148546
  • Worker 0: actual time=1080.808..1207.525 rows=199240 loops=1
  • Buffers: shared hit=63716
40. 0.121 1.137 ↓ 1.3 275 2 / 2

Hash (cost=51.12..51.12 rows=219 width=5) (actual time=1.137..1.137 rows=275 loops=2)

  • Output: cr_1.id
  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
  • Buffers: shared hit=67
  • Worker 0: actual time=1.509..1.509 rows=275 loops=1
  • Buffers: shared hit=39
41. 0.346 1.016 ↓ 1.3 275 2 / 2

Hash Join (cost=11.32..51.12 rows=219 width=5) (actual time=0.439..1.016 rows=275 loops=2)

  • Output: cr_1.id
  • Hash Cond: (cr_1.typeid = ct_1.id)
  • Buffers: shared hit=67
  • Worker 0: actual time=0.609..1.334 rows=275 loops=1
  • Buffers: shared hit=39
42. 0.330 0.330 ↑ 1.0 1,208 2 / 2

Seq Scan on delta.collecting_contactresult cr_1 (cost=0.00..33.08 rows=1,208 width=7) (actual time=0.032..0.330 rows=1,208 loops=2)

  • Output: cr_1.id, cr_1.typeid, cr_1.caption, cr_1.dataselect, cr_1.isnextcontact, cr_1.ispositive, cr_1.isdefault, cr_1.ishidden, cr_1.code, cr_1.fs, cr_1.dlfs, cr_1.showpromiss, cr_1.showdeclared, cr_1.showdelayreason, cr_1.showskip, cr_1.isscenarioauto, cr_1.ispaymentrequired, cr_1.isdeclaredrequired, cr_1.iscontwithrequired, cr_1.scriptcount, cr_1.scriptstate, cr_1.nextcontactmin, cr_1.nextcontactmax, cr_1.nextpromissmin, cr_1.nextpromissmax, cr_1.nextpaymentmin, cr_1.nextpaymentmax, cr_1.promisspaymentdays, cr_1.fields, cr_1.isconnect, cr_1.ismotivation, cr_1.showmoodindicator, cr_1.isphoneweight, cr_1.ispromiseschedule, cr_1.iscommentrequired, cr_1.fieldsrequired, cr_1.fieldsview
  • Buffers: shared hit=42
  • Worker 0: actual time=0.049..0.420 rows=1208 loops=1
  • Buffers: shared hit=21
43. 0.014 0.340 ↓ 1.0 54 2 / 2

Hash (cost=10.66..10.66 rows=53 width=2) (actual time=0.340..0.340 rows=54 loops=2)

  • Output: ct_1.id
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=14
  • Worker 0: actual time=0.457..0.457 rows=54 loops=1
  • Buffers: shared hit=7
44. 0.326 0.326 ↓ 1.0 54 2 / 2

Seq Scan on delta.collecting_contacttype ct_1 (cost=0.00..10.66 rows=53 width=2) (actual time=0.041..0.326 rows=54 loops=2)

  • Output: ct_1.id
  • Filter: (ct_1.captionpath ~~ '%клиент%'::text)
  • Rows Removed by Filter: 239
  • Buffers: shared hit=14
  • Worker 0: actual time=0.053..0.440 rows=54 loops=1
  • Buffers: shared hit=7
45. 0.006 2,065.883 ↑ 4.8 13 1

Materialize (cost=255,697.73..255,700.10 rows=63 width=10) (actual time=2,065.859..2,065.883 rows=13 loops=1)

  • Output: h_3.aid, (count(h_3.typeid))
  • Buffers: shared hit=148574
46. 0.021 2,065.877 ↑ 4.8 13 1

Finalize GroupAggregate (cost=255,697.73..255,699.31 rows=63 width=10) (actual time=2,065.857..2,065.877 rows=13 loops=1)

  • Output: h_3.aid, count(h_3.typeid)
  • Group Key: h_3.aid
  • Buffers: shared hit=148574
47. 0.000 2,065.856 ↑ 9.7 13 1

Sort (cost=255,697.73..255,698.05 rows=126 width=10) (actual time=2,065.854..2,065.856 rows=13 loops=1)

  • Output: h_3.aid, (PARTIAL count(h_3.typeid))
  • Sort Key: h_3.aid
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=148574
48. 0.848 2,065.879 ↑ 9.7 13 1

Gather (cost=255,673.60..255,693.34 rows=126 width=10) (actual time=2,065.428..2,065.879 rows=13 loops=1)

  • Output: h_3.aid, (PARTIAL count(h_3.typeid))
  • Workers Planned: 2
  • Workers Launched: 0
  • Buffers: shared hit=148574
49. 0.294 2,065.031 ↑ 4.8 13 1

Partial GroupAggregate (cost=254,673.60..254,680.74 rows=63 width=10) (actual time=2,064.628..2,065.031 rows=13 loops=1)

  • Output: h_3.aid, PARTIAL count(h_3.typeid)
  • Group Key: h_3.aid
  • Buffers: shared hit=148574
50. 0.736 2,064.737 ↓ 3.3 2,831 1

Sort (cost=254,673.60..254,675.77 rows=868 width=7) (actual time=2,064.504..2,064.737 rows=2,831 loops=1)

  • Output: h_3.aid, h_3.typeid
  • Sort Key: h_3.aid
  • Sort Method: quicksort Memory: 229kB
  • Buffers: shared hit=148574
51. 47.720 2,064.001 ↓ 3.3 2,831 1

Hash Join (cost=49.52..254,631.23 rows=868 width=7) (actual time=1,796.667..2,064.001 rows=2,831 loops=1)

  • Output: h_3.aid, h_3.typeid
  • Hash Cond: (h_3.typeid = cr_2.id)
  • Buffers: shared hit=148574
52. 2,015.897 2,015.897 ↓ 20.4 431,655 1

Parallel Seq Scan on delta.collecting_history h_3 (cost=0.00..254,493.65 rows=21,190 width=7) (actual time=1,796.001..2,015.897 rows=431,655 loops=1)

  • Output: h_3.id, h_3.lid, h_3.cid, h_3.aid, h_3.typeid, h_3.text, h_3.created, h_3.notification, h_3.paymentdate, h_3.paymentvalue, h_3.ispayment, h_3.isnotify, h_3.isviewved, h_3.isdeclared, h_3.isskiptracing, h_3.declareddate, h_3.declaredvalue, h_3.scenario, h_3.dr, h_3.phoneid, h_3.addressid, h_3.dictid, h_3.hasfields, h_3.hasevents, h_3.moodindicatorid
  • Filter: (((h_3.created)::date >= '2020-03-01'::date) AND ((h_3.created)::date < ('now'::cstring)::date))
  • Rows Removed by Filter: 9752059
  • Buffers: shared hit=148546
53. 0.008 0.384 ↑ 1.1 44 1

Hash (cost=48.91..48.91 rows=49 width=5) (actual time=0.384..0.384 rows=44 loops=1)

  • Output: cr_2.id
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=28
54. 0.113 0.376 ↑ 1.1 44 1

Hash Join (cost=10.81..48.91 rows=49 width=5) (actual time=0.134..0.376 rows=44 loops=1)

  • Output: cr_2.id
  • Hash Cond: (cr_2.typeid = ct_2.id)
  • Buffers: shared hit=28
55. 0.164 0.164 ↑ 1.0 1,208 1

Seq Scan on delta.collecting_contactresult cr_2 (cost=0.00..33.08 rows=1,208 width=7) (actual time=0.010..0.164 rows=1,208 loops=1)

  • Output: cr_2.id, cr_2.typeid, cr_2.caption, cr_2.dataselect, cr_2.isnextcontact, cr_2.ispositive, cr_2.isdefault, cr_2.ishidden, cr_2.code, cr_2.fs, cr_2.dlfs, cr_2.showpromiss, cr_2.showdeclared, cr_2.showdelayreason, cr_2.showskip, cr_2.isscenarioauto, cr_2.ispaymentrequired, cr_2.isdeclaredrequired, cr_2.iscontwithrequired, cr_2.scriptcount, cr_2.scriptstate, cr_2.nextcontactmin, cr_2.nextcontactmax, cr_2.nextpromissmin, cr_2.nextpromissmax, cr_2.nextpaymentmin, cr_2.nextpaymentmax, cr_2.promisspaymentdays, cr_2.fields, cr_2.isconnect, cr_2.ismotivation, cr_2.showmoodindicator, cr_2.isphoneweight, cr_2.ispromiseschedule, cr_2.iscommentrequired, cr_2.fieldsrequired, cr_2.fieldsview
  • Buffers: shared hit=21
56. 0.003 0.099 ↑ 1.0 12 1

Hash (cost=10.66..10.66 rows=12 width=2) (actual time=0.099..0.099 rows=12 loops=1)

  • Output: ct_2.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=7
57. 0.096 0.096 ↑ 1.0 12 1

Seq Scan on delta.collecting_contacttype ct_2 (cost=0.00..10.66 rows=12 width=2) (actual time=0.016..0.096 rows=12 loops=1)

  • Output: ct_2.id
  • Filter: (ct_2.captionpath ~~ '%3-е лицо, знающее должника%'::text)
  • Rows Removed by Filter: 281
  • Buffers: shared hit=7
58. 0.002 1,286.858 ↑ 4.8 13 1

Materialize (cost=255,656.21..255,658.57 rows=63 width=10) (actual time=1,286.846..1,286.858 rows=13 loops=1)

  • Output: h_4.aid, (count(h_4.typeid))
  • Buffers: shared hit=148656
59. 0.009 1,286.856 ↑ 4.8 13 1

Finalize GroupAggregate (cost=255,656.21..255,657.78 rows=63 width=10) (actual time=1,286.844..1,286.856 rows=13 loops=1)

  • Output: h_4.aid, count(h_4.typeid)
  • Group Key: h_4.aid
  • Buffers: shared hit=148656
60. 0.000 1,286.847 ↑ 4.8 26 1

Sort (cost=255,656.21..255,656.52 rows=126 width=10) (actual time=1,286.841..1,286.847 rows=26 loops=1)

  • Output: h_4.aid, (PARTIAL count(h_4.typeid))
  • Sort Key: h_4.aid
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=148656
61. 3.801 1,286.869 ↑ 4.8 26 1

Gather (cost=255,636.41..255,651.81 rows=126 width=10) (actual time=1,286.464..1,286.869 rows=26 loops=1)

  • Output: h_4.aid, (PARTIAL count(h_4.typeid))
  • Workers Planned: 2
  • Workers Launched: 1
  • Buffers: shared hit=148656
62. 0.132 1,283.068 ↑ 4.8 13 2 / 2

Partial GroupAggregate (cost=254,636.41..254,639.21 rows=63 width=10) (actual time=1,282.895..1,283.068 rows=13 loops=2)

  • Output: h_4.aid, PARTIAL count(h_4.typeid)
  • Group Key: h_4.aid
  • Buffers: shared hit=148656
  • Worker 0: actual time=1280.127..1280.289 rows=13 loops=1
  • Buffers: shared hit=62364
63. 0.470 1,282.936 ↓ 3.5 1,015 2 / 2

Sort (cost=254,636.41..254,637.14 rows=289 width=7) (actual time=1,282.856..1,282.936 rows=1,015 loops=2)

  • Output: h_4.aid, h_4.typeid
  • Sort Key: h_4.aid
  • Sort Method: quicksort Memory: 102kB
  • Buffers: shared hit=148656
  • Worker 0: actual time=1280.089..1280.166 rows=882 loops=1
  • Buffers: shared hit=62364
64. 26.425 1,282.466 ↓ 3.5 1,015 2 / 2

Hash Join (cost=48.68..254,624.60 rows=289 width=7) (actual time=1,133.721..1,282.466 rows=1,015 loops=2)

  • Output: h_4.aid, h_4.typeid
  • Hash Cond: (h_4.typeid = cr_3.id)
  • Buffers: shared hit=148627
  • Worker 0: actual time=1130.988..1279.577 rows=882 loops=1
  • Buffers: shared hit=62335
65. 1,255.630 1,255.630 ↓ 10.2 215,828 2 / 2

Parallel Seq Scan on delta.collecting_history h_4 (cost=0.00..254,493.65 rows=21,190 width=7) (actual time=1,133.020..1,255.630 rows=215,828 loops=2)

  • Output: h_4.id, h_4.lid, h_4.cid, h_4.aid, h_4.typeid, h_4.text, h_4.created, h_4.notification, h_4.paymentdate, h_4.paymentvalue, h_4.ispayment, h_4.isnotify, h_4.isviewved, h_4.isdeclared, h_4.isskiptracing, h_4.declareddate, h_4.declaredvalue, h_4.scenario, h_4.dr, h_4.phoneid, h_4.addressid, h_4.dictid, h_4.hasfields, h_4.hasevents, h_4.moodindicatorid
  • Filter: (((h_4.created)::date >= '2020-03-01'::date) AND ((h_4.created)::date < ('now'::cstring)::date))
  • Rows Removed by Filter: 4876030
  • Buffers: shared hit=148546
  • Worker 0: actual time=1130.196..1253.866 rows=204232 loops=1
  • Buffers: shared hit=62282
66. 0.010 0.411 ↑ 2.0 8 2 / 2

Hash (cost=48.48..48.48 rows=16 width=5) (actual time=0.411..0.411 rows=8 loops=2)

  • Output: cr_3.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=67
  • Worker 0: actual time=0.464..0.464 rows=8 loops=1
  • Buffers: shared hit=39
67. 0.123 0.401 ↑ 2.0 8 2 / 2

Hash Join (cost=10.71..48.48 rows=16 width=5) (actual time=0.173..0.401 rows=8 loops=2)

  • Output: cr_3.id
  • Hash Cond: (cr_3.typeid = ct_3.id)
  • Buffers: shared hit=67
  • Worker 0: actual time=0.209..0.448 rows=8 loops=1
  • Buffers: shared hit=39
68. 0.168 0.168 ↑ 1.0 1,208 2 / 2

Seq Scan on delta.collecting_contactresult cr_3 (cost=0.00..33.08 rows=1,208 width=7) (actual time=0.021..0.168 rows=1,208 loops=2)

  • Output: cr_3.id, cr_3.typeid, cr_3.caption, cr_3.dataselect, cr_3.isnextcontact, cr_3.ispositive, cr_3.isdefault, cr_3.ishidden, cr_3.code, cr_3.fs, cr_3.dlfs, cr_3.showpromiss, cr_3.showdeclared, cr_3.showdelayreason, cr_3.showskip, cr_3.isscenarioauto, cr_3.ispaymentrequired, cr_3.isdeclaredrequired, cr_3.iscontwithrequired, cr_3.scriptcount, cr_3.scriptstate, cr_3.nextcontactmin, cr_3.nextcontactmax, cr_3.nextpromissmin, cr_3.nextpromissmax, cr_3.nextpaymentmin, cr_3.nextpaymentmax, cr_3.promisspaymentdays, cr_3.fields, cr_3.isconnect, cr_3.ismotivation, cr_3.showmoodindicator, cr_3.isphoneweight, cr_3.ispromiseschedule, cr_3.iscommentrequired, cr_3.fieldsrequired, cr_3.fieldsview
  • Buffers: shared hit=42
  • Worker 0: actual time=0.030..0.198 rows=1208 loops=1
  • Buffers: shared hit=21
69. 0.004 0.110 ↑ 1.0 4 2 / 2

Hash (cost=10.66..10.66 rows=4 width=2) (actual time=0.110..0.110 rows=4 loops=2)

  • Output: ct_3.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=14
  • Worker 0: actual time=0.120..0.120 rows=4 loops=1
  • Buffers: shared hit=7
70. 0.106 0.106 ↑ 1.0 4 2 / 2

Seq Scan on delta.collecting_contacttype ct_3 (cost=0.00..10.66 rows=4 width=2) (actual time=0.022..0.106 rows=4 loops=2)

  • Output: ct_3.id
  • Filter: (ct_3.captionpath ~~ '%3-е лицо, не знающее%'::text)
  • Rows Removed by Filter: 289
  • Buffers: shared hit=14
  • Worker 0: actual time=0.026..0.115 rows=4 loops=1
  • Buffers: shared hit=7
71. 0.005 2,380.353 ↑ 3.1 20 1

Materialize (cost=257,066.98..257,069.34 rows=63 width=10) (actual time=2,380.341..2,380.353 rows=20 loops=1)

  • Output: h_5.aid, (count(h_5.typeid))
  • Buffers: shared hit=148574
72. 0.009 2,380.348 ↑ 3.1 20 1

Finalize GroupAggregate (cost=257,066.98..257,068.55 rows=63 width=10) (actual time=2,380.338..2,380.348 rows=20 loops=1)

  • Output: h_5.aid, count(h_5.typeid)
  • Group Key: h_5.aid
  • Buffers: shared hit=148574
73. 0.000 2,380.339 ↑ 6.3 20 1

Sort (cost=257,066.98..257,067.29 rows=126 width=10) (actual time=2,380.335..2,380.339 rows=20 loops=1)

  • Output: h_5.aid, (PARTIAL count(h_5.typeid))
  • Sort Key: h_5.aid
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=148574
74. 0.891 2,380.359 ↑ 6.3 20 1

Gather (cost=256,928.94..257,062.58 rows=126 width=10) (actual time=2,377.229..2,380.359 rows=20 loops=1)

  • Output: h_5.aid, (PARTIAL count(h_5.typeid))
  • Workers Planned: 2
  • Workers Launched: 0
  • Buffers: shared hit=148574
75. 56.020 2,379.468 ↑ 3.1 20 1

Partial GroupAggregate (cost=255,928.94..256,049.98 rows=63 width=10) (actual time=2,376.388..2,379.468 rows=20 loops=1)

  • Output: h_5.aid, PARTIAL count(h_5.typeid)
  • Group Key: h_5.aid
  • Buffers: shared hit=148574
76. 100.262 2,323.448 ↓ 24.2 389,118 1

Sort (cost=255,928.94..255,969.08 rows=16,055 width=7) (actual time=2,288.419..2,323.448 rows=389,118 loops=1)

  • Output: h_5.aid, h_5.typeid
  • Sort Key: h_5.aid
  • Sort Method: quicksort Memory: 30528kB
  • Buffers: shared hit=148574
77. 129.314 2,223.186 ↓ 24.2 389,118 1

Hash Join (cost=73.83..254,807.44 rows=16,055 width=7) (actual time=1,848.933..2,223.186 rows=389,118 loops=1)

  • Output: h_5.aid, h_5.typeid
  • Hash Cond: (h_5.typeid = cr_4.id)
  • Buffers: shared hit=148574
78. 2,093.110 2,093.110 ↓ 20.4 431,655 1

Parallel Seq Scan on delta.collecting_history h_5 (cost=0.00..254,493.65 rows=21,190 width=7) (actual time=1,848.153..2,093.110 rows=431,655 loops=1)

  • Output: h_5.id, h_5.lid, h_5.cid, h_5.aid, h_5.typeid, h_5.text, h_5.created, h_5.notification, h_5.paymentdate, h_5.paymentvalue, h_5.ispayment, h_5.isnotify, h_5.isviewved, h_5.isdeclared, h_5.isskiptracing, h_5.declareddate, h_5.declaredvalue, h_5.scenario, h_5.dr, h_5.phoneid, h_5.addressid, h_5.dictid, h_5.hasfields, h_5.hasevents, h_5.moodindicatorid
  • Filter: (((h_5.created)::date >= '2020-03-01'::date) AND ((h_5.created)::date < ('now'::cstring)::date))
  • Rows Removed by Filter: 9752059
  • Buffers: shared hit=148546
79. 0.119 0.762 ↑ 1.1 848 1

Hash (cost=62.39..62.39 rows=915 width=5) (actual time=0.762..0.762 rows=848 loops=1)

  • Output: cr_4.id
  • Buckets: 1024 Batches: 1 Memory Usage: 39kB
  • Buffers: shared hit=28
80. 0.199 0.643 ↑ 1.1 848 1

Hash Join (cost=15.63..62.39 rows=915 width=5) (actual time=0.322..0.643 rows=848 loops=1)

  • Output: cr_4.id
  • Hash Cond: (cr_4.typeid = ct_4.id)
  • Buffers: shared hit=28
81. 0.141 0.141 ↑ 1.0 1,208 1

Seq Scan on delta.collecting_contactresult cr_4 (cost=0.00..33.08 rows=1,208 width=7) (actual time=0.010..0.141 rows=1,208 loops=1)

  • Output: cr_4.id, cr_4.typeid, cr_4.caption, cr_4.dataselect, cr_4.isnextcontact, cr_4.ispositive, cr_4.isdefault, cr_4.ishidden, cr_4.code, cr_4.fs, cr_4.dlfs, cr_4.showpromiss, cr_4.showdeclared, cr_4.showdelayreason, cr_4.showskip, cr_4.isscenarioauto, cr_4.ispaymentrequired, cr_4.isdeclaredrequired, cr_4.iscontwithrequired, cr_4.scriptcount, cr_4.scriptstate, cr_4.nextcontactmin, cr_4.nextcontactmax, cr_4.nextpromissmin, cr_4.nextpromissmax, cr_4.nextpaymentmin, cr_4.nextpaymentmax, cr_4.promisspaymentdays, cr_4.fields, cr_4.isconnect, cr_4.ismotivation, cr_4.showmoodindicator, cr_4.isphoneweight, cr_4.ispromiseschedule, cr_4.iscommentrequired, cr_4.fieldsrequired, cr_4.fieldsview
  • Buffers: shared hit=21
82. 0.028 0.303 ↑ 1.0 217 1

Hash (cost=12.86..12.86 rows=222 width=2) (actual time=0.303..0.303 rows=217 loops=1)

  • Output: ct_4.id
  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
  • Buffers: shared hit=7
83. 0.275 0.275 ↑ 1.0 217 1

Seq Scan on delta.collecting_contacttype ct_4 (cost=0.00..12.86 rows=222 width=2) (actual time=0.012..0.275 rows=217 loops=1)

  • Output: ct_4.id
  • Filter: ((ct_4.captionpath !~~ '%3-е лицо, не знающее%'::text) AND (ct_4.captionpath !~~ '%3-е лицо, знающее должника%'::text) AND (ct_4.captionpath !~~ '%клиент%'::text) AND (ct_4.caption !~~ 'Негативный контакт'::text))
  • Rows Removed by Filter: 76
  • Buffers: shared hit=7
84. 0.006 11.232 ↑ 5.2 12 1

Materialize (cost=2,102.84..2,106.82 rows=63 width=20) (actual time=11.011..11.232 rows=12 loops=1)

  • Output: pp.aid, (sum(pp.totalamountpromise)), (count(pp.id))
  • Buffers: shared hit=807
85. 0.188 11.226 ↑ 5.2 12 1

GroupAggregate (cost=2,102.84..2,106.04 rows=63 width=20) (actual time=11.007..11.226 rows=12 loops=1)

  • Output: pp.aid, sum(pp.totalamountpromise), count(pp.id)
  • Group Key: pp.aid
  • Buffers: shared hit=807
86. 0.296 11.038 ↓ 4.9 1,269 1

Sort (cost=2,102.84..2,103.48 rows=257 width=16) (actual time=10.969..11.038 rows=1,269 loops=1)

  • Output: pp.aid, pp.totalamountpromise, pp.id
  • Sort Key: pp.aid
  • Sort Method: quicksort Memory: 148kB
  • Buffers: shared hit=807
87. 10.742 10.742 ↓ 4.9 1,269 1

Seq Scan on delta.collecting_promisepayment pp (cost=0.00..2,092.55 rows=257 width=16) (actual time=10.050..10.742 rows=1,269 loops=1)

  • Output: pp.aid, pp.totalamountpromise, pp.id
  • Filter: (((pp.created)::date >= '2020-03-01'::date) AND ((pp.created)::date < ('now'::cstring)::date))
  • Rows Removed by Filter: 50153
  • Buffers: shared hit=807
88. 0.013 0.040 ↓ 6.0 6 20

Materialize (cost=0.00..1.35 rows=1 width=37) (actual time=0.001..0.002 rows=6 loops=20)

  • Output: collection_oper_individual_plan.plan_promise, collection_oper_individual_plan.name
  • Buffers: shared hit=1
89. 0.027 0.027 ↓ 6.0 6 1

Seq Scan on analysts.collection_oper_individual_plan (cost=0.00..1.35 rows=1 width=37) (actual time=0.024..0.027 rows=6 loops=1)

  • Output: collection_oper_individual_plan.plan_promise, collection_oper_individual_plan.name
  • Filter: ((collection_oper_individual_plan.month >= '2020-03-01'::date) AND (collection_oper_individual_plan.month <= '2020-03-21'::date) AND (collection_oper_individual_plan.bucket = '6-20'::text))
  • Rows Removed by Filter: 25
  • Buffers: shared hit=1
90. 0.930 3,720.738 ↑ 8.1 12 6

GroupAggregate (cost=153,238.21..153,241.10 rows=97 width=38) (actual time=619.910..620.123 rows=12 loops=6)

  • Output: u_1.name, sum(pp_1.totalamountpromise)
  • Group Key: u_1.name
  • Buffers: shared hit=269780
91. 0.911 3,719.808 ↓ 5.1 1,313 6

Sort (cost=153,238.21..153,238.85 rows=257 width=38) (actual time=619.892..619.968 rows=1,313 loops=6)

  • Output: u_1.name, pp_1.totalamountpromise
  • Sort Key: u_1.name
  • Sort Method: quicksort Memory: 159kB
  • Buffers: shared hit=269780
92. 0.234 3,718.897 ↓ 5.1 1,313 1

Hash Left Join (cost=151,121.22..153,227.92 rows=257 width=38) (actual time=3,717.038..3,718.897 rows=1,313 loops=1)

  • Output: u_1.name, pp_1.totalamountpromise
  • Hash Cond: (pp_1.id = pay.id)
  • Buffers: shared hit=269780
93. 1.115 108.512 ↓ 5.1 1,313 1

Hash Left Join (cost=12,745.90..14,845.51 rows=257 width=42) (actual time=106.862..108.512 rows=1,313 loops=1)

  • Output: pp_1.totalamountpromise, pp_1.id, u_1.name
  • Hash Cond: (pp_1.loanid = l.id)
  • Buffers: shared hit=9524
94. 0.251 11.068 ↓ 5.1 1,313 1

Hash Left Join (cost=5.18..2,101.27 rows=257 width=48) (actual time=10.219..11.068 rows=1,313 loops=1)

  • Output: pp_1.totalamountpromise, pp_1.loanid, pp_1.id, u_1.name
  • Hash Cond: (pp_1.aid = u_1.id)
  • Buffers: shared hit=810
95. 10.774 10.774 ↓ 5.1 1,313 1

Seq Scan on delta.collecting_promisepayment pp_1 (cost=0.00..2,092.55 rows=257 width=22) (actual time=10.158..10.774 rows=1,313 loops=1)

  • Output: pp_1.id, pp_1.aid, pp_1.pid, pp_1.loanid, pp_1.isdiscount, pp_1.firsttimepromise, pp_1.totalamountpromise, pp_1.periodamount, pp_1.paymentperiod, pp_1.state, pp_1.comment, pp_1.phoneid, pp_1.created, pp_1.discountamount, pp_1.promisesource, pp_1.isimport, pp_1.delayreason
  • Filter: (((pp_1.created)::date >= '2020-03-01'::date) AND ((pp_1.created)::date <= ('now'::cstring)::date))
  • Rows Removed by Filter: 50109
  • Buffers: shared hit=807
96. 0.019 0.043 ↑ 1.0 97 1

Hash (cost=3.97..3.97 rows=97 width=32) (actual time=0.043..0.043 rows=97 loops=1)

  • Output: u_1.name, u_1.id
  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
  • Buffers: shared hit=3
97. 0.024 0.024 ↑ 1.0 97 1

Seq Scan on delta.collecting_users u_1 (cost=0.00..3.97 rows=97 width=32) (actual time=0.010..0.024 rows=97 loops=1)

  • Output: u_1.name, u_1.id
  • Buffers: shared hit=3
98. 50.126 96.329 ↑ 1.0 178,965 1

Hash (cost=10,503.65..10,503.65 rows=178,965 width=6) (actual time=96.329..96.329 rows=178,965 loops=1)

  • Output: l.id
  • Buckets: 262144 Batches: 1 Memory Usage: 8865kB
  • Buffers: shared hit=8714
99. 46.203 46.203 ↑ 1.0 178,965 1

Seq Scan on delta.collecting_loans l (cost=0.00..10,503.65 rows=178,965 width=6) (actual time=0.008..46.203 rows=178,965 loops=1)

  • Output: l.id
  • Buffers: shared hit=8714
100. 0.034 3,610.151 ↓ 1.2 243 1

Hash (cost=138,372.83..138,372.83 rows=200 width=4) (actual time=3,610.151..3,610.151 rows=243 loops=1)

  • Output: pay.id
  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
  • Buffers: shared hit=260256
101. 0.033 3,610.117 ↓ 1.2 243 1

Subquery Scan on pay (cost=138,355.08..138,372.83 rows=200 width=4) (actual time=3,610.009..3,610.117 rows=243 loops=1)

  • Output: pay.id
  • Buffers: shared hit=260256
102. 0.060 3,610.084 ↓ 1.2 243 1

GroupAggregate (cost=138,355.08..138,370.83 rows=200 width=40) (actual time=3,610.008..3,610.084 rows=243 loops=1)

  • Output: a.id, a.aid, NULL::numeric
  • Group Key: a.id, a.aid
  • Buffers: shared hit=260256
103. 0.066 3,610.024 ↑ 6.2 297 1

Sort (cost=138,355.08..138,359.66 rows=1,833 width=8) (actual time=3,610.004..3,610.024 rows=297 loops=1)

  • Output: a.id, a.aid
  • Sort Key: a.id, a.aid
  • Sort Method: quicksort Memory: 38kB
  • Buffers: shared hit=260256
104. 0.043 3,609.958 ↑ 6.2 297 1

Subquery Scan on a (cost=138,214.49..138,255.73 rows=1,833 width=8) (actual time=3,609.855..3,609.958 rows=297 loops=1)

  • Output: a.id, a.aid
  • Buffers: shared hit=260256
105. 0.039 3,609.915 ↑ 6.2 297 1

Unique (cost=138,214.49..138,237.40 rows=1,833 width=28) (actual time=3,609.853..3,609.915 rows=297 loops=1)

  • Output: pp_2.id, acc.sum, acc.accounting_id, pp_2.aid
  • Buffers: shared hit=260256
106. 0.584 3,609.876 ↑ 6.2 297 1

Sort (cost=138,214.49..138,219.07 rows=1,833 width=28) (actual time=3,609.853..3,609.876 rows=297 loops=1)

  • Output: pp_2.id, acc.sum, acc.accounting_id, pp_2.aid
  • Sort Key: pp_2.id, acc.sum, acc.accounting_id, pp_2.aid
  • Sort Method: quicksort Memory: 48kB
  • Buffers: shared hit=260256
107. 26.331 3,609.292 ↑ 6.2 297 1

Merge Join (cost=114,131.94..138,115.14 rows=1,833 width=28) (actual time=444.733..3,609.292 rows=297 loops=1)

  • Output: pp_2.id, acc.sum, acc.accounting_id, pp_2.aid
  • Merge Cond: (acc.accounting_id = acc_1.accounting_id)
  • Buffers: shared hit=260256
108. 170.101 410.579 ↑ 1.0 366,465 1

Unique (cost=87,090.63..104,500.71 rows=366,528 width=416) (actual time=195.479..410.579 rows=366,465 loops=1)

  • Output: acc.accounting_id, acc.tms, acc.auser_id, acc.application_id, acc.type, acc.method, acc.sum, acc.balance_before, acc.balance_after, acc.bazis_before, acc.bazis_after, acc.percent_before, acc.percent_after, acc.penalty_before, acc.penalty_after, acc.fee_before, acc.fee_after, acc.app_overdue_days
  • Buffers: shared hit=5528
109. 160.017 240.478 ↑ 1.0 366,465 1

Sort (cost=87,090.63..88,006.95 rows=366,528 width=416) (actual time=195.478..240.478 rows=366,465 loops=1)

  • Output: acc.accounting_id, acc.tms, acc.auser_id, acc.application_id, acc.type, acc.method, acc.sum, acc.balance_before, acc.balance_after, acc.bazis_before, acc.bazis_after, acc.percent_before, acc.percent_after, acc.penalty_before, acc.penalty_after, acc.fee_before, acc.fee_after, acc.app_overdue_days
  • Sort Key: acc.accounting_id, acc.tms, acc.auser_id, acc.application_id, acc.type, acc.method, acc.sum, acc.balance_before, acc.balance_after, acc.bazis_before, acc.bazis_after, acc.percent_before, acc.percent_after, acc.penalty_before, acc.penalty_after, acc.fee_before, acc.fee_after, acc.app_overdue_days
  • Sort Method: quicksort Memory: 61813kB
  • Buffers: shared hit=5528
110. 33.581 80.461 ↑ 1.0 366,528 1

Append (cost=0.00..12,858.56 rows=366,528 width=416) (actual time=0.013..80.461 rows=366,528 loops=1)

  • Buffers: shared hit=5528
111. 41.085 41.085 ↑ 1.0 307,952 1

Seq Scan on delta.collecting_accounting acc (cost=0.00..7,825.52 rows=307,952 width=84) (actual time=0.012..41.085 rows=307,952 loops=1)

  • Output: acc.accounting_id, acc.tms, acc.auser_id, acc.application_id, acc.type, acc.method, acc.sum, acc.balance_before, acc.balance_after, acc.bazis_before, acc.bazis_after, acc.percent_before, acc.percent_after, acc.penalty_before, acc.penalty_after, acc.fee_before, acc.fee_after, acc.app_overdue_days
  • Buffers: shared hit=4746
112. 5.795 5.795 ↑ 1.0 58,576 1

Seq Scan on delta.collecting_accounting3 acc3 (cost=0.00..1,367.76 rows=58,576 width=174) (actual time=0.015..5.795 rows=58,576 loops=1)

  • Output: acc3.accounting_id, acc3.tms, acc3.auser_id, acc3.application_id, acc3.type, acc3.method, acc3.sum, acc3.balance_before, acc3.balance_after, acc3.bazis_before, acc3.bazis_after, acc3.percent_before, acc3.percent_after, acc3.penalty_before, acc3.penalty_after, acc3.fee_before, acc3.fee_after, acc3.app_overdue_days
  • Buffers: shared hit=782
113. 0.999 3,172.382 ↓ 297.0 297 1

Materialize (cost=27,041.31..29,005.34 rows=1 width=12) (actual time=192.011..3,172.382 rows=297 loops=1)

  • Output: acc_1.accounting_id, pp_2.id, pp_2.aid
  • Buffers: shared hit=254728
114. 1,268.350 3,171.383 ↓ 297.0 297 1

Nested Loop (cost=27,041.31..29,005.34 rows=1 width=12) (actual time=192.004..3,171.383 rows=297 loops=1)

  • Output: acc_1.accounting_id, pp_2.id, pp_2.aid
  • Join Filter: ((max(pp_3.id)) = pp_2.id)
  • Rows Removed by Join Filter: 15272037
  • Buffers: shared hit=254728
115. 1.203 183.700 ↓ 297.0 297 1

GroupAggregate (cost=27,041.31..27,041.33 rows=1 width=20) (actual time=182.204..183.700 rows=297 loops=1)

  • Output: acc_1.accounting_id, max(pp_3.id), acc_1.application_id, acc_1.tms
  • Group Key: acc_1.accounting_id, acc_1.application_id, acc_1.tms
  • Buffers: shared hit=15049
116. 0.395 182.497 ↓ 299.0 299 1

Sort (cost=27,041.31..27,041.31 rows=1 width=20) (actual time=182.199..182.497 rows=299 loops=1)

  • Output: acc_1.accounting_id, acc_1.application_id, acc_1.tms, pp_3.id
  • Sort Key: acc_1.accounting_id, acc_1.application_id, acc_1.tms
  • Sort Method: quicksort Memory: 48kB
  • Buffers: shared hit=15049
117. 0.424 182.102 ↓ 299.0 299 1

Hash Join (cost=24,947.76..27,041.30 rows=1 width=20) (actual time=181.011..182.102 rows=299 loops=1)

  • Output: acc_1.accounting_id, acc_1.application_id, acc_1.tms, pp_3.id
  • Hash Cond: (pp_3.loanid = l_1.id)
  • Join Filter: ((acc_1.tms > pp_3.created) AND ((date(acc_1.tms) - date(pp_3.firsttimepromise)) < 2))
  • Rows Removed by Join Filter: 469
  • Buffers: shared hit=15049
118. 10.606 10.606 ↓ 5.1 1,313 1

Seq Scan on delta.collecting_promisepayment pp_3 (cost=0.00..2,092.55 rows=257 width=26) (actual time=9.902..10.606 rows=1,313 loops=1)

  • Output: pp_3.id, pp_3.aid, pp_3.pid, pp_3.loanid, pp_3.isdiscount, pp_3.firsttimepromise, pp_3.totalamountpromise, pp_3.periodamount, pp_3.paymentperiod, pp_3.state, pp_3.comment, pp_3.phoneid, pp_3.created, pp_3.discountamount, pp_3.promisesource, pp_3.isimport, pp_3.delayreason
  • Filter: (((pp_3.created)::date >= '2020-03-01'::date) AND ((pp_3.created)::date <= ('now'::cstring)::date))
  • Rows Removed by Filter: 50109
  • Buffers: shared hit=807
119. 0.232 171.072 ↓ 3.0 1,030 1

Hash (cost=24,943.52..24,943.52 rows=339 width=22) (actual time=171.072..171.072 rows=1,030 loops=1)

  • Output: acc_1.accounting_id, acc_1.application_id, acc_1.tms, l_1.id
  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 72kB
  • Buffers: shared hit=14242
120. 1.124 170.840 ↓ 3.0 1,030 1

Hash Join (cost=24,922.34..24,943.52 rows=339 width=22) (actual time=169.682..170.840 rows=1,030 loops=1)

  • Output: acc_1.accounting_id, acc_1.application_id, acc_1.tms, l_1.id
  • Hash Cond: ((acc_1.application_id)::numeric = l_1.eid)
  • Buffers: shared hit=14242
121. 1.202 61.326 ↓ 3.0 1,030 1

HashAggregate (cost=12,181.62..12,185.01 rows=339 width=416) (actual time=60.984..61.326 rows=1,030 loops=1)

  • Output: acc_1.accounting_id, acc_1.tms, acc_1.auser_id, acc_1.application_id, acc_1.type, acc_1.method, acc_1.sum, acc_1.balance_before, acc_1.balance_after, acc_1.bazis_before, acc_1.bazis_after, acc_1.percent_before, acc_1.percent_after, acc_1.penalty_before, acc_1.penalty_after, acc_1.fee_before, acc_1.fee_after, acc_1.app_overdue_days
  • Group Key: acc_1.accounting_id, acc_1.tms, acc_1.auser_id, acc_1.application_id, acc_1.type, acc_1.method, acc_1.sum, acc_1.balance_before, acc_1.balance_after, acc_1.bazis_before, acc_1.bazis_after, acc_1.percent_before, acc_1.percent_after, acc_1.penalty_before, acc_1.penalty_after, acc_1.fee_before, acc_1.fee_after, acc_1.app_overdue_days
  • Buffers: shared hit=5528
122. 0.045 60.124 ↓ 3.0 1,030 1

Append (cost=1,000.00..12,166.37 rows=339 width=416) (actual time=46.172..60.124 rows=1,030 loops=1)

  • Buffers: shared hit=5528
123. 3.386 46.368 ↑ 47.2 6 1

Gather (cost=1,000.00..9,623.70 rows=283 width=84) (actual time=46.171..46.368 rows=6 loops=1)

  • Output: acc_1.accounting_id, acc_1.tms, acc_1.auser_id, acc_1.application_id, acc_1.type, acc_1.method, acc_1.sum, acc_1.balance_before, acc_1.balance_after, acc_1.bazis_before, acc_1.bazis_after, acc_1.percent_before, acc_1.percent_after, acc_1.penalty_before, acc_1.penalty_after, acc_1.fee_before, acc_1.fee_after, acc_1.app_overdue_days
  • Workers Planned: 2
  • Workers Launched: 1
  • Buffers: shared hit=4746
124. 42.982 42.982 ↑ 39.3 3 2 / 2

Parallel Seq Scan on delta.collecting_accounting acc_1 (cost=0.00..8,595.40 rows=118 width=84) (actual time=42.953..42.982 rows=3 loops=2)

  • Output: acc_1.accounting_id, acc_1.tms, acc_1.auser_id, acc_1.application_id, acc_1.type, acc_1.method, acc_1.sum, acc_1.balance_before, acc_1.balance_after, acc_1.bazis_before, acc_1.bazis_after, acc_1.percent_before, acc_1.percent_after, acc_1.penalty_before, acc_1.penalty_after, acc_1.fee_before, acc_1.fee_after, acc_1.app_overdue_days
  • Filter: ((acc_1.app_overdue_days > 2) AND (acc_1.app_overdue_days < 21) AND ((acc_1.tms)::date >= '2020-03-01'::date) AND ((acc_1.tms)::date <= ('now'::cstring)::date))
  • Rows Removed by Filter: 153973
  • Buffers: shared hit=4746
  • Worker 0: actual time=40.470..40.494 rows=2 loops=1
  • Buffers: shared hit=1995
125. 13.711 13.711 ↓ 18.3 1,024 1

Seq Scan on delta.collecting_accounting3 acc3_1 (cost=0.00..2,539.28 rows=56 width=174) (actual time=5.963..13.711 rows=1,024 loops=1)

  • Output: acc3_1.accounting_id, acc3_1.tms, acc3_1.auser_id, acc3_1.application_id, acc3_1.type, acc3_1.method, acc3_1.sum, acc3_1.balance_before, acc3_1.balance_after, acc3_1.bazis_before, acc3_1.bazis_after, acc3_1.percent_before, acc3_1.percent_after, acc3_1.penalty_before, acc3_1.penalty_after, acc3_1.fee_before, acc3_1.fee_after, acc3_1.app_overdue_days
  • Filter: ((acc3_1.app_overdue_days > 2) AND (acc3_1.app_overdue_days < 21) AND ((acc3_1.tms)::date >= '2020-03-01'::date) AND ((acc3_1.tms)::date <= ('now'::cstring)::date))
  • Rows Removed by Filter: 57552
  • Buffers: shared hit=782
126. 52.591 108.390 ↑ 1.0 178,965 1

Hash (cost=10,503.65..10,503.65 rows=178,965 width=12) (actual time=108.390..108.390 rows=178,965 loops=1)

  • Output: l_1.id, l_1.eid
  • Buckets: 262144 Batches: 1 Memory Usage: 10087kB
  • Buffers: shared hit=8714
127. 55.799 55.799 ↑ 1.0 178,965 1

Seq Scan on delta.collecting_loans l_1 (cost=0.00..10,503.65 rows=178,965 width=12) (actual time=0.011..55.799 rows=178,965 loops=1)

  • Output: l_1.id, l_1.eid
  • Buffers: shared hit=8714
128. 1,719.333 1,719.333 ↑ 1.0 51,422 297

Seq Scan on delta.collecting_promisepayment pp_2 (cost=0.00..1,321.22 rows=51,422 width=8) (actual time=0.004..5.789 rows=51,422 loops=297)

  • Output: pp_2.id, pp_2.aid, pp_2.pid, pp_2.loanid, pp_2.isdiscount, pp_2.firsttimepromise, pp_2.totalamountpromise, pp_2.periodamount, pp_2.paymentperiod, pp_2.state, pp_2.comment, pp_2.phoneid, pp_2.created, pp_2.discountamount, pp_2.promisesource, pp_2.isimport, pp_2.delayreason
  • Buffers: shared hit=239679