explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WNFc

Settings
# exclusive inclusive rows x rows loops node
1. 165.899 41,510.618 ↑ 104.1 153,753 1

Subquery Scan on foo (cost=983,016,410.15..5,944,030,565.85 rows=16,003,271 width=303) (actual time=26,914.042..41,510.618 rows=153,753 loops=1)

  • Output: foo.loanid, foo.cl_createdate, foo.cl_report_createdate, foo.businessunit, foo.refi, foo.cl_product_name, foo.type_formula, foo.control_file_name, foo.credit_model_true, foo.total_score, foo.bcs_score, foo.uwscore2_calculated, foo.bcscore2_calculated, foo.uwscore3_calculated, foo.bcscore3_calculated, foo.uwscore4_calculated, foo.uw4_apprve_uw3_deny_exception_flag, foo.uw4_deny_uw3_apprve_exception_flag, foo.uw4_apprve_uw3_apprve_exception_flag, foo.truefpd, foo.pd28_ratio, foo.pd70_ratio, foo.python_calc_time, foo.denygrp, foo.rownum1
  • Filter: (foo.rownum1 = 1)
  • Rows Removed by Filter: 1878750
  • Buffers: shared hit=7744276 read=209612 dirtied=620
2. 14,310.505 41,344.719 ↑ 1,574.7 2,032,503 1

WindowAgg (cost=983,016,410.15..5,904,022,387.18 rows=3,200,654,294 width=303) (actual time=26,914.040..41,344.719 rows=2,032,503 loops=1)

  • Output: production_test_uw4_leads.loanid, foooo.createddate, foooo.created_at, production_test_uw4_leads.businessunit, production_test_uw4_leads.refi, app.cl_product_name, app.type_formula, production_test_uw4_leads.control_file_name, production_test_uw4_leads.credit_model_true, app.total_score, app.bcs_score, uwscore_bcs_scores.uwscore2_calculated, uwscore_bcs_scores.bcscore2_calculated, uwscore_bcs_scores.uwscore3_calculated, uwscore_bcs_scores.bcscore3_calculated, round(production_test_uw4_leads.uwscore4_calculated), CASE WHEN (foooo.created_at <= '2019-09-06 12:30:00'::timestamp without time zone) THEN gfangyuan.func_uw4_apprve_uw3_deny('355'::double precision, (app.cl_product_name)::bpchar, (app.type_formula)::bpchar, uwscore_bcs_scores.uwscore3_calculated, production_test_uw4_leads.uwscore4_calculated) WHEN (foooo.created_at > '2019-09-06 12:30:00'::timestamp without time zone) THEN gfangyuan.func_uw4_apprve_uw3_deny('345'::double precision, (app.cl_product_name)::bpchar, (app.type_formula)::bpchar, uwscore_bcs_scores.uwscore3_calculated, production_test_uw4_leads.uwscore4_calculated) ELSE '0'::double precision END, CASE WHEN (foooo.created_at <= '2019-09-06 12:30:00'::timestamp without time zone) THEN gfangyuan.func_uw4_deny_uw3_apprve('355'::double precision, (app.cl_product_name)::bpchar, (app.type_formula)::bpchar, uwscore_bcs_scores.uwscore3_calculated, production_test_uw4_leads.uwscore4_calculated) WHEN (foooo.created_at > '2019-09-06 12:30:00'::timestamp without time zone) THEN gfangyuan.func_uw4_deny_uw3_apprve('345'::double precision, (app.cl_product_name)::bpchar, (app.type_formula)::bpchar, uwscore_bcs_scores.uwscore3_calculated, production_test_uw4_leads.uwscore4_calculated) ELSE '0'::double precision END, CASE WHEN (foooo.created_at <= '2019-09-06 12:30:00'::timestamp without time zone) THEN gfangyuan.func_uw4_apprve_uw3_apprve('355'::double precision, (app.cl_product_name)::bpchar, (app.type_formula)::bpchar, uwscore_bcs_scores.uwscore3_calculated, production_test_uw4_leads.uwscore4_calculated) WHEN (foooo.created_at > '2019-09-06 12:30:00'::timestamp without time zone) THEN gfangyuan.func_uw4_apprve_uw3_apprve('345'::double precision, (app.cl_product_name)::bpchar, (app.type_formula)::bpchar, uwscore_bcs_scores.uwscore3_calculated, production_test_uw4_leads.uwscore4_calculated) ELSE '0'::double precision END, pds.truefpd, pds.pd28_ratio, pds.pd70_ratio, production_test_uw4_leads.python_calc_time, allapps.denygrp, row_number() OVER (?)
  • Buffers: shared hit=7744276 read=209612 dirtied=620
