explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0mRA

Settings
# exclusive inclusive rows x rows loops node
1. 38.720 15,244.821 ↓ 10,158.0 10,158 1

Sort (cost=147.08..147.09 rows=1 width=443) (actual time=15,242.025..15,244.821 rows=10,158 loops=1)

  • Sort Key: ((regexp_replace((sal.emp_code)::text, '[^0-9]'::text, ''::text, 'g'::text))::integer)
  • Sort Method: external merge Disk: 3320kB
2.          

Initplan (for Sort)

3. 0.005 0.005 ↑ 1.0 1 1

Index Scan using txn_address_index on txn_address add (cost=0.28..8.30 rows=1 width=31) (actual time=0.005..0.005 rows=1 loops=1)

  • Index Cond: ((addresstypeid = 18001) AND (partyid = 9563))
4. 0.005 0.005 ↑ 1.0 1 1

Index Scan using txn_party_pkey on txn_party p (cost=0.28..8.30 rows=1 width=11) (actual time=0.005..0.005 rows=1 loops=1)

  • Index Cond: (partyid = 9563)
5. 447.939 15,206.091 ↓ 10,158.0 10,158 1

Nested Loop Semi Join (cost=1.51..130.47 rows=1 width=443) (actual time=3,428.631..15,206.091 rows=10,158 loops=1)

  • Join Filter: ((sal.month)::text = (unnest('{April,February,March}'::text[])))
6. 12.643 7,231.074 ↓ 10,158.0 10,158 1

Nested Loop Semi Join (cost=1.51..30.09 rows=1 width=507) (actual time=3,427.056..7,231.074 rows=10,158 loops=1)

  • Join Filter: ((sal.month)::text = (unnest('{April,February,March}'::text[])))
7. 19.584 7,208.273 ↓ 10,158.0 10,158 1

Nested Loop (cost=1.51..27.33 rows=1 width=475) (actual time=3,427.047..7,208.273 rows=10,158 loops=1)

  • Join Filter: ((sal.siteid = att.siteid) AND (sal.contractorid = att.contractorid) AND ((sal.emp_code)::text = (att.emp_code)::text) AND ((sal.month)::text = (att.month)::text))
  • Rows Removed by Join Filter: 9
8. 22.860 7,127.741 ↓ 10,158.0 10,158 1

Nested Loop (cost=1.09..26.65 rows=1 width=494) (actual time=3,427.028..7,127.741 rows=10,158 loops=1)

  • Join Filter: ((lwfac.siteid = sal.siteid) AND ((lwfac.month)::text = (sal.month)::text))
  • Rows Removed by Join Filter: 18
9. 4.034 3,529.265 ↓ 10,158.0 10,158 1

Nested Loop (cost=0.67..23.71 rows=1 width=132) (actual time=3,426.983..3,529.265 rows=10,158 loops=1)

10. 3,433.809 3,433.809 ↓ 10,158.0 10,158 1

Function Scan on usf_cic_calculate_lwf_account_quarterwise lwfac (cost=0.25..15.25 rows=1 width=112) (actual time=3,426.945..3,433.809 rows=10,158 loops=1)

  • Filter: ((accountid = 9563) AND (year = 2019))
11. 91.422 91.422 ↑ 1.0 1 10,158

Index Scan using mst_rgst_employee_onetime_table_index on mst_rgst_employee_onetime o (cost=0.42..8.45 rows=1 width=20) (actual time=0.007..0.009 rows=1 loops=10,158)

  • Index Cond: (((emp_code)::text = (lwfac.emp_code)::text) AND (partyid = 9563))
  • Filter: ((lwfac.contractorid = employer) AND (lwfac.siteid = (branch_site_id)::integer))
  • Rows Removed by Filter: 2
12. 3,575.616 3,575.616 ↑ 1.0 1 10,158

Index Scan using mst_master_data_table_index on mst_master_data sal (cost=0.42..2.92 rows=1 width=374) (actual time=0.185..0.352 rows=1 loops=10,158)

  • Index Cond: ((account_id = 9563) AND (siteid = (o.branch_site_id)::integer) AND (contractorid = o.employer) AND (year = 2019) AND ((emp_code)::text = (o.emp_code)::text))
13. 60.948 60.948 ↑ 1.0 1 10,158

