explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MhcF : exec0204

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 94,757.417 ↑ 1.0 1 1

Limit (cost=12,248,277.33..12,248,277.52 rows=1 width=362) (actual time=94,757.416..94,757.417 rows=1 loops=1)

  • CREDIT MDO ACCEPT'::character varying WHEN 'E'::text THEN 'ERROR'::character varying WHEN 'N'::text THEN 'NO DECISION AVAILABLE'::character varying ELSE atscorp.scmrfl END), (CASE c.rcdecn WHEN 'A'::text
  • THEN 'ACCEPT'::character varying WHEN 'C'::text THEN 'CONDITIONAL'::character varying WHEN 'R'::text THEN 'DECLINE'::character varying ELSE c.rcdecn END), (CASE atscorp.scjscor WHEN '0'::numeric THEN atscorp.scscor ELSE atscorp.scjscor END), ad.dhscode, atreqfpx.aqream, atreqfpx.aqsose1, "F#DECLCODF".fdvlue, "F#DECLCODF_1".fdvlue, (COALESCE("F#DECLCODF_1".fdvlue, "F#DECLCODF".fdvlue)), (CASE atreqfpx.aqmini2 WHEN 'Y'::text THEN CASE scorexact.sfmini WHEN '0'::numeric THEN scorexact.samini ELSE scorexact.sfmini END ELSE CASE scorexact.samini WHEN '0'::numeric THEN scorexact.sfmini ELSE scorexact.samini END END), (CASE atreqfpx.aqmini2 WHEN 'Y'::text THEN CASE scorexact.sfmaxi WHEN '0'::numeric THEN scorexact.samaxi ELSE scorexact.sfmaxi END ELSE CASE scorexact.samaxi WHEN '0'::numeric THEN scorexact.sfmaxi ELSE scorexact.samaxi END END), (CASE atreqfpx.aqmini2 WHEN 'Y'::text THEN CASE scorexact.sfmini WHEN '0'::numeric THEN scorexact.sacns2 ELSE scorexact.sfcns2 END ELSE CASE scorexact.sacns2 WHEN '0'::numeric THEN scorexact.sfcns2 ELSE scorexact.sacns2 END END), (COALESCE((SubPlan 1), atreqfpx.aqmis1)), (((atreqfpx.aqptno2)::text || (atreqfpx.aqptnm3)::text)), (CASE atreqfpx.aqmini2 WHEN 'Y'::text THEN 'AFF'::text ELSE 'MKT'::text END), a."UNITS#", a.actcde
  • END), ad.dhscode, atreqfpx.aqream, atreqfpx.aqsose1, "F#DECLCODF".fdvlue, "F#DECLCODF_1".fdvlue, (COALESCE("F#DECLCODF_1".fdvlue, "F#DECLCODF".fdvlue)), (CASE atreqfpx.aqmini2 WHEN 'Y'::text THEN CASE scorexact.sfmini WHEN '0'::numeric THEN scorexact.samini ELSE scorexact.sfmini END ELSE CASE scorexact.samini WHEN '0'::numeric THEN scorexact.sfmini ELSE scorexact.samini END END), (CASE atreqfpx.aqmini2 WHEN 'Y'::text THEN CASE scorexact.sfmaxi WHEN '0'::numeric THEN scorexact.samaxi ELSE scorexact.sfmaxi END ELSE CASE scorexact.samaxi WHEN '0'::numeric THEN scorexact.sfmaxi ELSE scorexact.samaxi END END), (CASE atreqfpx.aqmini2 WHEN 'Y'::text THEN CASE scorexact.sfmini WHEN '0'::numeric THEN scorexact.sacns2 ELSE scorexact.sfcns2 END ELSE CASE scorexact.sacns2 WHEN '0'::numeric THEN scorexact.sfcns2 ELSE
  • scorexact.sacns2 END END), (COALESCE((SubPlan 1), atreqfpx.aqmis1)), (((atreqfpx.aqptno2)::text || (atreqfpx.aqptnm3)::text)), (CASE atreqfpx.aqmini2 WHEN 'Y'::text THEN 'AFF'::text ELSE 'MKT'::text END), a."UNITS#", a.actcde
  • loops=1)
  • rows=1 loops=2)
