explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GTOB

Settings
# exclusive inclusive rows x rows loops node
1. 0.035 14,238.179 ↓ 6.0 6 1

Nested Loop Left Join (cost=1,694,448.14..1,694,456.35 rows=1 width=146) (actual time=14,236.555..14,238.179 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, temp read=4605 written=4605
2. 0.028 10,412.316 ↓ 6.0 6 1

Nested Loop (cost=1,541,209.93..1,541,213.02 rows=1 width=122) (actual time=10,412.284..10,412.316 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.018 10,412.268 ↑ 3.1 20 1

Sort (cost=1,541,209.93..1,541,210.09 rows=63 width=118) (actual time=10,412.260..10,412.268 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.039 10,412.250 ↑ 3.1 20 1

Hash Right Join (cost=1,541,201.67..1,541,208.05 rows=63 width=118) (actual time=10,412.215..10,412.250 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.018 0.018 ↑ 1.0 97 1

Seq Scan on delta.collecting_users u (cost=0.00..3.97 rows=97 width=32) (actual time=0.009..0.018 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.010 10,412.193 ↑ 3.1 20 1

Hash (cost=1,541,200.88..1,541,200.88 rows=63 width=66) (actual time=10,412.193..10,412.193 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.017 10,412.183 ↑ 3.1 20 1

Merge Left Join (cost=1,541,177.21..1,541,200.88 rows=63 width=66) (actual time=10,411.851..10,412.183 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.012 10,399.702 ↑ 3.1 20 1

Merge Left Join (cost=1,539,074.37..1,539,093.11 rows=63 width=50) (actual time=10,399.592..10,399.702 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.014 8,160.919 ↑ 3.1 20 1

Merge Left Join (cost=1,282,007.39..1,282,022.82 rows=63 width=42) (actual time=8,160.826..8,160.919 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.012 6,909.959 ↑ 3.1 20 1

Merge Left Join (cost=1,026,351.18..1,026,363.31 rows=63 width=34) (actual time=6,909.887..6,909.959 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.011 4,797.608 ↑ 3.1 20 1

Merge Left Join (cost=770,653.45..770,662.27 rows=63 width=26) (actual time=4,797.553..4,797.608 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.019 3,559.985 ↑ 3.1 20 1

Merge Left Join (cost=514,713.54..514,719.05 rows=63 width=18) (actual time=3,559.945..3,559.985 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.014 1,273.082 ↑ 3.1 20 1

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

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

Sort (cost=257,192.81..257,193.13 rows=126 width=10) (actual time=1,273.064..1,273.068 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. 6.011 1,273.156 ↑ 3.2 39 1

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

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

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

  • Output: h.aid, PARTIAL count(h.typeid)
  • Group Key: h.aid
  • Buffers: shared hit=148575
  • Worker 0: actual time=1251.869..1263.217 rows=20 loops=1
  • Buffers: shared hit=61930
17. 47.394 1,241.430 ↓ 10.2 215,828 2 / 2

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

  • Output: h.aid, h.typeid
  • Sort Key: h.aid
  • Sort Method: quicksort Memory: 16572kB
  • Buffers: shared hit=148575
  • Worker 0: actual time=1223.566..1238.146 rows=209199 loops=1
  • Buffers: shared hit=61930
18. 1,194.036 1,194.036 ↓ 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,076.002..1,194.036 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=1072.930..1189.387 rows=209199 loops=1
  • Buffers: shared hit=61901
19. 0.006 2,286.884 ↑ 3.1 20 1

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

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

Finalize GroupAggregate (cost=257,520.73..257,522.30 rows=63 width=10) (actual time=2,286.865..2,286.878 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,286.860 ↑ 6.3 20 1

Sort (cost=257,520.73..257,521.04 rows=126 width=10) (actual time=2,286.859..2,286.860 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.858 2,286.904 ↑ 6.3 20 1

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

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

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

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

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

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

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

  • Output: h_1.aid, h_1.typeid
  • Hash Cond: (h_1.typeid = cr.id)
  • Buffers: shared hit=148574
26. 2,008.253 2,008.253 ↓ 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,770.336..2,008.253 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.195 0.736 ↑ 1.0 1,175 1

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

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

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

  • Output: cr.id
  • Hash Cond: (cr.typeid = ct.id)
  • Buffers: shared hit=28
29. 0.146 0.146 ↑ 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.011..0.146 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.035 0.107 ↑ 1.0 287 1

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

  • Output: ct.id
  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
  • Buffers: shared hit=7
31. 0.072 0.072 ↑ 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.072 rows=287 loops=1)

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

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

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

Finalize GroupAggregate (cost=255,939.91..255,941.48 rows=63 width=10) (actual time=1,237.599..1,237.606 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,237.597 ↑ 4.8 26 1

Sort (cost=255,939.91..255,940.22 rows=126 width=10) (actual time=1,237.595..1,237.597 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. 4.272 1,237.641 ↑ 4.8 26 1

Gather (cost=255,893.54..255,935.51 rows=126 width=10) (actual time=1,236.912..1,237.641 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.230 1,233.369 ↑ 4.8 13 2 / 2

Partial GroupAggregate (cost=254,893.54..254,922.91 rows=63 width=10) (actual time=1,232.935..1,233.369 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=1229.827..1230.338 rows=13 loops=1
  • Buffers: shared hit=59512
37. 0.929 1,233.139 ↑ 1.7 2,194 2 / 2

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

  • Output: h_2.aid, h_2.typeid
  • Sort Key: h_2.aid
  • Sort Method: quicksort Memory: 209kB
  • Buffers: shared hit=148656
  • Worker 0: actual time=1229.749..1230.111 rows=1984 loops=1
  • Buffers: shared hit=59512
38. 25.965 1,232.210 ↑ 1.7 2,194 2 / 2

Hash Join (cost=53.86..254,665.39 rows=3,833 width=7) (actual time=1,076.243..1,232.210 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=1073.302..1228.949 rows=1984 loops=1
  • Buffers: shared hit=59483
39. 1,205.694 1,205.694 ↓ 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,075.548..1,205.694 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=1072.427..1204.576 rows=191056 loops=1
  • Buffers: shared hit=59430
40. 0.052 0.551 ↓ 1.3 275 2 / 2

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

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

Hash Join (cost=11.32..51.12 rows=219 width=5) (actual time=0.244..0.499 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.299..0.569 rows=275 loops=1
  • Buffers: shared hit=39
42. 0.159 0.159 ↑ 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.018..0.159 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.027..0.189 rows=1208 loops=1
  • Buffers: shared hit=21
43. 0.011 0.167 ↓ 1.0 54 2 / 2

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

  • Output: ct_1.id
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=14
  • Worker 0: actual time=0.176..0.176 rows=54 loops=1
  • Buffers: shared hit=7
44. 0.156 0.156 ↓ 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.025..0.156 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.030..0.163 rows=54 loops=1
  • Buffers: shared hit=7
45. 0.003 2,112.339 ↑ 4.8 13 1

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

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

Finalize GroupAggregate (cost=255,697.73..255,699.31 rows=63 width=10) (actual time=2,112.329..2,112.336 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,112.328 ↑ 9.7 13 1

Sort (cost=255,697.73..255,698.05 rows=126 width=10) (actual time=2,112.325..2,112.328 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.801 2,112.366 ↑ 9.7 13 1

Gather (cost=255,673.60..255,693.34 rows=126 width=10) (actual time=2,111.882..2,112.366 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.281 2,111.565 ↑ 4.8 13 1

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

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

Sort (cost=254,673.60..254,675.77 rows=868 width=7) (actual time=2,111.010..2,111.284 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. 48.553 2,110.399 ↓ 3.3 2,831 1

Hash Join (cost=49.52..254,631.23 rows=868 width=7) (actual time=1,839.180..2,110.399 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,061.482 2,061.482 ↓ 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,838.522..2,061.482 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.007 0.364 ↑ 1.1 44 1

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

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

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

  • Output: cr_2.id
  • Hash Cond: (cr_2.typeid = ct_2.id)
  • Buffers: shared hit=28
55. 0.149 0.149 ↑ 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.011..0.149 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.001 0.111 ↑ 1.0 12 1

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

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

Seq Scan on delta.collecting_contacttype ct_2 (cost=0.00..10.66 rows=12 width=2) (actual time=0.017..0.110 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.003 1,250.946 ↑ 4.8 13 1

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

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

Finalize GroupAggregate (cost=255,656.21..255,657.78 rows=63 width=10) (actual time=1,250.931..1,250.943 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,250.934 ↑ 4.8 26 1

Sort (cost=255,656.21..255,656.52 rows=126 width=10) (actual time=1,250.927..1,250.934 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.924 1,250.969 ↑ 4.8 26 1

Gather (cost=255,636.41..255,651.81 rows=126 width=10) (actual time=1,250.473..1,250.969 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.164 1,247.045 ↑ 4.8 13 2 / 2

Partial GroupAggregate (cost=254,636.41..254,639.21 rows=63 width=10) (actual time=1,246.812..1,247.045 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=1244.031..1244.217 rows=13 loops=1
  • Buffers: shared hit=61288
63. 0.539 1,246.881 ↓ 3.5 1,015 2 / 2

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

  • Output: h_4.aid, h_4.typeid
  • Sort Key: h_4.aid
  • Sort Method: quicksort Memory: 97kB
  • Buffers: shared hit=148656
  • Worker 0: actual time=1243.992..1244.083 rows=1000 loops=1
  • Buffers: shared hit=61288
64. 28.812 1,246.342 ↓ 3.5 1,015 2 / 2

Hash Join (cost=48.68..254,624.60 rows=289 width=7) (actual time=1,079.699..1,246.342 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=1077.141..1243.492 rows=1000 loops=1
  • Buffers: shared hit=61259
65. 1,216.969 1,216.969 ↓ 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,078.872..1,216.969 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=1076.091..1215.307 rows=215351 loops=1
  • Buffers: shared hit=61206
66. 0.013 0.561 ↑ 2.0 8 2 / 2

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

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

Hash Join (cost=10.71..48.48 rows=16 width=5) (actual time=0.239..0.548 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.322..0.731 rows=8 loops=1
  • Buffers: shared hit=39
68. 0.219 0.219 ↑ 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.025..0.219 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.038..0.291 rows=1208 loops=1
  • Buffers: shared hit=21
69. 0.005 0.145 ↑ 1.0 4 2 / 2

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

  • Output: ct_3.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=14
  • Worker 0: actual time=0.179..0.179 rows=4 loops=1
  • Buffers: shared hit=7
70. 0.140 0.140 ↑ 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.034..0.140 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.041..0.172 rows=4 loops=1
  • Buffers: shared hit=7
71. 0.006 2,238.771 ↑ 3.1 20 1

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

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

Finalize GroupAggregate (cost=257,066.98..257,068.55 rows=63 width=10) (actual time=2,238.757..2,238.765 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,238.757 ↑ 6.3 20 1

Sort (cost=257,066.98..257,067.29 rows=126 width=10) (actual time=2,238.753..2,238.757 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. 1.118 2,238.773 ↑ 6.3 20 1

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

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

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

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

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

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

Hash Join (cost=73.83..254,807.44 rows=16,055 width=7) (actual time=1,757.431..2,109.798 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. 1,993.474 1,993.474 ↓ 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,756.581..1,993.474 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.135 0.832 ↑ 1.1 848 1

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

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

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

  • Output: cr_4.id
  • Hash Cond: (cr_4.typeid = ct_4.id)
  • Buffers: shared hit=28
81. 0.151 0.151 ↑ 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.011..0.151 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.026 0.316 ↑ 1.0 217 1

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

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

Seq Scan on delta.collecting_contacttype ct_4 (cost=0.00..12.86 rows=222 width=2) (actual time=0.013..0.290 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.004 12.464 ↑ 5.2 12 1

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

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

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

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

Sort (cost=2,102.84..2,103.48 rows=257 width=16) (actual time=12.213..12.282 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. 12.011 12.011 ↓ 4.9 1,269 1

Seq Scan on delta.collecting_promisepayment pp (cost=0.00..2,092.55 rows=257 width=16) (actual time=11.348..12.011 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.007 0.020 ↓ 6.0 6 20

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

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

Seq Scan on analysts.collection_oper_individual_plan (cost=0.00..1.35 rows=1 width=37) (actual time=0.012..0.013 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. 1.032 3,825.828 ↑ 8.1 12 6

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

  • Output: u_1.name, sum(pp_1.totalamountpromise)
  • Group Key: u_1.name
  • Buffers: shared hit=269780, temp read=4605 written=4605
91. 1.063 3,824.796 ↓ 5.1 1,313 6

Sort (cost=153,238.21..153,238.85 rows=257 width=38) (actual time=637.368..637.466 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, temp read=4605 written=4605
92. 0.287 3,823.733 ↓ 5.1 1,313 1

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

  • Output: u_1.name, pp_1.totalamountpromise
  • Hash Cond: (pp_1.id = pay.id)
  • Buffers: shared hit=269780, temp read=4605 written=4605
93. 1.163 160.786 ↓ 5.1 1,313 1

Hash Left Join (cost=12,745.90..14,845.51 rows=257 width=42) (actual time=158.874..160.786 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.299 10.520 ↓ 5.1 1,313 1

Hash Left Join (cost=5.18..2,101.27 rows=257 width=48) (actual time=9.456..10.520 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.179 10.179 ↓ 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=9.398..10.179 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.014 0.042 ↑ 1.0 97 1

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

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

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

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

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

  • Output: l.id
  • Buckets: 262144 Batches: 1 Memory Usage: 8865kB
  • Buffers: shared hit=8714
99. 59.872 59.872 ↑ 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..59.872 rows=178,965 loops=1)

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

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

  • Output: pay.id
  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
  • Buffers: shared hit=260256, temp read=4605 written=4605
101. 0.029 3,662.618 ↓ 1.2 243 1

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

  • Output: pay.id
  • Buffers: shared hit=260256, temp read=4605 written=4605
102. 0.077 3,662.589 ↓ 1.2 243 1

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

  • Output: a.id, a.aid, NULL::numeric
  • Group Key: a.id, a.aid
  • Buffers: shared hit=260256, temp read=4605 written=4605
103. 0.065 3,662.512 ↑ 6.2 297 1

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

  • Output: a.id, a.aid
  • Sort Key: a.id, a.aid
  • Sort Method: quicksort Memory: 38kB
  • Buffers: shared hit=260256, temp read=4605 written=4605
104. 0.028 3,662.447 ↑ 6.2 297 1

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

  • Output: a.id, a.aid
  • Buffers: shared hit=260256, temp read=4605 written=4605
105. 0.038 3,662.419 ↑ 6.2 297 1

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

  • Output: pp_2.id, acc.sum, acc.accounting_id, pp_2.aid
  • Buffers: shared hit=260256, temp read=4605 written=4605
106. 0.607 3,662.381 ↑ 6.2 297 1

Sort (cost=138,214.49..138,219.07 rows=1,833 width=28) (actual time=3,662.360..3,662.381 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, temp read=4605 written=4605
107. 32.497 3,661.774 ↑ 6.2 297 1

Merge Join (cost=114,131.94..138,115.14 rows=1,833 width=28) (actual time=522.251..3,661.774 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, temp read=4605 written=4605
108. 166.914 504.320 ↑ 1.0 366,465 1

Unique (cost=87,090.63..104,500.71 rows=366,528 width=416) (actual time=282.170..504.320 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, temp read=4605 written=4605
109. 255.183 337.406 ↑ 1.0 366,465 1

Sort (cost=87,090.63..88,006.95 rows=366,528 width=416) (actual time=282.169..337.406 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: external merge Disk: 36824kB
  • Buffers: shared hit=5528, temp read=4605 written=4605
110. 31.681 82.223 ↑ 1.0 366,528 1

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

  • Buffers: shared hit=5528
111. 43.228 43.228 ↑ 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..43.228 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. 7.314 7.314 ↑ 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.011..7.314 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. 1.011 3,124.957 ↓ 297.0 297 1

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

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

Nested Loop (cost=27,041.31..29,005.34 rows=1 width=12) (actual time=197.604..3,123.946 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.133 189.243 ↓ 297.0 297 1

GroupAggregate (cost=27,041.31..27,041.33 rows=1 width=20) (actual time=187.821..189.243 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.391 188.110 ↓ 299.0 299 1

Sort (cost=27,041.31..27,041.31 rows=1 width=20) (actual time=187.816..188.110 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.397 187.719 ↓ 299.0 299 1

Hash Join (cost=24,947.76..27,041.30 rows=1 width=20) (actual time=186.699..187.719 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. 15.863 15.863 ↓ 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=15.208..15.863 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.179 171.459 ↓ 3.0 1,030 1

Hash (cost=24,943.52..24,943.52 rows=339 width=22) (actual time=171.459..171.459 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.066 171.280 ↓ 3.0 1,030 1

Hash Join (cost=24,922.34..24,943.52 rows=339 width=22) (actual time=170.219..171.280 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.010 63.120 ↓ 3.0 1,030 1

HashAggregate (cost=12,181.62..12,185.01 rows=339 width=416) (actual time=62.819..63.120 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.058 62.110 ↓ 3.0 1,030 1

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

  • Buffers: shared hit=5528
123. 8.690 50.057 ↑ 47.2 6 1

Gather (cost=1,000.00..9,623.70 rows=283 width=84) (actual time=47.340..50.057 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. 41.367 41.367 ↑ 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=41.340..41.367 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=36.006..36.036 rows=4 loops=1
  • Buffers: shared hit=1637
125. 11.995 11.995 ↓ 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=4.469..11.995 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. 46.371 107.094 ↑ 1.0 178,965 1

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

  • Output: l_1.id, l_1.eid
  • Buckets: 262144 Batches: 1 Memory Usage: 10087kB
  • Buffers: shared hit=8714
127. 60.723 60.723 ↑ 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.010..60.723 rows=178,965 loops=1)

  • Output: l_1.id, l_1.eid
  • Buffers: shared hit=8714
128. 1,680.426 1,680.426 ↑ 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.658 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