Index Scan using txn_attendence_table_index on txn_attendence att (cost=0.42..0.67 rows=1 width=65) (actual time=0.006..0.006 rows=1 loops=10,158)

  • Index Cond: (((emp_code)::text = (o.emp_code)::text) AND (siteid = (o.branch_site_id)::integer) AND (contractorid = o.employer) AND (account_id = 9563))
  • Filter: ((year)::integer = 2019)
14. 10.158 10.158 ↑ 100.0 1 10,158

Result (cost=0.00..0.51 rows=100 width=0) (actual time=0.001..0.001 rows=1 loops=10,158)

15. 0.000 0.000 ↑ 100.0 1 10,158

Result (cost=0.00..0.51 rows=100 width=0) (actual time=0.000..0.000 rows=1 loops=10,158)

16.          

SubPlan (for Nested Loop Semi Join)

17. 40.632 40.632 ↑ 1.0 1 10,158

Result (cost=0.00..0.06 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=10,158)

18. 40.632 40.632 ↑ 1.0 1 10,158

Result (cost=0.00..0.04 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=10,158)

19. 50.790 91.422 ↑ 1.0 1 10,158

Result (cost=0.04..0.11 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=10,158)

20.          

Initplan (for Result)

21. 40.632 40.632 ↑ 1.0 1 10,158

Result (cost=0.00..0.04 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=10,158)

22. 91.422 91.422 ↑ 1.0 1 10,158

Result (cost=0.00..0.10 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=10,158)

23. 30.474 30.474 ↑ 1.0 1 10,158

Result (cost=0.00..0.05 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=10,158)

24. 10.158 10.158 ↑ 1.0 1 10,158

Result (cost=0.00..0.04 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=10,158)

25. 30.474 30.474 ↑ 1.0 1 10,158

Result (cost=0.00..0.04 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=10,158)

26. 101.580 182.844 ↑ 1.0 1 10,158

Result (cost=0.13..0.25 rows=1 width=0) (actual time=0.018..0.018 rows=1 loops=10,158)

27.          

Initplan (for Result)

28. 40.632 40.632 ↑ 1.0 1 10,158

Result (cost=0.00..0.04 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=10,158)

29. 30.474 30.474 ↑ 1.0 1 10,158

Result (cost=0.00..0.05 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=10,158)

30. 10.158 10.158 ↑ 1.0 1 10,158

Result (cost=0.00..0.04 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=10,158)

31. 253.950 487.584 ↑ 1.0 1 10,158

Result (cost=9.57..9.79 rows=1 width=0) (actual time=0.048..0.048 rows=1 loops=10,158)

32.          

Initplan (for Result)

33. 81.264 81.264 ↑ 1.0 1 10,158

Index Scan using mst_rgst_employee_onetime_table_index on mst_rgst_employee_onetime o_1 (cost=0.42..8.45 rows=1 width=4) (actual time=0.007..0.008 rows=1 loops=10,158)

  • Index Cond: (((emp_code)::text = (sal.emp_code)::text) AND (partyid = 9563))
  • Filter: ((employer = sal.contractorid) AND ((branch_site_id)::integer = sal.siteid))
  • Rows Removed by Filter: 2
34. 152.370 152.370 ↑ 1.0 1 10,158