3. 2,194.870 27,034.214 ↑ 1,574.7 2,032,503 1

Sort (cost=983,016,410.15..991,018,045.89 rows=3,200,654,294 width=271) (actual time=26,912.560..27,034.214 rows=2,032,503 loops=1)

  • Output: production_test_uw4_leads.loanid, production_test_uw4_leads.python_calc_time, foooo.createddate, foooo.created_at, production_test_uw4_leads.businessunit, production_test_uw4_leads.refi, app.cl_product_name, app.type_formula, production_test_uw4_leads.control_file_name, production_test_uw4_leads.credit_model_true, app.total_score, app.bcs_score, uwscore_bcs_scores.uwscore2_calculated, uwscore_bcs_scores.bcscore2_calculated, uwscore_bcs_scores.uwscore3_calculated, uwscore_bcs_scores.bcscore3_calculated, production_test_uw4_leads.uwscore4_calculated, pds.truefpd, pds.pd28_ratio, pds.pd70_ratio, allapps.denygrp
  • Sort Key: production_test_uw4_leads.loanid, production_test_uw4_leads.python_calc_time DESC
  • Sort Method: quicksort Memory: 256272kB
  • Buffers: shared hit=7744103 read=209612 dirtied=620
4. 512.797 24,839.344 ↑ 1,574.7 2,032,503 1

Nested Loop Left Join (cost=737,803.25..3,542,163.53 rows=3,200,654,294 width=271) (actual time=13,540.373..24,839.344 rows=2,032,503 loops=1)

  • Output: production_test_uw4_leads.loanid, production_test_uw4_leads.python_calc_time, foooo.createddate, foooo.created_at, production_test_uw4_leads.businessunit, production_test_uw4_leads.refi, app.cl_product_name, app.type_formula, production_test_uw4_leads.control_file_name, production_test_uw4_leads.credit_model_true, app.total_score, app.bcs_score, uwscore_bcs_scores.uwscore2_calculated, uwscore_bcs_scores.bcscore2_calculated, uwscore_bcs_scores.uwscore3_calculated, uwscore_bcs_scores.bcscore3_calculated, production_test_uw4_leads.uwscore4_calculated, pds.truefpd, pds.pd28_ratio, pds.pd70_ratio, allapps.denygrp
  • Buffers: shared hit=7744100 read=209612 dirtied=620
5. 538.313 16,197.379 ↓ 2.2 2,032,292 1

Hash Right Join (cost=737,802.40..1,083,599.60 rows=936,395 width=231) (actual time=13,540.321..16,197.379 rows=2,032,292 loops=1)

  • Output: foooo.createddate, foooo.created_at, production_test_uw4_leads.loanid, production_test_uw4_leads.businessunit, production_test_uw4_leads.refi, production_test_uw4_leads.control_file_name, production_test_uw4_leads.credit_model_true, production_test_uw4_leads.uwscore4_calculated, production_test_uw4_leads.python_calc_time, app.cl_product_name, app.type_formula, app.total_score, app.bcs_score, pds.truefpd, pds.pd28_ratio, pds.pd70_ratio
  • Hash Cond: (production_test_uw4_leads.loanid = foooo.name)
  • Buffers: shared hit=4905436 read=209328 dirtied=620
6. 87.927 5,831.721 ↑ 1.0 153,768 1

