explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PENl : current

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

Hash Left Join (cost=4,753,026.00..8,875,543,702.33 rows=462,557 width=1,130) (actual rows= loops=)

  • Hash Cond: (sub.currentproviderid = currentsup.parametervalueid)
2. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=4,752,823.05..5,142,138.69 rows=462,557 width=560) (actual rows= loops=)

  • Hash Cond: (co.blocktypeparamid = block.parametervalueid)
3. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=4,752,620.11..5,140,721.52 rows=462,557 width=549) (actual rows= loops=)

  • Hash Cond: (lasthistory.productcodeparamid = lastprod.parametervalueid)
4. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=4,752,417.16..5,139,300.22 rows=462,557 width=534) (actual rows= loops=)

  • Hash Cond: (currenthistory.productcodeparamid = currentprod.parametervalueid)
5. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=4,752,214.22..5,137,878.93 rows=462,557 width=519) (actual rows= loops=)

  • Hash Cond: (firsthistory.productcodeparamid = firstprod.parametervalueid)
6. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=4,752,011.27..5,136,457.63 rows=462,557 width=504) (actual rows= loops=)

  • Hash Cond: ((COALESCE(gc_dgo.netarea, gc_usr.netarea))::text = (netarea.netarea)::text)
7. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=4,752,007.25..5,135,213.66 rows=462,557 width=500) (actual rows= loops=)

  • Hash Cond: (co.contractid = q_1.contractid)
8. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=4,286,376.79..4,667,848.03 rows=462,557 width=488) (actual rows= loops=)

  • Hash Cond: (co.contractid = q.contractid)
9. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=3,681,545.97..4,061,281.46 rows=462,557 width=483) (actual rows= loops=)

  • Hash Cond: (dp.deliverypointid = gc_usr.deliverypointid)
  • Join Filter: (((cdp.todate IS NULL) AND (gc_usr.todate IS NULL)) OR ((cdp.fromdate = gc_usr.fromdate) AND (cdp.todate = gc_usr.todate)) OR ((cdp.todate IS NOT NULL) AND (gc_usr.fromdate < cdp.todate) AND (( (...)
10. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=3,632,709.49..3,941,724.69 rows=462,557 width=446) (actual rows= loops=)

  • Hash Cond: (dp.deliverypointid = lc_usr.deliverypointid)
  • Join Filter: (((cdp.todate IS NULL) AND (lc_usr.todate IS NULL)) OR ((lc_usr.fromdate = cdp.fromdate) AND (lc_usr.todate = cdp.todate)) OR ((cdp.todate IS NOT NULL) AND (lc_usr.fromdate < cdp.todate) (...)
11. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=3,632,708.04..3,939,988.62 rows=462,557 width=442) (actual rows= loops=)

  • Hash Cond: (dp.deliverypointid = gc_dgo.deliverypointid)
  • Join Filter: (((cdp.todate IS NULL) AND (gc_dgo.todate IS NULL)) OR ((cdp.todate IS NOT NULL) AND (gc_dgo.fromdate < cdp.todate) AND ((gc_dgo.todate IS NULL) OR (gc_dgo.todate >= cdp.todate))))
12. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=3,572,518.73..3,809,118.17 rows=462,557 width=405) (actual rows= loops=)

  • Hash Cond: ((SubPlan 27) = pc_usr.physicalconfigid)
13. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=3,540,048.58..3,689,703.01 rows=462,557 width=401) (actual rows= loops=)

  • Hash Cond: (dp.deliverypointid = pc_dgo.deliverypointid)
  • Join Filter: (((cdp.todate IS NULL) AND (pc_dgo.todate IS NULL)) OR ((cdp.todate IS NOT NULL) AND (pc_dgo.fromdate < cdp.todate) AND ((pc_dgo.todate IS NULL) OR (pc_dgo.todate >= cdp (...)
14. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=3,506,372.59..3,591,639.46 rows=462,557 width=397) (actual rows= loops=)

  • Hash Cond: ((brpj.brp)::text = (brp.externalid)::text)
15. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=3,506,364.75..3,583,684.32 rows=462,557 width=399) (actual rows= loops=)

  • Hash Cond: (lc_dgo.deliverypointid = dp.deliverypointid)
  • Join Filter: (((cdp.todate IS NULL) AND (lc_dgo.todate IS NULL)) OR ((cdp.todate IS NOT NULL) AND (lc_dgo.fromdate < cdp.todate) AND ((lc_dgo.todate IS NULL) OR (lc_dgo.t (...)
16. 0.000 0.000 ↓ 0.0

Seq Scan on logicalconfig lc_dgo (cost=0.00..35,406.52 rows=573,285 width=16) (actual rows= loops=)

  • Filter: ((NOT deleted) AND ((sourceid)::text = 'DGO'::text) AND ((status)::text = 'ACTUAL'::text))
17. 0.000 0.000 ↓ 0.0

Hash (cost=3,476,641.78..3,476,641.78 rows=462,557 width=395) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,506,332.96..3,476,641.78 rows=462,557 width=395) (actual rows= loops=)

  • Hash Cond: (((dp.gridoperator)::text = (dgo_encom.gridoperator)::text) AND ((dp.regulator)::text = (dgo_hgz.regulator)::text) AND ((dp.market)::text = (dgo_hg (...)
19. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,506,209.03..3,470,274.06 rows=462,557 width=292) (actual rows= loops=)

  • Hash Cond: (per.deliveryperiodid = brpj.deliveryperiodid)
  • Join Filter: (((cdp.todate IS NULL) AND (brpj.todate IS NULL)) OR ((cdp.fromdate = cdp.todate) AND (brpj.fromdate = cdp.fromdate)) OR ((cdp.todate IS NO (...)
20. 0.000 0.000 ↓ 0.0

Merge Right Join (cost=1,487,802.45..3,402,268.56 rows=462,557 width=283) (actual rows= loops=)

  • Merge Cond: (freeaddressesdetail.addressid = dp.addressid)
21. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.13..4,146,077.87 rows=4,285 width=54) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.85..4,138,821.28 rows=4,285 width=107) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Index Scan using freeadddetail_addressid_idx on freeaddressesdetail (cost=0.42..4,130,279.17 rows=4,285 width=39) (actual rows= loops=)

  • Filter: ((((addressid)::text || ';'::text) || (addressdetailnbr)::text) = (SubPlan 26))
24.          

SubPlan (for Index Scan)

25. 0.000 0.000 ↓ 0.0

Limit (cost=4.76..4.77 rows=1 width=36) (actual rows= loops=)

26.          

Initplan (for Limit)

27. 0.000 0.000 ↓ 0.0

Index Scan using property_pkey on property (cost=0.27..2.29 rows=1 width=7) (actual rows= loops=)

  • Index Cond: ((propertycode)::text = 'defaultCustomerLanguageCode'::text)
28. 0.000 0.000 ↓ 0.0

Sort (cost=2.47..2.48 rows=1 width=36) (actual rows= loops=)

  • Sort Key: (CASE WHEN ((freeaddressesdetail_1.language)::text = ($43)::text) THEN 1 WHEN ((freeaddressesdetail_1.la (...)
  • -> Index Scan using freeadddetail_addressid_idx on freeaddressesdetail freeaddressesdetail_1 (cost=0.42..2.46 ro (...)
  • Index Cond: (addressid = freeaddressesdetail.addressid)
29. 0.000 0.000 ↓ 0.0

Index Scan using freeaddresses_pkey on freeaddresses (cost=0.42..1.99 rows=1 width=72) (actual rows= loops=)

  • Index Cond: (addressid = freeaddressesdetail.addressid)
30. 0.000 0.000 ↓ 0.0

Index Scan using town_pkey on town t (cost=0.28..1.69 rows=1 width=22) (actual rows= loops=)

  • Index Cond: (((country)::text = (freeaddresses.country)::text) AND ((postalcode)::text = (freeaddresses.postalcode)::text) AND (townco (...)
  • Filter: ((postalcode)::text <> 'UNKNOWN'::text)
31. 0.000 0.000 ↓ 0.0

Materialize (cost=1,487,801.32..1,490,114.11 rows=462,557 width=237) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Sort (cost=1,487,801.32..1,488,957.72 rows=462,557 width=237) (actual rows= loops=)

  • Sort Key: dp.addressid
33. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=341,642.10..1,384,648.39 rows=462,557 width=237) (actual rows= loops=)

  • Hash Cond: (per.deliverypointid = dp.deliverypointid)
34. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=322,370.95..1,335,445.02 rows=462,557 width=198) (actual rows= loops=)

  • Hash Cond: ((co.contractid = lasthistory.contractid) AND ((SubPlan 21) = lasthistory.contracthistentry))
35. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=275,432.72..1,218,605.09 rows=462,557 width=178) (actual rows= loops=)

  • Hash Cond: ((co.contractid = currenthistory.contractid) AND ((SubPlan 19) = currenthistory.contracthistentry))
36. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=228,494.50..1,104,359.53 rows=462,557 width=158) (actual rows= loops=)

  • Hash Cond: (cdp.contractdeliveryid = sub.contractdeliveryid)
37. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=203,618.87..1,056,554.68 rows=462,557 width=142) (actual rows= loops=)

  • Hash Cond: (cdp.deliveryperiodid = per.deliveryperiodid)
38. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=185,441.15..1,016,680.74 rows=462,557 width=138) (actual rows= loops=)

  • Hash Cond: ((co.contractid = cdp.contractid) AND ((SubPlan 22) = cdp.contractdeliveryid))
39. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=158,233.35..911,262.64 rows=462,557 width=118) (actual rows= loops=)

  • Hash Cond: (cj.contractid = co.contractid)
40. 0.000 0.000 ↓ 0.0

Seq Scan on contractjournal cj (cost=0.00..744,809.20 rows=3,716 width=9) (actual rows= loops=)

  • Filter: (journalentry = (SubPlan 24))
41.          

SubPlan (for Seq Scan)

42. 0.000 0.000 ↓ 0.0

Result (cost=0.95..0.96 rows=1 width=4) (actual rows= loops=)

43.          

Initplan (for Result)

44. 0.000 0.000 ↓ 0.0

Limit (cost=0.42..0.95 rows=1 width=4) (actual rows= loops=)

  • -> Index Only Scan Backward using contractjournal_pkey on contractjou (...)
  • Index Cond: ((contractid = cj.contractid) AND (journalentry IS N (...)
45. 0.000 0.000 ↓ 0.0

Hash (cost=144,320.39..144,320.39 rows=462,557 width=113) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=58,505.13..144,320.39 rows=462,557 width=113) (actual rows= loops=)

  • Hash Cond: (co.contractstatusparamid = stat.parametervalueid)
47. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=58,302.19..142,899.09 rows=462,557 width=93) (actual rows= loops=)

  • Hash Cond: (co.supplierid = s.supplierid)
48. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=58,279.59..141,653.09 rows=462,557 width=65) (actual rows= loops=)

  • Hash Cond: ((co.contractid = firsthistory.contractid) AND ((SubPlan (...)
49. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=11,341.36..40,171.17 rows=462,557 width=45) (actual rows= loops=)

  • Hash Cond: (co.customerid = cu.customerid)
  • -> Seq Scan on contract co (cost=0.00..19238.57 rows=462,557 (...)
50. 0.000 0.000 ↓ 0.0

Hash (cost=7,251.05..7,251.05 rows=235,305 width=14) (actual rows= loops=)

  • -> Seq Scan on customer cu (cost=0.00..7251.05 rows=23 (...)
51. 0.000 0.000 ↓ 0.0

Hash (cost=23,378.29..23,378.29 rows=1,078,929 width=28) (actual rows= loops=)

  • -> Seq Scan on contracthistory firsthistory (cost=0.00..2337 (...)
52.          

SubPlan (for Hash Left Join)

53. 0.000 0.000 ↓ 0.0

Result (cost=1.57..1.58 rows=1 width=4) (actual rows= loops=)

54.          

Initplan (for Result)

55. 0.000 0.000 ↓ 0.0

Limit (cost=0.43..1.57 rows=1 width=4) (actual rows= loops=)

  • -> Index Only Scan using "Key4" on contracthistory (...)
  • Index Cond: ((contractid = co.contractid) AND (...)
56. 0.000 0.000 ↓ 0.0

Hash (cost=15.60..15.60 rows=560 width=36) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Seq Scan on supplier s (cost=0.00..15.60 rows=560 width=36) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Hash (cost=187.42..187.42 rows=1,242 width=28) (actual rows= loops=)

  • -> Seq Scan on parametervalue stat (cost=0.00..187.42 rows=1,242 width=28 (...)
59. 0.000 0.000 ↓ 0.0

Hash (cost=17,452.32..17,452.32 rows=467,632 width=20) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Seq Scan on contractdelivery cdp (cost=0.00..17,452.32 rows=467,632 width=20) (actual rows= loops=)

61.          

SubPlan (for Hash Left Join)

62. 0.000 0.000 ↓ 0.0

Limit (cost=2.45..2.45 rows=1 width=8) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Sort (cost=2.45..2.45 rows=1 width=8) (actual rows= loops=)

  • Sort Key: cdp2_1.todate DESC
  • -> Index Scan using contractdelively_contract_id_idx on contractdelivery cdp2_1 (c (...)
  • Index Cond: (contractid = co.contractid)
64. 0.000 0.000 ↓ 0.0

Hash (cost=11,845.43..11,845.43 rows=385,943 width=8) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

Seq Scan on deliveryperiod per (cost=0.00..11,845.43 rows=385,943 width=8) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

Hash (cost=16,289.28..16,289.28 rows=467,628 width=24) (actual rows= loops=)

67. 0.000 0.000 ↓ 0.0

Seq Scan on subscription sub (cost=0.00..16,289.28 rows=467,628 width=24) (actual rows= loops=)

68. 0.000 0.000 ↓ 0.0

Hash (cost=23,378.29..23,378.29 rows=1,078,929 width=28) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

Seq Scan on contracthistory currenthistory (cost=0.00..23,378.29 rows=1,078,929 width=28) (actual rows= loops=)

70.          

SubPlan (for Hash Left Join)

71. 0.000 0.000 ↓ 0.0

Result (cost=1.58..1.59 rows=1 width=4) (actual rows= loops=)

72.          

Initplan (for Result)

73. 0.000 0.000 ↓ 0.0

Limit (cost=0.43..1.58 rows=1 width=4) (actual rows= loops=)

74. 0.000 0.000 ↓ 0.0

Index Scan Backward using "Key4" on contracthistory ch2_1 (cost=0.43..5.03 rows=4 width=4) (actual rows= loops=)

  • Index Cond: ((contractid = co.contractid) AND (contracthistentry IS NOT NULL))
  • Filter: (startdate < now())
75. 0.000 0.000 ↓ 0.0

Hash (cost=23,378.29..23,378.29 rows=1,078,929 width=28) (actual rows= loops=)

76. 0.000 0.000 ↓ 0.0

Seq Scan on contracthistory lasthistory (cost=0.00..23,378.29 rows=1,078,929 width=28) (actual rows= loops=)

77.          

SubPlan (for Hash Left Join)

78. 0.000 0.000 ↓ 0.0

Result (cost=1.57..1.58 rows=1 width=4) (actual rows= loops=)

79.          

Initplan (for Result)

80. 0.000 0.000 ↓ 0.0

Limit (cost=0.43..1.57 rows=1 width=4) (actual rows= loops=)

81. 0.000 0.000 ↓ 0.0

Index Only Scan Backward using "Key4" on contracthistory ch2_2 (cost=0.43..5.01 rows=4 width=4) (actual rows= loops=)

  • Index Cond: ((contractid = co.contractid) AND (contracthistentry IS NOT NULL))
82. 0.000 0.000 ↓ 0.0

Hash (cost=10,987.18..10,987.18 rows=389,118 width=43) (actual rows= loops=)

83. 0.000 0.000 ↓ 0.0

Seq Scan on deliverypoint dp (cost=0.00..10,987.18 rows=389,118 width=43) (actual rows= loops=)

84. 0.000 0.000 ↓ 0.0

Hash (cost=10,478.81..10,478.81 rows=409,981 width=25) (actual rows= loops=)

85. 0.000 0.000 ↓ 0.0

Seq Scan on deliveryperiodbrpjournal brpj (cost=0.00..10,478.81 rows=409,981 width=25) (actual rows= loops=)

86. 0.000 0.000 ↓ 0.0

Hash (cost=123.20..123.20 rows=42 width=155) (actual rows= loops=)

87. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=13.61..123.20 rows=42 width=155) (actual rows= loops=)

88. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=13.19..20.51 rows=42 width=114) (actual rows= loops=)

  • Hash Cond: (dgo_hgz.gridoperatorid = dgoac.actorid)
89. 0.000 0.000 ↓ 0.0

Hash Join (cost=5.34..12.54 rows=42 width=87) (actual rows= loops=)

  • Hash Cond: (a.actorid = dgo_hgz.gridoperatorid)
90. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.92..8.97 rows=54 width=79) (actual rows= loops=)

  • Hash Cond: ((a.externalid)::text = (dgo_encom.gridoperator)::text)
91. 0.000 0.000 ↓ 0.0

Seq Scan on actor a (cost=0.00..6.82 rows=82 width=24) (actual rows= loops=)

92. 0.000 0.000 ↓ 0.0

Hash (cost=1.41..1.41 rows=41 width=64) (actual rows= loops=)

93. 0.000 0.000 ↓ 0.0

Seq Scan on gridoperator dgo_encom (cost=0.00..1.41 rows=41 width=64) (actual rows= loops=)

94. 0.000 0.000 ↓ 0.0

Hash (cost=2.63..2.63 rows=63 width=12) (actual rows= loops=)

95. 0.000 0.000 ↓ 0.0

Seq Scan on gridoperator dgo_hgz (cost=0.00..2.63 rows=63 width=12) (actual rows= loops=)

96. 0.000 0.000 ↓ 0.0

Hash (cost=6.82..6.82 rows=82 width=31) (actual rows= loops=)

97. 0.000 0.000 ↓ 0.0

Seq Scan on actor dgoac (cost=0.00..6.82 rows=82 width=31) (actual rows= loops=)

98. 0.000 0.000 ↓ 0.0

Index Scan using freeaddresses_pkey on freeaddresses dgoadd (cost=0.43..2.45 rows=1 width=49) (actual rows= loops=)

  • Index Cond: (addressid = dgoac.addressid)
99. 0.000 0.000 ↓ 0.0

Hash (cost=6.82..6.82 rows=82 width=20) (actual rows= loops=)

100. 0.000 0.000 ↓ 0.0

Seq Scan on actor brp (cost=0.00..6.82 rows=82 width=20) (actual rows= loops=)

101. 0.000 0.000 ↓ 0.0

Hash (cost=23,699.10..23,699.10 rows=573,911 width=16) (actual rows= loops=)

102. 0.000 0.000 ↓ 0.0

Seq Scan on physicalconfig pc_dgo (cost=0.00..23,699.10 rows=573,911 width=16) (actual rows= loops=)

  • Filter: ((NOT deleted) AND ((sourceid)::text = 'DGO'::text) AND ((status)::text = 'ACTUAL'::text))
103. 0.000 0.000 ↓ 0.0

Hash (cost=19,854.40..19,854.40 rows=768,940 width=4) (actual rows= loops=)

104. 0.000 0.000 ↓ 0.0

Seq Scan on physicalconfig pc_usr (cost=0.00..19,854.40 rows=768,940 width=4) (actual rows= loops=)

105.          

SubPlan (for Hash Left Join)

106. 0.000 0.000 ↓ 0.0

Limit (cost=1.46..1.47 rows=1 width=4) (actual rows= loops=)

107. 0.000 0.000 ↓ 0.0

Sort (cost=1.46..1.47 rows=1 width=4) (actual rows= loops=)

  • Sort Key: pc.physicalconfigid DESC
108. 0.000 0.000 ↓ 0.0

Index Scan using physicalconfig_sourceid_idx on physicalconfig pc (cost=0.42..1.45 rows=1 width=4) (actual rows= loops=)

  • Index Cond: ((sourceid)::text = 'USER'::text)
  • Filter: ((NOT deleted) AND ((status)::text = 'ACTUAL'::text) AND (deliverypointid = dp.deliverypointid) AND (((cdp.todate IS NULL) AND (todate IS NULL)) OR ((cdp.todate (...)
109. 0.000 0.000 ↓ 0.0

Hash (cost=45,789.22..45,789.22 rows=646,727 width=49) (actual rows= loops=)

110. 0.000 0.000 ↓ 0.0

Seq Scan on generalconfig gc_dgo (cost=0.00..45,789.22 rows=646,727 width=49) (actual rows= loops=)

  • Filter: ((NOT deleted) AND ((sourceid)::text = 'DGO'::text) AND ((status)::text = 'ACTUAL'::text))
111. 0.000 0.000 ↓ 0.0

Hash (cost=1.45..1.45 rows=1 width=16) (actual rows= loops=)

112. 0.000 0.000 ↓ 0.0

Index Scan using logicalconfig_sourceid_idx on logicalconfig lc_usr (cost=0.42..1.45 rows=1 width=16) (actual rows= loops=)

  • Index Cond: ((sourceid)::text = 'USER'::text)
  • Filter: ((NOT deleted) AND ((status)::text = 'ACTUAL'::text))
113. 0.000 0.000 ↓ 0.0

Hash (cost=39,961.32..39,961.32 rows=398,573 width=49) (actual rows= loops=)

114. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on generalconfig gc_usr (cost=5,670.15..39,961.32 rows=398,573 width=49) (actual rows= loops=)

  • Recheck Cond: ((sourceid)::text = 'USER'::text)
  • Filter: ((NOT deleted) AND ((status)::text = 'ACTUAL'::text))
115. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on generalconfig_sourceid_id (cost=0.00..5,570.51 rows=472,411 width=0) (actual rows= loops=)

  • Index Cond: ((sourceid)::text = 'USER'::text)
116. 0.000 0.000 ↓ 0.0

Hash (cost=604,829.35..604,829.35 rows=117 width=9) (actual rows= loops=)

117. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=603,310.88..604,829.35 rows=117 width=9) (actual rows= loops=)

118. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=603,310.44..604,457.27 rows=117 width=13) (actual rows= loops=)

119. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=603,310.00..604,237.18 rows=117 width=8) (actual rows= loops=)

120. 0.000 0.000 ↓ 0.0

Subquery Scan on q (cost=603,309.57..604,067.24 rows=117 width=8) (actual rows= loops=)

  • Filter: (q.rank = 1)
121. 0.000 0.000 ↓ 0.0

WindowAgg (cost=603,309.57..603,775.83 rows=23,313 width=24) (actual rows= loops=)

122. 0.000 0.000 ↓ 0.0

Sort (cost=603,309.57..603,367.85 rows=23,313 width=8) (actual rows= loops=)

  • Sort Key: i.contractid, i.invoiceid DESC
123. 0.000 0.000 ↓ 0.0

Gather (cost=1,003.27..601,618.34 rows=23,313 width=8) (actual rows= loops=)

  • Workers Planned: 2
124. 0.000 0.000 ↓ 0.0

Hash Join (cost=3.27..598,287.04 rows=9,714 width=8) (actual rows= loops=)

  • Hash Cond: (i.invoicenatureparamid = pv.parametervalueid)
125. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on invoice i (cost=0.00..582,395.38 rows=6,032,138 width=12) (actual rows= loops=)

126. 0.000 0.000 ↓ 0.0

Hash (cost=3.24..3.24 rows=2 width=4) (actual rows= loops=)

127. 0.000 0.000 ↓ 0.0

Index Scan using parametervalue_internalparametervaluecode_idx on parametervalue pv (cost=0.28..3.24 rows=2 width=4) (actual rows= loops=)

  • Index Cond: ((internalparametervaluecode)::text = 'instalment'::text)
128. 0.000 0.000 ↓ 0.0

Index Only Scan using invoice_pkey on invoice lastinstal_i (cost=0.43..1.45 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (invoiceid = q.invoiceid)
129. 0.000 0.000 ↓ 0.0

Index Scan using invdoc_invoiceid on invdoc lastinstal_id (cost=0.43..1.87 rows=1 width=9) (actual rows= loops=)

  • Index Cond: (invoiceid = lastinstal_i.invoiceid)
130. 0.000 0.000 ↓ 0.0

Index Scan using index_invoicerelation_finalinvoiceid on invoicerelation lastinstal_template_cr (cost=0.44..3.04 rows=14 width=8) (actual rows= loops=)

  • Index Cond: (finalinvoiceid = lastinstal_i.invoiceid)
  • Filter: ((nature)::text = 'TEMPLATED'::text)
131. 0.000 0.000 ↓ 0.0

Hash (cost=465,629.73..465,629.73 rows=58 width=16) (actual rows= loops=)

132. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=465,109.10..465,629.73 rows=58 width=16) (actual rows= loops=)

133. 0.000 0.000 ↓ 0.0

Subquery Scan on q_1 (cost=465,108.67..465,487.49 rows=58 width=8) (actual rows= loops=)

  • Filter: (q_1.rank = 1)
134. 0.000 0.000 ↓ 0.0

WindowAgg (cost=465,108.67..465,341.79 rows=11,656 width=24) (actual rows= loops=)

135. 0.000 0.000 ↓ 0.0

Sort (cost=465,108.67..465,137.81 rows=11,656 width=8) (actual rows= loops=)

  • Sort Key: i_1.contractid, i_1.invoiceid DESC
136. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.84..464,321.38 rows=11,656 width=8) (actual rows= loops=)

137. 0.000 0.000 ↓ 0.0

Index Scan using parametervalue_internalparametervaluecode_idx on parametervalue pv_1 (cost=0.28..2.29 rows=1 width=4) (actual rows= loops=)

  • Index Cond: ((internalparametervaluecode)::text = 'instalmentTemplate'::text)
138. 0.000 0.000 ↓ 0.0

Index Scan using invoice_invoicenatureparamid_idx on invoice i_1 (cost=0.56..449,841.95 rows=1,447,713 width=12) (actual rows= loops=)

  • Index Cond: (invoicenatureparamid = pv_1.parametervalueid)
139. 0.000 0.000 ↓ 0.0

Index Scan using invoice_pkey on invoice very_lastinstal_template (cost=0.43..2.45 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (invoiceid = q_1.invoiceid)
140. 0.000 0.000 ↓ 0.0

Hash (cost=2.90..2.90 rows=90 width=23) (actual rows= loops=)

141. 0.000 0.000 ↓ 0.0

Seq Scan on netarea (cost=0.00..2.90 rows=90 width=23) (actual rows= loops=)

142. 0.000 0.000 ↓ 0.0

Hash (cost=187.42..187.42 rows=1,242 width=19) (actual rows= loops=)

143. 0.000 0.000 ↓ 0.0

Seq Scan on parametervalue firstprod (cost=0.00..187.42 rows=1,242 width=19) (actual rows= loops=)

144. 0.000 0.000 ↓ 0.0

Hash (cost=187.42..187.42 rows=1,242 width=19) (actual rows= loops=)

145. 0.000 0.000 ↓ 0.0

Seq Scan on parametervalue currentprod (cost=0.00..187.42 rows=1,242 width=19) (actual rows= loops=)

146. 0.000 0.000 ↓ 0.0

Hash (cost=187.42..187.42 rows=1,242 width=19) (actual rows= loops=)

147. 0.000 0.000 ↓ 0.0

Seq Scan on parametervalue lastprod (cost=0.00..187.42 rows=1,242 width=19) (actual rows= loops=)

148. 0.000 0.000 ↓ 0.0

Hash (cost=187.42..187.42 rows=1,242 width=19) (actual rows= loops=)

149. 0.000 0.000 ↓ 0.0

Seq Scan on parametervalue block (cost=0.00..187.42 rows=1,242 width=19) (actual rows= loops=)

150. 0.000 0.000 ↓ 0.0

Hash (cost=187.42..187.42 rows=1,242 width=19) (actual rows= loops=)

151. 0.000 0.000 ↓ 0.0

Seq Scan on parametervalue currentsup (cost=0.00..187.42 rows=1,242 width=19) (actual rows= loops=)

152.          

SubPlan (for Hash Left Join)

153. 0.000 0.000 ↓ 0.0

Aggregate (cost=8.88..8.89 rows=1 width=4) (actual rows= loops=)

154. 0.000 0.000 ↓ 0.0

Index Scan using idx_contractrateid_contractid on contractrate cr (cost=0.43..8.88 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (contractid = co.contractid)
  • Filter: (((todate IS NULL) OR (todate > currenthistory.startdate)) AND (fromdate < COALESCE((currenthistory.forcedenddate)::timestamp without time zone, (currenthistory.startdate + (((currenthistory.duration)::text || 'months'::text))::inte (...)
155. 0.000 0.000 ↓ 0.0

Aggregate (cost=8.88..8.89 rows=1 width=4) (actual rows= loops=)

156. 0.000 0.000 ↓ 0.0

Index Scan using idx_contractrateid_contractid on contractrate cr_1 (cost=0.43..8.88 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (contractid = co.contractid)
  • Filter: (((todate IS NULL) OR (todate > lasthistory.startdate)) AND (fromdate < COALESCE((lasthistory.forcedenddate)::timestamp without time zone, (lasthistory.startdate + (((lasthistory.duration)::text || 'months'::text))::interval))))
157. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.43..7.35 rows=1 width=0) (actual rows= loops=)

158. 0.000 0.000 ↓ 0.0

Index Scan using idx_attestation_customerid_pk on attestation a_1 (cost=0.29..5.17 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (customerid = cu.customerid)
  • Filter: ((fromdate <= now()) AND ((todate IS NULL) OR (todate >= now())))
159. 0.000 0.000 ↓ 0.0

Index Only Scan using attestationcategory_pkey on attestationcategory ac (cost=0.14..2.16 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (attestationcategoryid = a_1.attestationcategoryid)
160. 0.000 0.000 ↓ 0.0

Hash Join (cost=15.62..1,612.82 rows=6,138 width=4) (actual rows= loops=)

  • Hash Cond: (a_2.attestationcategoryid = ac_1.attestationcategoryid)
161. 0.000 0.000 ↓ 0.0

Seq Scan on attestation a_2 (cost=0.00..1,580.80 rows=6,138 width=8) (actual rows= loops=)

  • Filter: ((fromdate <= now()) AND ((todate IS NULL) OR (todate >= now())))
162. 0.000 0.000 ↓ 0.0

Hash (cost=12.50..12.50 rows=250 width=4) (actual rows= loops=)

163. 0.000 0.000 ↓ 0.0

Seq Scan on attestationcategory ac_1 (cost=0.00..12.50 rows=250 width=4) (actual rows= loops=)

164. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.43..7.36 rows=1 width=0) (actual rows= loops=)

165. 0.000 0.000 ↓ 0.0

Index Scan using idx_attestation_customerid_pk on attestation a_3 (cost=0.29..5.17 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (customerid = cu.customerid)
  • Filter: ((fromdate <= now()) AND ((todate IS NULL) OR (todate >= now())))
166. 0.000 0.000 ↓ 0.0

Index Scan using attestationcategory_pkey on attestationcategory ac_2 (cost=0.14..2.16 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (attestationcategoryid = a_3.attestationcategoryid)
  • Filter: socialinvoiced
167. 0.000 0.000 ↓ 0.0

Hash Join (cost=14.06..1,611.25 rows=3,069 width=4) (actual rows= loops=)

  • Hash Cond: (a_4.attestationcategoryid = ac_3.attestationcategoryid)
168. 0.000 0.000 ↓ 0.0

Seq Scan on attestation a_4 (cost=0.00..1,580.80 rows=6,138 width=8) (actual rows= loops=)

  • Filter: ((fromdate <= now()) AND ((todate IS NULL) OR (todate >= now())))
169. 0.000 0.000 ↓ 0.0

Hash (cost=12.50..12.50 rows=125 width=4) (actual rows= loops=)

170. 0.000 0.000 ↓ 0.0

Seq Scan on attestationcategory ac_3 (cost=0.00..12.50 rows=125 width=4) (actual rows= loops=)

  • Filter: socialinvoiced
171. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.71..15.39 rows=1 width=0) (actual rows= loops=)

172. 0.000 0.000 ↓ 0.0

Index Scan using idx_contractrateid_contractid on contractrate cr_2 (cost=0.43..8.77 rows=2 width=4) (actual rows= loops=)

  • Index Cond: (contractid = co.contractid)
  • Filter: ((fromdate <= now()) AND ((todate IS NULL) OR (todate > now())))
173. 0.000 0.000 ↓ 0.0

Index Scan using rate_rateid_key on rate r (cost=0.28..2.30 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (rateid = cr_2.rateid)
  • Filter: ((social IS TRUE) AND (socialinvoiced IS TRUE))
174. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.43..3,279.71 rows=69 width=4) (actual rows= loops=)

175. 0.000 0.000 ↓ 0.0

Seq Scan on rate r_1 (cost=0.00..409.56 rows=1 width=4) (actual rows= loops=)

  • Filter: ((social IS TRUE) AND (socialinvoiced IS TRUE))
176. 0.000 0.000 ↓ 0.0

Index Scan using contractrate_rateid_index on contractrate cr_3 (cost=0.43..2,861.55 rows=860 width=8) (actual rows= loops=)

  • Index Cond: (rateid = r_1.rateid)
  • Filter: ((fromdate <= now()) AND ((todate IS NULL) OR (todate > now())))
177. 0.000 0.000 ↓ 0.0

Result (cost=19,108.23..19,116.34 rows=60 width=32) (actual rows= loops=)

178.          

Initplan (for Result)

179. 0.000 0.000 ↓ 0.0

Limit (cost=19,107.80..19,107.80 rows=1 width=8) (actual rows= loops=)

180. 0.000 0.000 ↓ 0.0

Sort (cost=19,107.80..19,107.80 rows=1 width=8) (actual rows= loops=)

  • Sort Key: index.todate DESC, index.indexid DESC
181. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on index (cost=4,601.21..19,107.79 rows=1 width=8) (actual rows= loops=)

  • Recheck Cond: (((sourceid)::text = ANY ('{CUSTOMER,USER}'::text[])) AND ((context)::text = 'INITIAL'::text))
  • Filter: ((externalref)::text = ((co.contractid)::character varying)::text)
182. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=4,601.21..4,601.21 rows=16,248 width=0) (actual rows= loops=)

183. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on index_sourceid_idx (cost=0.00..2,172.77 rows=193,588 width=0) (actual rows= loops=)

  • Index Cond: ((sourceid)::text = ANY ('{CUSTOMER,USER}'::text[]))
184. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on "IX_index_context_index" (cost=0.00..2,428.18 rows=193,434 width=0) (actual rows= loops=)

  • Index Cond: ((context)::text = 'INITIAL'::text)
185. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.43..7.19 rows=60 width=40) (actual rows= loops=)

186. 0.000 0.000 ↓ 0.0

Index Scan using "IX_indexdetail_indexid_indexdetail" on indexdetail id (cost=0.43..6.84 rows=6 width=39) (actual rows= loops=)

  • Index Cond: (indexid = $19)
187. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.27..5.35 rows=1 width=18) (actual rows= loops=)

188. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.84..4.89 rows=1 width=4) (actual rows= loops=)

189. 0.000 0.000 ↓ 0.0

Index Scan using contractdelively_contract_id_idx on contractdelivery cdp2 (cost=0.42..2.45 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (contractid = cdp.contractid)
  • Filter: ((fromdate <> todate) AND (cdp.fromdate = fromdate) AND (COALESCE(cdp.todate, '2099-12-31'::date) = COALESCE(todate, '2099-12-31'::date)))
190. 0.000 0.000 ↓ 0.0

Index Scan using deliveryperiod_pkey on deliveryperiod per2 (cost=0.42..2.44 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (deliveryperiodid = cdp2.deliveryperiodid)
191. 0.000 0.000 ↓ 0.0

Index Scan using deliverypoint_pkey on deliverypoint dp2 (cost=0.42..0.46 rows=1 width=22) (actual rows= loops=)

  • Index Cond: (deliverypointid = per2.deliverypointid)
  • Filter: ((gsrn)::text <> (dp.gsrn)::text)
192. 0.000 0.000 ↓ 0.0

Index Scan using town_pkey on town t_1 (cost=0.28..2.30 rows=1 width=10) (actual rows= loops=)

  • Index Cond: (((country)::text = (dgoadd.towncountrycode)::text) AND ((postalcode)::text = (dgoadd.townpostalcode)::text) AND (towncode = dgoadd.towntowncode))
193. 0.000 0.000 ↓ 0.0

Index Scan using meter_physicalconfigid_idx on meter (cost=0.42..2.45 rows=1 width=0) (actual rows= loops=)

  • Index Cond: (physicalconfigid = COALESCE(pc_dgo.physicalconfigid, pc_usr.physicalconfigid))
  • Filter: ((metertype)::text = 'BUDGET_METER'::text)
194. 0.000 0.000 ↓ 0.0

Index Scan using "IX_meter_metertype_meter" on meter meter_1 (cost=0.42..1,392.48 rows=6,516 width=4) (actual rows= loops=)

  • Index Cond: ((metertype)::text = 'BUDGET_METER'::text)
195. 0.000 0.000 ↓ 0.0

Index Scan using meter_physicalconfigid_idx on meter meter_2 (cost=0.42..2.44 rows=1 width=9) (actual rows= loops=)

  • Index Cond: (physicalconfigid = COALESCE(pc_dgo.physicalconfigid, pc_usr.physicalconfigid))