explain.depesz.com

PostgreSQL's explain analyze made readable

Result: C4M9 : exec2-0204

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 88,600.192 ↑ 1.0 1 1

Limit (cost=12,250,246.50..12,250,246.54 rows=1 width=216) (actual time=88,600.192..88,600.192 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.007 255.277 ↓ 1.5 3 1

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

4. 0.054 255.270 ↓ 1.5 3 1

Sort (cost=1,521,762.73..1,521,762.73 rows=2 width=584) (actual time=255.270..255.270 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.031 255.216 ↓ 1.5 3 1

GroupAggregate (cost=1,521,541.83..1,521,762.72 rows=2 width=584) (actual time=255.212..255.216 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.079 255.185 ↓ 3.0 6 1

Sort (cost=1,521,541.83..1,521,541.84 rows=2 width=460) (actual time=255.185..255.185 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.036 255.106 ↓ 3.0 6 1

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

8. 0.003 253.072 ↓ 2.0 2 1

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

9. 0.002 253.039 ↓ 2.0 2 1

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

10. 0.003 253.011 ↓ 2.0 2 1

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

11. 0.006 252.808 ↓ 2.0 2 1

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

12. 0.066 252.748 ↓ 2.0 2 1

Nested Loop Left Join (cost=8,913.78..1,521,149.71 rows=1 width=92) (actual time=252.735..252.748 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. 7.925 252.682 ↓ 2.0 2 1

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

  • Hash Cond: ((frptranp.xttxcn)::text = (atreqfpx."AQINV#")::text)
  • Index Cond: ((dhsrepid)::text = '52225849'::text)
14. 233.651 244.681 ↑ 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=17.579..244.681 rows=87,188 loops=1)

  • Recheck Cond: ((xtprco)::text = ANY ('{C05,C39}'::text[]))
  • Heap Blocks: exact=53354
15. 11.030 11.030 ↑ 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=11.030..11.030 rows=87,188 loops=1)

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

Hash (cost=67.83..67.83 rows=1 width=83) (actual time=0.076..0.076 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.069..0.071 rows=2
  • Index Cond: ((aqrefn)::text = '52225849'::text)
  • Filter: ((aqacct)::text = '00251'::text)
17. 0.054 0.054 ↑ 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.027 rows=1 loops=2)

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

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

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

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

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

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

  • Join Filter: ((a."ACCT#")::text = (m2.prop_id)::text)
21. 0.034 0.034 ↑ 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.017..0.017 rows=1 loops=2)

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

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

23. 0.043 0.106 ↓ 0.0 0 2

Hash Join (cost=8.32..16.19 rows=1 width=35) (actual time=0.053..0.053 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.038 0.038 ↑ 2.0 139 2

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

25. 0.000 0.025 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
26. 0.025 0.025 ↓ 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.025..0.025 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.032 0.032 ↓ 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.016..0.016 rows=0 loops=2)

  • Index Cond: (((fdacct)::text = '00594'::text) AND ((fdcode)::text = 'APP'::text) AND ((fdaltc)::text = 'RIN'::text))
29. 0.020 0.020 ↓ 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.010..0.010 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.024..0.033 rows=3 loops=2)

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

SubPlan (for Nested Loop Left Join)

34. 1.932 1.932 ↓ 0.0 0 6

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

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

CTE decision_sorts

36. 0.006 0.010 ↑ 1.0 5 1

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

  • Group Key: (5), ('CRIMINAL DECLINE'::text)
37. 0.002 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.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=36) (actual time=0.000..0.001 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.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)

41. 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)

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. 13,223.027 66,535.653 ↑ 1.3 20,665,403 1

Hash Left Join (cost=2,464,902.64..10,103,810.34 rows=26,218,917 width=40) (actual time=19,564.511..66,535.653 rows=20,665,403 loops=1)

  • Hash Cond: ((atreqfpx_1."AQINV#")::text = (atscorp_1."SCINV#")::text)
45. 17,360.440 34,336.690 ↑ 1.5 17,672,491 1

Merge Left Join (cost=161,207.51..5,623,036.61 rows=26,218,917 width=18) (actual time=581.737..34,336.690 rows=17,672,491 loops=1)

  • Merge Cond: ((atreqfpx_1."AQINV#")::text = (c_1.rcminv)::text)