Hash Left Join (cost=155,291.62..498,974.51 rows=153,768 width=215) (actual time=3,712.159..5,831.721 rows=153,768 loops=1)

  • Output: production_test_uw4_leads.loanid, production_test_uw4_leads.businessunit, production_test_uw4_leads.refi, production_test_uw4_leads.control_file_name, production_test_uw4_leads.credit_model_true, production_test_uw4_leads.uwscore4_calculated, production_test_uw4_leads.python_calc_time, app.cl_product_name, app.type_formula, app.total_score, app.bcs_score, pds.truefpd, pds.pd28_ratio, pds.pd70_ratio
  • Hash Cond: (production_test_uw4_leads.loanid = pds.loanid)
  • Buffers: shared hit=815398 read=92973 dirtied=588
7. 0.000 5,136.695 ↑ 1.0 153,768 1

Nested Loop Left Join (cost=124,626.70..466,195.28 rows=153,768 width=205) (actual time=3,101.357..5,136.695 rows=153,768 loops=1)

  • Output: production_test_uw4_leads.loanid, production_test_uw4_leads.businessunit, production_test_uw4_leads.refi, production_test_uw4_leads.control_file_name, production_test_uw4_leads.credit_model_true, production_test_uw4_leads.uwscore4_calculated, production_test_uw4_leads.python_calc_time, app.cl_product_name, app.type_formula, app.total_score, app.bcs_score
  • Buffers: shared hit=810380 read=82463 dirtied=588
8. 152.018 3,159.487 ↑ 1.0 153,768 1

HashAggregate (cost=124,626.14..126,163.82 rows=153,768 width=176) (actual time=3,101.322..3,159.487 rows=153,768 loops=1)

  • Output: production_test_uw4_leads.businessunit, production_test_uw4_leads.refi, production_test_uw4_leads.control_file_name, production_test_uw4_leads.credit_model_true, production_test_uw4_leads.loanid, production_test_uw4_leads.uwscore4_calculated, production_test_uw4_leads.python_calc_time
  • Group Key: production_test_uw4_leads.businessunit, production_test_uw4_leads.refi, production_test_uw4_leads.control_file_name, production_test_uw4_leads.credit_model_true, production_test_uw4_leads.loanid, production_test_uw4_leads.uwscore4_calculated, production_test_uw4_leads.python_calc_time
  • Buffers: shared hit=35628 read=82463 dirtied=421
9. 16.484 3,007.469 ↑ 1.0 153,768 1

Append (cost=0.00..121,935.20 rows=153,768 width=176) (actual time=0.019..3,007.469 rows=153,768 loops=1)

  • Buffers: shared hit=35628 read=82463 dirtied=421
10. 204.624 204.624 ↑ 1.0 48,226 1

Seq Scan on gfangyuan.production_test_uw4_leads (cost=0.00..36,108.26 rows=48,226 width=57) (actual time=0.017..204.624 rows=48,226 loops=1)

  • Output: production_test_uw4_leads.businessunit, production_test_uw4_leads.refi, production_test_uw4_leads.control_file_name, production_test_uw4_leads.credit_model_true, production_test_uw4_leads.loanid, production_test_uw4_leads.uwscore4_calculated, production_test_uw4_leads.python_calc_time
  • Buffers: shared hit=35626 dirtied=142
11. 2,786.361 2,786.361 ↑ 1.0 105,542 1

Seq Scan on gfangyuan.production_test_uw4_nonleads (cost=0.00..83,520.42 rows=105,542 width=56) (actual time=0.402..2,786.361 rows=105,542 loops=1)

  • Output: production_test_uw4_nonleads.businessunit, production_test_uw4_nonleads.refi, production_test_uw4_nonleads.control_file_name, production_test_uw4_nonleads.credit_model_true, production_test_uw4_nonleads.loanid, production_test_uw4_nonleads.uwscore4_calculated, production_test_uw4_nonleads.python_calc_time
  • Buffers: shared hit=2 read=82463 dirtied=279
12. 1,998.984 1,998.984 ↑ 1.0 1 153,768

Index Scan using applications_name_idx on cloudlending.applications app (cost=0.56..2.19 rows=1 width=44) (actual time=0.012..0.013 rows=1 loops=153,768)

  • Output: app.cl_product_name, app.type_formula, app.total_score, app.bcs_score, app.name
  • Index Cond: (app.name = production_test_uw4_leads.loanid)
  • Buffers: shared hit=774752 dirtied=167
13. 184.615 607.099 ↑ 1.0 672,752 1