2.          

CTE executive_detail

3. 0.008 268.230 ↓ 1.5 3 1

Unique (cost=1,521,762.73..1,521,762.88 rows=2 width=584) (actual time=268.223..268.230 rows=3 loops=1)

4. 0.039 268.222 ↓ 1.5 3 1

Sort (cost=1,521,762.73..1,521,762.73 rows=2 width=584) (actual time=268.222..268.222 rows=3 loops=1)

  • Sort Key: a.llord, hq.llord, r2.region_nm, a2.area_name, atreqfpx.aqdstm, atreqfpx."AQINV#", atreqfpx.aqfnam, atreqfpx.aqmini, (COALESCE(frptranp.xtlnam, atreqfpx.aqlnam)), atreqfpx.aqsose, (CASE atreqfpx."AQCO#S" WHEN 'Y'::text THEN 'CO-SIGNER'::text ELSE CASE atreqfpx.aqrapl WHEN 'C'::text THEN 'CO-APPLICANT'::text WHEN 'P'::text THEN 'APPLICANT'::text ELSE 'UNKNOWN'::text END END), atreqfpx."AQCO#S", atreqfpx.aqmoof, (CASE atscorp.scmrfl WHEN 'A'::text THEN 'ACCEPT'::character varying WHEN 'C'::text THEN 'CONDITIONAL'::character varying WHEN 'R'::text THEN 'DECLINE'::character varyingWHEN 'F'::text THEN 'MDO DECLINE'::character varying WHEN 'M'::text THEN 'NO CREDIT MDO CONDITIONAL'::character varying WHEN 'V'::text THEN 'NO CREDIT MDO ACCEPT'::character varying WHEN 'E'::text THEN 'ERROR'::character varying WHEN 'N'::text THEN 'NO DECISION AVAILABLE'::character varying ELSE atscorp.scmrfl END), (CASE c.rcdecn WHEN 'A'::text THEN 'ACCEPT'::character varying WHEN 'C'::text THEN 'CONDITIONAL'::character varying WHEN 'R'::text THEN 'DECLINE'::character varying ELSE c.rcdecn END), (CASE atscorp.scjscor WHEN '0'::numeric THEN atscorp.scscor ELSE atscorp.scjscor END), ad.dhscode, atreqfpx.aqream, atreqfpx.aqsose1, (COALESCE("F#DECLCODF_1".fdvlue, "F#DECLCODF".fdvlue, '0'::numeric)), (max(atreqfpx.aqinco)), (('1'::numeric / atreqfpx.aqmoof)), (CASE atreqfpx.aqmini2 WHEN 'Y'::text THEN CASE scorexact.sfmini WHEN '0'::numeric THEN scorexact.samini ELSE scorexact.sfmini END ELSE CASE scorexact.samini WHEN '0'::numeric THEN scorexact.sfmini ELSE scorexact.samini END END), (CASE atreqfpx.aqmini2WHEN 'Y'::text THEN CASE scorexact.sfmaxi WHEN '0'::numeric THEN scorexact.samaxi ELSE scorexact.sfmaxi END ELSE CASE scorexact.samaxi WHEN '0'::numeric THEN scorexact.sfmaxi ELSE scorexact.samaxi END END), (CASE atreqfpx.aqmini2 WHEN 'Y'::text THEN CASE scorexact.sfmini WHEN '0'::numeric THEN scorexact.sacns2 ELSE scorexact.sfcns2 END ELSE CASE scorexact.sacns2 WHEN '0'::numeric THEN scorexact.sfcns2 ELSE scorexact.sacns2 END END), (COALESCE((SubPlan 1), atreqfpx.aqmis1)), (((atreqfpx.aqptno2)::text || (atreqfpx.aqptnm3)::text)), (CASE atreqfpx.aqmini2 WHEN 'Y'::text THEN 'AFF'::text ELSE 'MKT'::text END), a."UNITS#", a.actcde
  • Sort Method: quicksort Memory: 25kB