46. 2,306.915 2,306.915 ↓ 1.0 15,799,599 1

Index Only Scan using idx_auto_atreqfpx__aqinv_aqrefn_aqtdob on atreqfpx atreqfpx_1 (cost=0.56..958,437.47 rows=15,799,183 width=17) (actual time=0.044..2,306.915 rows=15,799,599 loops=1)

  • Heap Fetches: 97961
47. 3,741.393 14,669.335 ↓ 1.0 24,530,560 1

Materialize (cost=0.56..4,319,873.17 rows=24,508,714 width=9) (actual time=0.015..14,669.335 rows=24,530,560 loops=1)

48. 10,927.942 10,927.942 ↑ 1.0 24,263,489 1

Index Scan using idx_auto_crcreqpf__rcminv_rcrqno_rcinet on crcreqpf c_1 (cost=0.56..4,258,601.39 rows=24,508,714 width=9) (actual time=0.010..10,927.942 rows=24,263,489 loops=1)

49. 5,219.253 18,975.936 ↑ 1.0 34,254,879 1

Hash (cost=1,708,205.19..1,708,205.19 rows=34,257,356 width=10) (actual time=18,975.936..18,975.936 rows=34,254,879 loops=1)

  • Buckets: 1048576 Batches: 64 Memory Usage: 30566kB
50. 13,756.683 13,756.683 ↑ 1.0 34,254,879 1

Seq Scan on atscorp atscorp_1 (cost=0.00..1,708,205.19 rows=34,257,356 width=10) (actual time=0.014..13,756.683 rows=34,254,879 loops=1)

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

CTE final_decisions

52. 10,650.268 85,229.222 ↓ 58,981.4 11,796,273 1

HashAggregate (cost=624,665.84..624,667.84 rows=200 width=40) (actual time=82,172.670..85,229.222 rows=11,796,273 loops=1)

  • Group Key: b.trx_number
53. 3,547.984 74,578.954 ↓ 157.6 20,665,403 1

Hash Right Join (cost=0.14..624,010.37 rows=131,095 width=40) (actual time=19,564.546..74,578.954 rows=20,665,403 loops=1)

  • Hash Cond: (b.lease_decision = a_1.decision)
54. 71,030.953 71,030.953 ↑ 1.3 20,665,403 1

CTE Scan on decisions b (cost=0.00..524,378.34 rows=26,218,917 width=68) (actual time=19,564.514..71,030.953 rows=20,665,403 loops=1)

55. 0.004 0.017 ↓ 5.0 5 1

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

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

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

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

CTE executive_summary

58. 0.053 88,600.017 ↓ 1.5 3 1

Hash Left Join (cost=0.23..5.01 rows=2 width=1,112) (actual time=88,160.114..88,600.017 rows=3 loops=1)

  • Hash Cond: (final_decisions.sort_order = decision_sorts.sort_order)
59. 811.604 88,599.952 ↓ 1.5 3 1

Hash Right Join (cost=0.07..4.83 rows=2 width=1,084) (actual time=88,160.051..88,599.952 rows=3 loops=1)

  • Hash Cond: ((final_decisions.trx_number)::text = (executive_detail.trx_number)::text)
60. 87,533.059 87,533.059 ↓ 58,981.4 11,796,273 1

CTE Scan on final_decisions (cost=0.00..4.00 rows=200 width=40) (actual time=82,172.673..87,533.059 rows=11,796,273 loops=1)

61. 0.006 255.289 ↓ 1.5 3 1

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

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

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

63. 0.005 0.012 ↑ 1.0 5 1

Hash (cost=0.10..0.10 rows=5 width=36) (actual time=0.012..0.012 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.005..0.007 rows=5 loops=1)

65. 0.129 88,600.190 ↑ 1.0 1 1

Aggregate (cost=0.25..0.28 rows=1 width=216) (actual time=88,600.190..88,600.190 rows=1 loops=1)

66. 88,600.061 88,600.061 ↓ 3.0 3 1

CTE Scan on executive_summary (cost=0.00..0.12 rows=1 width=76) (actual time=88,160.151..88,600.061 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))