Hash (cost=22,255.52..22,255.52 rows=672,752 width=25) (actual time=607.099..607.099 rows=672,752 loops=1)

  • Output: pds.truefpd, pds.pd28_ratio, pds.pd70_ratio, pds.loanid
  • Buckets: 1048576 Batches: 1 Memory Usage: 45206kB
  • Buffers: shared hit=5018 read=10510
14. 422.484 422.484 ↑ 1.0 672,752 1

Seq Scan on tableau_reporting.tbl_pd_rate_loan_level pds (cost=0.00..22,255.52 rows=672,752 width=25) (actual time=0.010..422.484 rows=672,752 loops=1)

  • Output: pds.truefpd, pds.pd28_ratio, pds.pd70_ratio, pds.loanid
  • Buffers: shared hit=5018 read=10510
15. 558.486 9,827.345 ↓ 2.2 2,032,240 1

Hash (cost=570,805.84..570,805.84 rows=936,395 width=31) (actual time=9,827.345..9,827.345 rows=2,032,240 loops=1)

  • Output: foooo.createddate, foooo.created_at, foooo.name
  • Buckets: 2097152 (originally 1048576) Batches: 1 (originally 1) Memory Usage: 141415kB
  • Buffers: shared hit=4090038 read=116355 dirtied=32
16. 200.916 9,268.859 ↓ 2.2 2,032,240 1

Subquery Scan on foooo (cost=191,234.03..570,805.84 rows=936,395 width=31) (actual time=4,173.533..9,268.859 rows=2,032,240 loops=1)

  • Output: foooo.createddate, foooo.created_at, foooo.name
  • Buffers: shared hit=4090038 read=116355 dirtied=32
17. 3,003.357 9,067.943 ↓ 2.2 2,032,240 1

Hash Join (cost=191,234.03..561,441.89 rows=936,395 width=127) (actual time=4,173.532..9,067.943 rows=2,032,240 loops=1)

  • Output: NULL::bigint, NULL::text, a.name, a.createddate, NULL::jsonb, NULL::bigint, cached_reports.created_at, NULL::timestamp without time zone, a.lastmodifieddate
  • Hash Cond: (cached_reports.id = report_usages.cached_report_id)
  • Buffers: shared hit=4090038 read=116355 dirtied=32
18. 1,891.957 1,891.957 ↑ 1.0 7,023,190 1

Seq Scan on public.cached_reports (cost=0.00..334,486.21 rows=7,028,721 width=16) (actual time=0.006..1,891.957 rows=7,023,190 loops=1)

  • Output: cached_reports.id, cached_reports.report_key, cached_reports.report_name, cached_reports.body, cached_reports.used, cached_reports.created_at, cached_reports.updated_at, cached_reports.originating_org, cached_reports.report_type, cached_reports.invalidated_at
  • Buffers: shared hit=200221 read=63978 dirtied=31
19. 557.915 4,172.629 ↓ 2.2 2,032,240 1

Hash (cost=179,529.09..179,529.09 rows=936,395 width=35) (actual time=4,172.629..4,172.629 rows=2,032,240 loops=1)

  • Output: a.name, a.createddate, a.lastmodifieddate, report_usages.cached_report_id
  • Buckets: 2097152 (originally 1048576) Batches: 1 (originally 1) Memory Usage: 151338kB
  • Buffers: shared hit=3889814 read=52377 dirtied=1
20. 1,614.656 3,614.714 ↓ 2.2 2,032,240 1

Merge Join (cost=1.11..179,529.09 rows=936,395 width=35) (actual time=0.042..3,614.714 rows=2,032,240 loops=1)

  • Output: a.name, a.createddate, a.lastmodifieddate, report_usages.cached_report_id
  • Merge Cond: (a.origination_id = report_usages.origination_id)
  • Buffers: shared hit=3889814 read=52377 dirtied=1
21. 999.504 999.504 ↑ 1.0 2,558,177 1