Seq Scan on mst_pf_esic_configurations (cost=0.00..1.13 rows=1 width=516) (actual time=0.010..0.015 rows=1 loops=10,158)

  • Filter: ((flag = 'pf'::text) AND ((concat(((('2019-'::text || (sal.month)::text) || '-'::text) || '01'::text)))::date >= (effective_start_date)::date) AND ((concat(((('2019-'::text || (sal.month)::text) || '-'::text) || '01'::text)) (...)
  • Rows Removed by Filter: 3
35. 172.686 518.058 ↑ 1.0 1 10,158

Result (cost=9.80..9.94 rows=1 width=0) (actual time=0.051..0.051 rows=1 loops=10,158)

36.          

Initplan (for Result)

37. 71.106 71.106 ↑ 1.0 1 10,158

Index Scan using mst_rgst_employee_onetime_table_index on mst_rgst_employee_onetime o_2 (cost=0.42..8.45 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=10,158)

  • Index Cond: (((emp_code)::text = (sal.emp_code)::text) AND (partyid = 9563))
  • Filter: ((employer = sal.contractorid) AND ((branch_site_id)::integer = sal.siteid))
  • Rows Removed by Filter: 2
38. 40.632 40.632 ↑ 1.0 1 10,158

Result (cost=0.00..0.04 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=10,158)

39. 20.316 20.316 ↑ 1.0 1 10,158

Result (cost=0.00..0.03 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=10,158)

40. 10.158 10.158 ↑ 1.0 1 10,158

Result (cost=0.00..0.04 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=10,158)

41. 40.632 50.790 ↑ 1.0 1 10,158

Result (cost=0.03..0.07 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=10,158)

42.          

Initplan (for Result)

43. 10.158 10.158 ↑ 1.0 1 10,158

Result (cost=0.00..0.03 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=10,158)

44. 40.632 40.632 ↑ 1.0 1 10,158

Result (cost=0.00..0.04 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=10,158)

45. 111.738 111.738 ↑ 1.0 1 10,158

Seq Scan on mst_pf_esic_configurations mst_pf_esic_configurations_1 (cost=0.00..1.12 rows=1 width=516) (actual time=0.009..0.011 rows=1 loops=10,158)

  • Filter: ((flag = 'esic'::text) AND ((concat(((('2019-'::text || (sal.month)::text) || '-'::text) || '01'::text)))::date >= (effective_start_date)::date) AND ((concat(((('2019-'::text || (sal.month)::text) || '-'::text) || '01'::text (...)
  • Rows Removed by Filter: 3
46. 792.324 975.168 ↑ 1.0 1 10,158

Result (cost=0.25..0.51 rows=1 width=0) (actual time=0.096..0.096 rows=1 loops=10,158)

47.          

Initplan (for Result)

48. 91.422 182.844 ↑ 1.0 1 10,158

Result (cost=0.13..0.25 rows=1 width=0) (actual time=0.018..0.018 rows=1 loops=10,158)

49.          

Initplan (for Result)

50. 50.790 50.790 ↑ 1.0 1 10,158

Result (cost=0.00..0.04 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=10,158)

51. 30.474 30.474 ↑ 1.0 1 10,158

Result (cost=0.00..0.05 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=10,158)

52. 10.158 10.158 ↑ 1.0 1 10,158

Result (cost=0.00..0.04 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=10,158)

53. 325.056 1,990.968 ↑ 1.0 1 10,158

Result (cost=20.02..20.31 rows=1 width=0) (actual time=0.196..0.196 rows=1 loops=10,158)

54.          

Initplan (for Result)

55. 91.422 91.422 ↑ 1.0 1 10,158

Index Scan using mst_rgst_employee_onetime_table_index on mst_rgst_employee_onetime o_3 (cost=0.42..8.45 rows=1 width=4) (actual time=0.008..0.009 rows=1 loops=10,158)

  • Index Cond: (((emp_code)::text = (sal.emp_code)::text) AND (partyid = 9563))
  • Filter: ((employer = sal.contractorid) AND ((branch_site_id)::integer = sal.siteid))
  • Rows Removed by Filter: 2
56. 142.212 142.212 ↑ 1.0 1 10,158

Seq Scan on mst_pf_esic_configurations mst_pf_esic_configurations_2 (cost=0.00..1.13 rows=1 width=516) (actual time=0.009..0.014 rows=1 loops=10,158)

  • Filter: ((flag = 'pf'::text) AND ((concat(((('2019-'::text || (sal.month)::text) || '-'::text) || '01'::text)))::date >= (effective_start_date)::date) AND ((concat(((('2019-'::text || (sal.month)::text) || '-'::text) || '01'::text)) (...)
  • Rows Removed by Filter: 3
57. 162.528 507.900 ↑ 1.0 1 10,158

Result (cost=9.80..9.94 rows=1 width=0) (actual time=0.050..0.050 rows=1 loops=10,158)

58.          

Initplan (for Result)

59. 71.106 71.106 ↑ 1.0 1 10,158

Index Scan using mst_rgst_employee_onetime_table_index on mst_rgst_employee_onetime o_4 (cost=0.42..8.45 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=10,158)

  • Index Cond: (((emp_code)::text = (sal.emp_code)::text) AND (partyid = 9563))
  • Filter: ((employer = sal.contractorid) AND ((branch_site_id)::integer = sal.siteid))
  • Rows Removed by Filter: 2
60. 50.790 50.790 ↑ 1.0 1 10,158

Result (cost=0.00..0.04 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=10,158)

61. 10.158 10.158 ↑ 1.0 1 10,158

Result (cost=0.00..0.03 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=10,158)

62. 10.158 10.158 ↑ 1.0 1 10,158

Result (cost=0.00..0.04 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=10,158)

63. 40.632 50.790 ↑ 1.0 1 10,158

Result (cost=0.03..0.07 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=10,158)

64.          

Initplan (for Result)

65. 10.158 10.158 ↑ 1.0 1 10,158

Result (cost=0.00..0.03 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=10,158)

66. 40.632 40.632 ↑ 1.0 1 10,158

Result (cost=0.00..0.04 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=10,158)

67. 111.738 111.738 ↑ 1.0 1 10,158

Seq Scan on mst_pf_esic_configurations mst_pf_esic_configurations_3 (cost=0.00..1.12 rows=1 width=516) (actual time=0.008..0.011 rows=1 loops=10,158)

  • Filter: ((flag = 'esic'::text) AND ((concat(((('2019-'::text || (sal.month)::text) || '-'::text) || '01'::text)))::date >= (effective_start_date)::date) AND ((concat(((('2019-'::text || (sal.month)::text) || '-'::text) || '0 (...)
  • Rows Removed by Filter: 3
68. 741.534 924.378 ↑ 1.0 1 10,158

Result (cost=0.25..0.51 rows=1 width=0) (actual time=0.091..0.091 rows=1 loops=10,158)

69.          

Initplan (for Result)

70. 91.422 182.844 ↑ 1.0 1 10,158

Result (cost=0.13..0.25 rows=1 width=0) (actual time=0.018..0.018 rows=1 loops=10,158)

71.          

Initplan (for Result)

72. 50.790 50.790 ↑ 1.0 1 10,158

Result (cost=0.00..0.04 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=10,158)

73. 30.474 30.474 ↑ 1.0 1 10,158

Result (cost=0.00..0.05 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=10,158)

74. 10.158 10.158 ↑ 1.0 1 10,158

Result (cost=0.00..0.04 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=10,158)

75. 40.632 2,183.970 ↑ 1.0 1 10,158

Result (cost=20.55..20.57 rows=1 width=0) (actual time=0.215..0.215 rows=1 loops=10,158)

76.          

Initplan (for Result)

77. 101.580 182.844 ↑ 1.0 1 10,158

Result (cost=0.08..0.20 rows=1 width=0) (actual time=0.018..0.018 rows=1 loops=10,158)

78.          

Initplan (for Result)

79. 50.790 50.790 ↑ 1.0 1 10,158

Result (cost=0.00..0.04 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=10,158)

80. 30.474 30.474 ↑ 1.0 1 10,158

Result (cost=0.00..0.05 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=10,158)

81. 304.740 1,950.336 ↑ 1.0 1 10,158

Result (cost=20.02..20.31 rows=1 width=0) (actual time=0.192..0.192 rows=1 loops=10,158)

82.          

Initplan (for Result)

83. 91.422 91.422 ↑ 1.0 1 10,158

Index Scan using mst_rgst_employee_onetime_table_index on mst_rgst_employee_onetime o_5 (cost=0.42..8.45 rows=1 width=4) (actual time=0.008..0.009 rows=1 loops=10,158)

  • Index Cond: (((emp_code)::text = (sal.emp_code)::text) AND (partyid = 9563))
  • Filter: ((employer = sal.contractorid) AND ((branch_site_id)::integer = sal.siteid))
  • Rows Removed by Filter: 2
84. 142.212 142.212 ↑ 1.0 1 10,158

Seq Scan on mst_pf_esic_configurations mst_pf_esic_configurations_4 (cost=0.00..1.13 rows=1 width=516) (actual time=0.008..0.014 rows=1 loops=10,158)

  • Filter: ((flag = 'pf'::text) AND ((concat(((('2019-'::text || (sal.month)::text) || '-'::text) || '01'::text)))::date >= (effective_start_date)::date) AND ((concat(((('2019-'::text || (sal.month)::text) || '-'::text) || '01' (...)
  • Rows Removed by Filter: 3
85. 152.370 497.742 ↑ 1.0 1 10,158

Result (cost=9.80..9.94 rows=1 width=0) (actual time=0.049..0.049 rows=1 loops=10,158)

86.          

Initplan (for Result)

87. 71.106 71.106 ↑ 1.0 1 10,158

Index Scan using mst_rgst_employee_onetime_table_index on mst_rgst_employee_onetime o_6 (cost=0.42..8.45 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=10,158)

  • Index Cond: (((emp_code)::text = (sal.emp_code)::text) AND (partyid = 9563))
  • Filter: ((employer = sal.contractorid) AND ((branch_site_id)::integer = sal.siteid))
  • Rows Removed by Filter: 2
88. 50.790 50.790 ↑ 1.0 1 10,158

Result (cost=0.00..0.04 rows=1 width=0) (actual time=0.004..0.005 rows=1 loops=10,158)

89. 20.316 20.316 ↑ 1.0 1 10,158

Result (cost=0.00..0.03 rows=1 width=0) (actual time=0.001..0.002 rows=1 loops=10,158)

90. 10.158 10.158 ↑ 1.0 1 10,158

Result (cost=0.00..0.04 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=10,158)

91. 40.632 50.790 ↑ 1.0 1 10,158

Result (cost=0.03..0.07 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=10,158)

92.          

Initplan (for Result)

93. 10.158 10.158 ↑ 1.0 1 10,158

Result (cost=0.00..0.03 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=10,158)

94. 40.632 40.632 ↑ 1.0 1 10,158

Result (cost=0.00..0.04 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=10,158)

95. 101.580 101.580 ↑ 1.0 1 10,158

Seq Scan on mst_pf_esic_configurations mst_pf_esic_configurations_5 (cost=0.00..1.12 rows=1 width=516) (actual time=0.008..0.010 rows=1 loops=10,158)

  • Filter: ((flag = 'esic'::text) AND ((concat(((('2019-'::text || (sal.month)::text) || '-'::text) || '01'::text)))::date >= (effective_start_date)::date) AND ((concat(((('2019-'::text || (sal.month)::text) || '-'::tex (...)
  • Rows Removed by Filter: 3
96. 731.376 914.220 ↑ 1.0 1 10,158

Result (cost=0.25..0.51 rows=1 width=0) (actual time=0.090..0.090 rows=1 loops=10,158)

97.          

Initplan (for Result)

98. 91.422 182.844 ↑ 1.0 1 10,158

Result (cost=0.13..0.25 rows=1 width=0) (actual time=0.018..0.018 rows=1 loops=10,158)

99.          

Initplan (for Result)

100. 50.790 50.790 ↑ 1.0 1 10,158

Result (cost=0.00..0.04 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=10,158)

101. 30.474 30.474 ↑ 1.0 1 10,158

Result (cost=0.00..0.05 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=10,158)

102. 10.158 10.158 ↑ 1.0 1 10,158

Result (cost=0.00..0.04 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=10,158)

103. 10.158 10.158 ↑ 1.0 1 10,158

Result (cost=0.00..0.04 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=10,158)

104. 30.474 30.474 ↑ 1.0 1 10,158

Index Scan using txn_party_pkey on txn_party a (cost=0.28..8.30 rows=1 width=11) (actual time=0.003..0.003 rows=1 loops=10,158)

  • Index Cond: (partyid = sal.contractorid)
105. 802.482 822.798 ↑ 1.0 1 10,158

Result (cost=27.32..27.33 rows=1 width=0) (actual time=0.081..0.081 rows=1 loops=10,158)

106.          

Initplan (for Result)

107. 0.000 20.316 ↑ 1.0 1 10,158

Index Scan using txn_party_pkey on txn_party t (cost=19.30..27.32 rows=1 width=11) (actual time=0.001..0.002 rows=1 loops=10,158)

  • Index Cond: (partyid = $176)
108.          

Initplan (for Index Scan)

109. 761.850 761.850 ↑ 1.0 1 10,158

Seq Scan on txn_site s (cost=0.00..19.02 rows=1 width=4) (actual time=0.053..0.075 rows=1 loops=10,158)

  • Filter: ((customername <> 0) AND (partyid = sal.siteid))
  • Rows Removed by Filter: 667
Planning time : 37.158 ms
Execution time : 15,251.316 ms