5. 0.029 268.183 ↓ 1.5 3 1

GroupAggregate (cost=1,521,541.83..1,521,762.72 rows=2 width=584) (actual time=268.179..268.183 rows=3 loops=1)

  • Group Key: a."ACCT#", a.llord, hq.llord, a.hqtrs, r2.region_nm, a2.area_name, atreqfpx.aqdstm, atreqfpx."AQINV#", atreqfpx.aqrefn, atreqfpx.aqfnam, atreqfpx.aqmini, (COALESCE(frptranp.xtlnam, atreqfpx.aqlnam)), atreqfpx.aqsose, (CASE atreqfpx."AQCO#S" WHEN 'Y'::text THEN 'CO-SIGNER'::text ELSE CASE atreqfpx.aqrapl WHEN 'C'::text THEN 'CO-APPLICANT'::text WHEN 'P'::text THEN 'APPLICANT'::text ELSE 'UNKNOWN'::text END END), atreqfpx."AQCO#S", atreqfpx.aqmoof, atreqfpx.aqrapl, (CASE atscorp.scmrfl WHEN 'A'::text THEN 'ACCEPT'::character varying WHEN 'C'::text THEN 'CONDITIONAL'::character varying WHEN 'R'::text THEN 'DECLINE'::character varying WHEN 'F'::text THEN 'MDO DECLINE'::character varying WHEN 'M'::text THEN 'NO CREDIT MDO CONDITIONAL'::character varying WHEN 'V'::text THEN 'NO
6. 0.078 268.154 ↓ 3.0 6 1

Sort (cost=1,521,541.83..1,521,541.84 rows=2 width=460) (actual time=268.153..268.154 rows=6 loops=1)

  • Sort Key: a.llord, hq.llord, r2.region_nm, a2.area_name, atreqfpx.aqdstm, atreqfpx."AQINV#", atreqfpx.aqfnam, atreqfpx.aqmini, (COALESCE(frptranp.xtlnam, atreqfpx.aqlnam)), atreqfpx.aqsose, (CASE atreqfpx."AQCO#S" WHEN 'Y'::text THEN 'CO-SIGNER'::text ELSE CASE atreqfpx.aqrapl WHEN 'C'::text THEN 'CO-APPLICANT'::text WHEN 'P'::text THEN 'APPLICANT'::text ELSE 'UNKNOWN'::text END END), atreqfpx."AQCO#S", atreqfpx.aqmoof, atreqfpx.aqrapl, (CASE atscorp.scmrfl WHEN 'A'::text THEN 'ACCEPT'::character varying WHEN 'C'::text THEN 'CONDITIONAL'::character varying WHEN 'R'::text THEN'DECLINE'::character varying WHEN 'F'::text THEN 'MDO DECLINE'::character varying WHEN 'M'::text THEN 'NO CREDIT MDO CONDITIONAL'::character varying WHEN 'V'::text THEN 'NO CREDIT MDO ACCEPT'::character varying WHEN 'E'::text THEN 'ERROR'::character varying WHEN 'N'::text THEN 'NO DECISION AVAILABLE'::character varying ELSE atscorp.scmrfl END), (CASE c.rcdecn WHEN 'A'::text THEN 'ACCEPT'::character varying WHEN 'C'::text THEN 'CONDITIONAL'::character varying WHEN 'R'::text THEN 'DECLINE'::character varying ELSE c.rcdecn END), (CASE atscorp.scjscor WHEN '0'::numeric THEN atscorp.scscor ELSE atscorp.scjscor
  • Sort Method: quicksort Memory: 26kB
7. 0.035 268.076 ↓ 3.0 6 1

Nested Loop Left Join (cost=8,925.62..1,521,541.82 rows=2 width=460) (actual time=266.596..268.076 rows=6 loops=1)

8. 0.004 265.971 ↓ 2.0 2 1

Nested Loop (cost=8,925.06..1,521,225.09 rows=1 width=207) (actual time=265.860..265.971 rows=2 loops=1)

9. 0.004 265.937 ↓ 2.0 2 1

Nested Loop (cost=8,924.64..1,521,216.64 rows=1 width=186) (actual time=265.832..265.937 rows=2 loops=1)

10. 0.002 265.907 ↓ 2.0 2 1

Nested Loop (cost=8,924.21..1,521,208.18 rows=1 width=169) (actual time=265.808..265.907 rows=2 loops=1)

11. 0.007 265.679 ↓ 2.0 2 1

Nested Loop Left Join (cost=8,914.35..1,521,158.30 rows=1 width=101) (actual time=265.652..265.679 rows=2 loops=1)

12. 0.066 265.620 ↓ 2.0 2 1

Nested Loop Left Join (cost=8,913.78..1,521,149.71 rows=1 width=92) (actual time=265.607..265.620 rows=2 loops=1)

  • Join Filter: ((atreqfpx.aqrefn)::text = (ad.dhsrepid)::text)
  • -> Index Scan using idx_auto_scorexdchp__dhsrepid_dhstmstamp on scorexdchp ad (cost=0.56..8.58 rows=1 width=10) (actual time=0.030..0.030
13. 10.539 265.554 ↓ 2.0 2 1

Hash Right Join (cost=8,913.22..1,521,141.12 rows=1 width=90) (actual time=265.552..265.554 rows=2 loops=1)

  • Hash Cond: ((frptranp.xttxcn)::text = (atreqfpx."AQINV#")::text)
  • Index Cond: ((dhsrepid)::text = '52225849'::text)
14. 242.281 254.958 ↑ 5.4 87,188 1

Bitmap Heap Scan on frptranp (cost=8,845.38..1,519,302.19 rows=472,287 width=16) (actual time=19.463..254.958 rows=87,188 loops=1)

  • Recheck Cond: ((xtprco)::text = ANY ('{C05,C39}'::text[]))
  • Heap Blocks: exact=53354
15. 12.677 12.677 ↑ 5.4 87,188 1

Bitmap Index Scan on idx_far2001_frptranp__xtprco (cost=0.00..8,727.31 rows=472,287 width=0) (actual time=12.677..12.677 rows=87,188 loops=1)

  • Index Cond: ((xtprco)::text = ANY ('{C05,C39}'::text[]))
16. 0.057 0.057 ↓ 2.0 2 1

Hash (cost=67.83..67.83 rows=1 width=83) (actual time=0.057..0.057 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • -> Index Scan using idx_auto_atreqfpx__aqrefn on atreqfpx (cost=0.56..67.83 rows=1 width=83) (actual time=0.050..0.053 rows=2
  • Index Cond: ((aqrefn)::text = '52225849'::text)
  • Filter: ((aqacct)::text = '00251'::text)
17. 0.052 0.052 ↑ 1.0 1 2

Index Scan using idx_auto_atscorp__scinv on atscorp (cost=0.56..8.58 rows=1 width=18) (actual time=0.026..0.026 rows=1 loops=2)

  • Index Cond: ((atreqfpx."AQINV#")::text = ("SCINV#")::text)
  • Filter: ((scmrfl)::text <> 'E'::text)
18. 0.002 0.226 ↑ 1.0 1 2

Nested Loop Left Join (cost=9.87..49.87 rows=1 width=74) (actual time=0.112..0.113 rows=1 loops=2)

  • Join Filter: ((a."ACCT#")::text = ("F#DECLCODF_1".fdacct)::text)
19. 0.002 0.206 ↑ 1.0 1 2

Nested Loop Left Join (cost=9.44..41.41 rows=1 width=71) (actual time=0.102..0.103 rows=1 loops=2)

  • Join Filter: ((a.hqtrs)::text = ("F#DECLCODF".fdacct)::text)
20. 0.004 0.154 ↑ 1.0 1 2

Nested Loop Left Join (cost=9.02..32.95 rows=1 width=68) (actual time=0.076..0.077 rows=1 loops=2)

  • Join Filter: ((a."ACCT#")::text = (m2.prop_id)::text)
21. 0.038 0.038 ↑ 1.0 1 2

Index Scan using idx_regis_acctpf__acct on acctpf a (cost=0.42..8.45 rows=1 width=33) (actual time=0.018..0.019 rows=1 loops=2)

  • Index Cond: (("ACCT#")::text = '00251'::text)
  • Filter: ((hqtrs)::text = '00594'::text)
22. 0.002 0.112 ↓ 0.0 0 2

Nested Loop (cost=8.59..24.49 rows=1 width=41) (actual time=0.056..0.056 rows=0 loops=2)

23. 0.043 0.110 ↓ 0.0 0 2

Hash Join (cost=8.32..16.19 rows=1 width=35) (actual time=0.055..0.055 rows=0 loops=2)

  • Hash Cond: (((r2.mgmtco_id)::text = (m2.mgmtco_id)::text) AND ((r2.region_id)::text = (m2.region_id)::text))
24. 0.040 0.040 ↑ 2.0 139 2

Seq Scan on idsregionp r2 (cost=0.00..5.78 rows=278 width=34) (actual time=0.008..0.020 rows=139 loops=2)

25. 0.000 0.027 ↓ 0.0 0 1

Hash (cost=8.30..8.30 rows=1 width=30) (actual time=0.027..0.027 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
26. 0.027 0.027 ↓ 0.0 0 1

Index Scan using idx_auto_idsorgidsp__prop_id_area_id on idsorgidsp m2 (cost=0.28..8.30 rows=1 width=30) (actual time=0.027..0.027 rows=0 loops=1)

  • Index Cond: ((prop_id)::text = '00251'::text)
27. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_auto_idsareap__area_id on idsareap a2 (cost=0.28..8.29 rows=1 width=24) (never executed)

  • Index Cond: ((area_id)::text = (m2.area_id)::text)
28. 0.050 0.050 ↓ 0.0 0 2

Index Scan using idx_auto_fdeclcodf__fdacct_fdcode_fdaltc on "F#DECLCODF" (cost=0.42..8.45 rows=1 width=8) (actual time=0.025..0.025 rows=0 loops=2)

  • Index Cond: (((fdacct)::text = '00594'::text) AND ((fdcode)::text = 'APP'::text) AND ((fdaltc)::text = 'RIN'::text))
29. 0.018 0.018 ↓ 0.0 0 2

Index Scan using idx_auto_fdeclcodf__fdacct_fdcode_fdaltc on "F#DECLCODF" "F#DECLCODF_1" (cost=0.42..8.45 rows=1 width=8) (actual time=0.009..0.009 rows=0 loops=2)

  • Index Cond: (((fdacct)::text = '00251'::text) AND ((fdcode)::text = 'APP'::text) AND ((fdaltc)::text = 'RIN'::text))
30. 0.026 0.026 ↑ 1.0 1 2

Index Scan using idx_regis_acctpf__acct on acctpf hq (cost=0.42..8.44 rows=1 width=22) (actual time=0.013..0.013 rows=1 loops=2)

  • Index Cond: (("ACCT#")::text = '00594'::text)
31. 0.030 0.030 ↑ 1.0 1 2

Index Scan using idx_auto_scorexact__sactno on scorexact (cost=0.42..8.44 rows=1 width=27) (actual time=0.015..0.015 rows=1 loops=2)

  • Index Cond: ((sactno)::text = '00251'::text)
32. 0.066 0.066 ↑ 8.3 3 2

Index Scan using idx_auto_crcreqpf__rcminv_rcrqno_rcinet on crcreqpf c (cost=0.56..95.81 rows=25 width=9) (actual time=0.025..0.033 rows=3 loops=2)

  • Index Cond: ((atreqfpx."AQINV#")::text = (rcminv)::text)
33.          

SubPlan (for Nested Loop Left Join)

34. 2.004 2.004 ↓ 0.0 0 6

Seq Scan on marketsrc ms (cost=0.00..110.28 rows=1 width=17) (actual time=0.334..0.334 rows=0 loops=6)

  • Filter: ((mkcode)::text = (atreqfpx.aqmis1)::text)
  • Rows Removed by Filter: 4582
35.          

CTE decision_sorts

36. 0.005 0.009 ↑ 1.0 5 1

HashAggregate (cost=0.12..0.17 rows=5 width=36) (actual time=0.008..0.009 rows=5 loops=1)

  • Group Key: (5), ('CRIMINAL DECLINE'::text)
37. 0.003 0.004 ↑ 1.0 5 1

Append (cost=0.00..0.10 rows=5 width=36) (actual time=0.001..0.004 rows=5 loops=1)

38. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=36) (actual time=0.000..0.000 rows=1 loops=1)

39. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=36) (actual time=0.000..0.000 rows=1 loops=1)

40. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=36) (actual time=0.001..0.001 rows=1 loops=1)

41. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=36) (actual time=0.000..0.000 rows=1 loops=1)

42. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=36) (actual time=0.000..0.000 rows=1 loops=1)

43.          

CTE decisions

44. 14,270.472 68,789.687 ↑ 1.3 20,663,421 1

Hash Left Join (cost=2,464,799.98..10,101,953.57 rows=26,214,205 width=40) (actual time=20,531.406..68,789.687 rows=20,663,421 loops=1)

  • Hash Cond: ((atreqfpx_1."AQINV#")::text = (atscorp_1."SCINV#")::text)
45. 17,379.194 34,617.999 ↑ 1.5 17,670,509 1

Merge Left Join (cost=161,179.43..5,621,622.40 rows=26,214,205 width=18) (actual time=623.052..34,617.999 rows=17,670,509 loops=1)

  • Merge Cond: ((atreqfpx_1."AQINV#")::text = (c_1.rcminv)::text)
46. 2,331.844 2,331.844 ↓ 1.0 15,797,769 1

Index Only Scan using idx_auto_atreqfpx__aqinv_aqrefn_aqtdob on atreqfpx atreqfpx_1 (cost=0.56..957,835.75 rows=15,797,379 width=17) (actual time=0.039..2,331.844 rows=15,797,769 loops=1)

  • Heap Fetches: 96086
47. 3,857.239 14,906.961 ↓ 1.0 24,529,449 1

Materialize (cost=0.56..4,319,120.06 rows=24,504,310 width=9) (actual time=0.017..14,906.961 rows=24,529,449 loops=1)

48. 11,049.722 11,049.722 ↑ 1.0 24,262,378 1

Index Scan using idx_auto_crcreqpf__rcminv_rcrqno_rcinet on crcreqpf c_1 (cost=0.56..4,257,859.28 rows=24,504,310 width=9) (actual time=0.013..11,049.722 rows=24,262,378 loops=1)

49. 5,255.826 19,901.216 ↑ 1.0 34,253,726 1

Hash (cost=1,708,150.35..1,708,150.35 rows=34,256,256 width=10) (actual time=19,901.216..19,901.216 rows=34,253,726 loops=1)

  • Buckets: 1048576 Batches: 64 Memory Usage: 30566kB
50. 14,645.390 14,645.390 ↑ 1.0 34,253,726 1

Seq Scan on atscorp atscorp_1 (cost=0.00..1,708,150.35 rows=34,256,256 width=10) (actual time=0.014..14,645.390 rows=34,253,726 loops=1)

  • Filter: ((scmrfl)::text <> 'E'::text)
  • Rows Removed by Filter: 225862
51.          

CTE final_decisions

52. 14,368.857 91,374.928 ↓ 58,974.2 11,794,834 1

HashAggregate (cost=624,553.57..624,555.57 rows=200 width=40) (actual time=87,906.962..91,374.928 rows=11,794,834 loops=1)

  • Group Key: b.trx_number
53. 3,622.932 77,006.071 ↓ 157.7 20,663,421 1

Hash Right Join (cost=0.14..623,898.22 rows=131,071 width=40) (actual time=20,531.441..77,006.071 rows=20,663,421 loops=1)

  • Hash Cond: (b.lease_decision = a_1.decision)
54. 73,383.124 73,383.124 ↑ 1.3 20,663,421 1

CTE Scan on decisions b (cost=0.00..524,284.10 rows=26,214,205 width=68) (actual time=20,531.410..73,383.124 rows=20,663,421 loops=1)

55. 0.003 0.015 ↓ 5.0 5 1

Hash (cost=0.12..0.12 rows=1 width=36) (actual time=0.015..0.015 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
56. 0.012 0.012 ↓ 5.0 5 1

CTE Scan on decision_sorts a_1 (cost=0.00..0.12 rows=1 width=36) (actual time=0.010..0.012 rows=5 loops=1)

  • Filter: ((sort_order >= 1) AND (sort_order <= 5))
57.          

CTE executive_summary

58. 0.058 94,757.244 ↓ 1.5 3 1

Hash Left Join (cost=0.23..5.01 rows=2 width=1,112) (actual time=94,331.590..94,757.244 rows=3 loops=1)

  • Hash Cond: (final_decisions.sort_order = decision_sorts.sort_order)
59. 791.718 94,757.173 ↓ 1.5 3 1

Hash Right Join (cost=0.07..4.83 rows=2 width=1,084) (actual time=94,331.522..94,757.173 rows=3 loops=1)

  • Hash Cond: ((final_decisions.trx_number)::text = (executive_detail.trx_number)::text)
60. 93,697.213 93,697.213 ↓ 58,974.2 11,794,834 1

CTE Scan on final_decisions (cost=0.00..4.00 rows=200 width=40) (actual time=87,906.966..93,697.213 rows=11,794,834 loops=1)

61. 0.006 268.242 ↓ 1.5 3 1

Hash (cost=0.04..0.04 rows=2 width=1,080) (actual time=268.242..268.242 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
62. 268.236 268.236 ↓ 1.5 3 1

CTE Scan on executive_detail (cost=0.00..0.04 rows=2 width=1,080) (actual time=268.226..268.236 rows=3 loops=1)

63. 0.006 0.013 ↑ 1.0 5 1

Hash (cost=0.10..0.10 rows=5 width=36) (actual time=0.013..0.013 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
64. 0.007 0.007 ↑ 1.0 5 1

CTE Scan on decision_sorts (cost=0.00..0.10 rows=5 width=36) (actual time=0.006..0.007 rows=5 loops=1)

65. 0.085 94,757.415 ↑ 1.0 1 1

GroupAggregate (cost=0.12..0.31 rows=1 width=362) (actual time=94,757.415..94,757.415 rows=1 loops=1)

  • Group Key: executive_summary.account_number, executive_summary.account_name, executive_summary.lower_decision_point, executive_summary.upper_decision_point
66. 0.043 94,757.330 ↓ 3.0 3 1

Sort (cost=0.12..0.13 rows=1 width=222) (actual time=94,757.329..94,757.330 rows=3 loops=1)

  • Sort Key: executive_summary.account_number DESC, executive_summary.account_name, executive_summary.lower_decision_point, executive_summary.upper_decision_point
  • Sort Method: quicksort Memory: 25kB
67. 94,757.287 94,757.287 ↓ 3.0 3 1

CTE Scan on executive_summary (cost=0.00..0.12 rows=1 width=222) (actual time=94,331.627..94,757.287 rows=3 loops=1)

  • Filter: (((hq_id)::text = '00594'::text) AND (CASE length(((trx_date)::character varying)::text) WHEN 7 THEN concat(20, substr(((trx_date)::character varying)::text, 2, 2)) WHEN 6 THEN concat(19, substr(((trx_date)::character varying)::text, 1, 2)) ELSE NULL::text END = '2018'::text))