Index Only Scan using applications_origination_id_cd_idx on cloudlending.applications a (cost=0.56..155,582.45 rows=2,568,646 width=67) (actual time=0.023..999.504 rows=2,558,177 loops=1)

  • Output: a.origination_id, a.createddate, a.name, a.lastmodifieddate
  • Index Cond: (a.createddate >= '2018-10-24 05:00:00+00'::timestamp with time zone)
  • Heap Fetches: 130471
  • Buffers: shared hit=2491389 read=52323
22. 1,000.554 1,000.554 ↑ 1.0 2,033,191 1

Index Scan using report_usages_origination_id on public.report_usages (cost=0.55..81,401.18 rows=2,033,286 width=40) (actual time=0.009..1,000.554 rows=2,033,191 loops=1)

  • Output: report_usages.id, report_usages.cached_report_id, report_usages.report_type, report_usages.origination_id, report_usages.disabled_at, report_usages.created_at, report_usages.updated_at
  • Buffers: shared hit=1398425 read=54 dirtied=1
23. 0.000 8,129.168 ↓ 0.0 0 2,032,292

Nested Loop Left Join (cost=0.85..2.61 rows=2 width=55) (actual time=0.003..0.004 rows=0 loops=2,032,292)

  • Output: uwscore_bcs_scores.uwscore2_calculated, uwscore_bcs_scores.bcscore2_calculated, uwscore_bcs_scores.uwscore3_calculated, uwscore_bcs_scores.bcscore3_calculated, uwscore_bcs_scores.loanid, allapps.denygrp
  • Inner Unique: true
  • Buffers: shared hit=2838664 read=284
24. 2,032.292 6,096.876 ↓ 0.0 0 2,032,292

Append (cost=0.42..1.06 rows=2 width=47) (actual time=0.002..0.003 rows=0 loops=2,032,292)

  • Buffers: shared hit=1808665 read=270
25. 2,032.292 2,032.292 ↓ 0.0 0 2,032,292

Index Scan using ubs_loanid_control_file_idx on gfangyuan.uwscore_bcs_scores (cost=0.42..0.57 rows=1 width=47) (actual time=0.001..0.001 rows=0 loops=2,032,292)

  • Output: uwscore_bcs_scores.uwscore2_calculated, uwscore_bcs_scores.bcscore2_calculated, uwscore_bcs_scores.uwscore3_calculated, uwscore_bcs_scores.bcscore3_calculated, uwscore_bcs_scores.loanid
  • Index Cond: (uwscore_bcs_scores.loanid = production_test_uw4_leads.loanid)
  • Buffers: shared hit=916826 read=245
26. 2,032.292 2,032.292 ↓ 0.0 0 2,032,292

Index Scan using lubs_loanid_control_file_idx on gfangyuan.leads_uwscore_bcs_scores (cost=0.42..0.49 rows=1 width=47) (actual time=0.001..0.001 rows=0 loops=2,032,292)

  • Output: leads_uwscore_bcs_scores.uwscore2_calculated, leads_uwscore_bcs_scores.bcscore2_calculated, leads_uwscore_bcs_scores.uwscore3_calculated, leads_uwscore_bcs_scores.bcscore3_calculated, leads_uwscore_bcs_scores.loanid
  • Index Cond: (leads_uwscore_bcs_scores.loanid = production_test_uw4_leads.loanid)
  • Buffers: shared hit=891839 read=25
27. 2,572.230 2,572.230 ↑ 1.0 1 257,223

Index Scan using all_allapps_2737_loanid_idx on public.all_allapps allapps (cost=0.43..0.77 rows=1 width=21) (actual time=0.010..0.010 rows=1 loops=257,223)

  • Output: allapps.custid, allapps.loanid, allapps.admethod, allapps.amount, allapps.fund, allapps.appldate, allapps.refi, allapps.store, allapps.assigned, allapps.denyreason, allapps.adjappl, allapps.decisiondate, allapps.decisionadmin, allapps.denygrp, allapps.adgrp, allapps.clname, allapps.control_file, allapps.businessunit, allapps.cl_payroll_type, allapps.cl_income_type, allapps.cl_monthlyincome, allapps.lastmodifiedate
  • Index Cond: (uwscore_bcs_scores.loanid = allapps.loanid)
  • Buffers: shared hit=1029999 read=14
Planning time : 6.067 ms
Execution time : 41,539.976 ms