explain.depesz.com

PostgreSQL's explain analyze made readable

Result: S69u

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Limit (cost=1,116,069,040.70..1,116,069,040.75 rows=20 width=1,534) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Sort (cost=1,116,069,040.70..1,116,866,071.95 rows=318,812,503 width=1,534) (actual rows= loops=)

  • Sort Key: "companyCte"."nameCompany
3. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=1,090,050,866.95..1,104,397,429.58 rows=318,812,503 width=1,502) (actual rows= loops=)

  • Group Key: "companyCte"."idCompany", "companyCte"."nameCompany", "companyCte"."nameComKana", "companyCte"."idSanCompany", "accComEntityCte"."indxCustomer", "applicantBranchCte"."branchName", "applicantBranchCte"."empName", "branchCte".branch, "financialCte"."numOfCreditReport", "financialCte"."latestDataDate", "creditInProcessCte"."creditInProcess", "reSurveyStatusCte"."reSurveyStatus
4.          

CTE accComEntityCte

5. 0.000 0.000 ↓ 0.0

Seq Scan on acc_com_entity (cost=0.00..71.28 rows=1,984 width=17) (actual rows= loops=)

  • Filter: (indx_customer IS NOT NULL)
6.          

CTE companyCte

7. 0.000 0.000 ↓ 0.0

Seq Scan on san_com_info (cost=0.00..604.01 rows=73 width=100) (actual rows= loops=)

  • Filter: ((id_company = unified_id) AND ("substring"((id_san_company)::text, 1, 1) <> '#'::text))
8.          

CTE applicantBranchCte

9. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=8.25..204.73 rows=888 width=104) (actual rows= loops=)

  • Hash Cond: ((acc_com_credit.ac_request_branch)::text = (branch_master.branch_code)::text)
10. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=6.31..191.08 rows=888 width=26) (actual rows= loops=)

  • Hash Cond: (acc_com_credit.ac_request = employee_mst.emp_code)
11. 0.000 0.000 ↓ 0.0

Seq Scan on acc_com_credit (cost=0.00..172.56 rows=888 width=16) (actual rows= loops=)

  • Filter: ((status_code < 7) OR (status_code > 10))
12. 0.000 0.000 ↓ 0.0

Hash (cost=4.47..4.47 rows=147 width=18) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Seq Scan on employee_mst (cost=0.00..4.47 rows=147 width=18) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Hash (cost=1.42..1.42 rows=42 width=98) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Seq Scan on branch_master (cost=0.00..1.42 rows=42 width=98) (actual rows= loops=)

16.          

CTE branchCte

17. 0.000 0.000 ↓ 0.0

HashAggregate (cost=6,713.71..6,982.55 rows=1,034 width=86) (actual rows= loops=)

  • Group Key: san_card_data.id_company
18. 0.000 0.000 ↓ 0.0

Hash Join (cost=7.66..6,708.54 rows=1,034 width=86) (actual rows= loops=)

  • Hash Cond: ((san_card_data.ac_user)::text = (card_holder_view.ac_user)::text)
19. 0.000 0.000 ↓ 0.0

Seq Scan on san_card_data (cost=0.00..6,485.12 rows=54,777 width=13) (actual rows= loops=)

  • Filter: ((enum_info_validation)::text <> 'D'::text)
20. 0.000 0.000 ↓ 0.0

Hash (cost=7.65..7.65 rows=1 width=164) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Subquery Scan on card_holder_view (cost=1.68..7.65 rows=1 width=164) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

WindowAgg (cost=1.68..7.64 rows=1 width=164) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.68..7.63 rows=1 width=164) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.54..6.60 rows=4 width=86) (actual rows= loops=)

  • Hash Cond: (e.emp_adpcode = (b.branch_code)::bpchar)
25. 0.000 0.000 ↓ 0.0

Seq Scan on employee_mst e (cost=0.00..4.47 rows=147 width=8) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Hash (cost=1.52..1.52 rows=1 width=98) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Seq Scan on branch_master b (cost=0.00..1.52 rows=1 width=98) (actual rows= loops=)

  • Filter: (use_state = 1)
28. 0.000 0.000 ↓ 0.0

Index Only Scan using ref_card_holder_pkey on ref_card_holder h (cost=0.14..0.25 rows=1 width=86) (actual rows= loops=)

  • Index Cond: ((emp_code = e.emp_code) AND (ac_user IS NOT NULL))
  • Filter: ((ac_user)::text <> ''::text)
29.          

CTE financialCte

30. 0.000 0.000 ↓ 0.0

HashAggregate (cost=172.06..179.23 rows=717 width=36) (actual rows= loops=)

  • Group Key: acc_com_credit_1.id_com_entity
31. 0.000 0.000 ↓ 0.0

Seq Scan on acc_com_credit acc_com_credit_1 (cost=0.00..165.80 rows=835 width=36) (actual rows= loops=)

  • Filter: ((path_report IS NOT NULL) AND ((path_report)::text <> ''::text))
32.          

CTE reSurveyStatusCte

33. 0.000 0.000 ↓ 0.0

Seq Scan on san_com_info san_com_info_1 (cost=0.00..4,151.54 rows=14,629 width=4) (actual rows= loops=)

34.          

CTE creditInProcessCte

35. 0.000 0.000 ↓ 0.0

Seq Scan on san_com_info san_com_info_2 (cost=0.00..4,151.54 rows=14,629 width=4) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Sort (cost=1,090,034,229.49..1,090,831,260.74 rows=318,812,503 width=1,502) (actual rows= loops=)

  • Sort Key: "companyCte"."idCompany", "companyCte"."nameCompany", "companyCte"."nameComKana", "companyCte"."idSanCompany", "accComEntityCte"."indxCustomer", "applicantBranchCte"."branchName", "applicantBranchCte"."empName", "branchCte".branch, "financialCte"."numOfCreditReport", "financialCte"."latestDataDate", "creditInProcessCte"."creditInProcess", "reSurveyStatusCte"."reSurve...
37. 0.000 0.000 ↓ 0.0

Hash Join (cost=15,777.31..4,349,869.60 rows=318,812,503 width=1,502) (actual rows= loops=)

  • Hash Cond: ("creditInProcessCte"."idCompany" = "companyCte"."idCompany")
38. 0.000 0.000 ↓ 0.0

Merge Join (cost=2,609.31..18,733.02 rows=1,070,038 width=72) (actual rows= loops=)

  • Merge Cond: ("creditInProcessCte"."idCompany" = "reSurveyStatusCte"."idCompany")
39. 0.000 0.000 ↓ 0.0

Sort (cost=1,304.65..1,341.23 rows=14,629 width=36) (actual rows= loops=)

  • Sort Key: "creditInProcessCte"."idCompany
40. 0.000 0.000 ↓ 0.0

CTE Scan on "creditInProcessCte" (cost=0.00..292.58 rows=14,629 width=36) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Sort (cost=1,304.65..1,341.23 rows=14,629 width=36) (actual rows= loops=)

  • Sort Key: "reSurveyStatusCte"."idCompany
42. 0.000 0.000 ↓ 0.0

CTE Scan on "reSurveyStatusCte" (cost=0.00..292.58 rows=14,629 width=36) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Hash (cost=1,773.14..1,773.14 rows=59,589 width=1,438) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=293.35..1,773.14 rows=59,589 width=1,438) (actual rows= loops=)

  • Hash Cond: ("accComEntityCte"."idCompany" = "companyCte"."idCompany")
45. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=257.94..866.07 rows=31,582 width=228) (actual rows= loops=)

  • Merge Cond: ("accComEntityCte"."idComEntity" = "applicantBranchCte"."idComEntity")
46. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=196.69..313.31 rows=7,113 width=68) (actual rows= loops=)

  • Merge Cond: ("accComEntityCte"."idComEntity" = "financialCte"."idComEntity")
47. 0.000 0.000 ↓ 0.0

Sort (cost=148.35..153.31 rows=1,984 width=52) (actual rows= loops=)

  • Sort Key: "accComEntityCte"."idComEntity
48. 0.000 0.000 ↓ 0.0

CTE Scan on "accComEntityCte" (cost=0.00..39.68 rows=1,984 width=52) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Sort (cost=48.35..50.14 rows=717 width=20) (actual rows= loops=)

  • Sort Key: "financialCte"."idComEntity
50. 0.000 0.000 ↓ 0.0

CTE Scan on "financialCte" (cost=0.00..14.34 rows=717 width=20) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Sort (cost=61.25..63.47 rows=888 width=168) (actual rows= loops=)

  • Sort Key: "applicantBranchCte"."idComEntity
52. 0.000 0.000 ↓ 0.0

CTE Scan on "applicantBranchCte" (cost=0.00..17.76 rows=888 width=168) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Hash (cost=30.70..30.70 rows=377 width=1,214) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=2.37..30.70 rows=377 width=1,214) (actual rows= loops=)

  • Hash Cond: ("branchCte"."idCompany" = "companyCte"."idCompany")
55. 0.000 0.000 ↓ 0.0

CTE Scan on "branchCte" (cost=0.00..20.68 rows=1,034 width=36) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Hash (cost=1.46..1.46 rows=73 width=1182) | -> CTE Scan on "companyCte" (cost=0.00..1.46 rows=73 width=1,182) (actual rows= loops=)